CODEWITHSUNDEEP
excelvbscript
GettingStarted
GettingStarted

Reputation: 7605

I can't figure out the .FieldName

I am trying to create my first VBScript that creates an Excel spreadsheet.

I got most of the code from an Excel Macro but the project changed so now the VBScript needs to live on the outside.

I was crashing on rStart = .Range but I changed the code to 

 Set rStart = objExcel.Range("A1")

Now I am crashing on Also if someone can explain why some of the code starts with .ColumnWidth, .Range, .Cells

I looked up some of these things and it can come from a number of places. Not sure how to determine which one.

For example

.Range can come from Excel.Application or Excel.Application.AutoFilter or Global.Range, etc.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
objWorkbook.SaveAs(strFileName)

Dim lMonth 'As Variant
Dim strMonth 'As String
Dim rStart 'As Range
Dim strAddress 'As String
Dim rCell 'As Range
Dim lDays 'As Long
Dim dDate 'As Date

'Add new sheet and format
'Worksheets.Add
objExcel.Worksheets.Add
'ActiveWindow.DisplayGridlines = False
objExcel.ActiveWindow.DisplayGridlines = False
With objExcel.Cells
    .ColumnWidth = 6
    .Font.Size = 8
End With

'Create the Month headings
For lMonth = 1 To 4
    Select Case lMonth
        Case 1
        strMonth = "January"
        Set rStart = .Range("A1") 
        Case 2
        strMonth = "April"
        Set rStart = .Range("A8")
        Case 3
        strMonth = "July"
        Set rStart = .Range("A15")
        Case 4
        strMonth = "October"
        Set rStart = .Range("A22")
    End Select  

    'Merge, AutoFill and align months       
    With rStart         
        rStart.Value = strMonth
        .HorizontalAlignment = xlCenter // Crashing here
        .Interior.ColorIndex = 6        
        .Font.Bold = True           
        With .Range("A1:G1")                
            .Merge              
            .BorderAround xlContinuous              
        End With            
        .Range("A1:G1").AutoFill Destination=.Range("A1:U1")            
    End With        
Next

I've tried using 

 rStart.HorizonalAlignment = xlCenter
 objExcel.Range.HorizontalAlignment = xlCenter

I am trying to create an External VBScript that will create an Excel file (no Module Macro or VBA Projects within an Excel document).

The error message says "Unable to set the HorizontalAlignment property of the Range class"

Upvotes: 1

Views: 102

Answers (2)

barryleajo
barryleajo

Reputation: 1952

Given that this is in vbscript, and assuming you have imported the Excel namespace, try:

rStart.HorizonalAlignment = Constants.xlCenter

On the 'dot notation' that you mention.

In an object oriented language, Objects have Methods and Properties that belong to them. Operating code requires each method/property to be properly 'qualified' to its 'parent' object. So, for example, a value in a cell could be 'fully qualified' viz:

objExcel.Workbooks("MyWorkbook").Sheets("MySheet").Range("MyRange").Value = 10

You have already used this notation in your code in a number of places.

You can implement multiple statements on a particular object using a With.. End With structure. In so doing you can refer to the 'parents' using the shortform . (period) So, in the above simple example:

With objExcel.Workbooks("MyWorkbook").Sheets("MySheet").Range("MyRange")
    .value = 10
    .HorizontalAlignment = Constants.xlCenter
End With

Look here for the Microsoft reference for With.

The availability of objects and their respective methods and properties can be found in the appropriate 'object model'. You can begin the exploration of the Excel object model here. If you were working with Word or Outlook for example then you would need to refer to their respective object models.

This should help you straighten-out some of your code and point you to the key initial sources of reference as a first port of call. This VBScript Language Reference may also be a useful resource for you.

Upvotes: 1

Sean Wessell
Sean Wessell

Reputation: 3510

When accessing excel through intertrop you need to remember you no longer have access to excel constants that are set within excel so you must use the value assigned to those constants or declare those constants yourself.

Excel Constants enumeration

Add the constants at the top of your code.

Const xlCenter = -4108

Upvotes: 4

Related Questions