Excel vba

[fd1]

Hi everyone,
I'm getting error when running the following vbscript that formats an Excel sheet. It complains that object doesn't support the property "Selection.Borders".
Is the code for adding borders correct?

Dim objXL
Dim boolXL
Dim objActiveWkb
Dim filePath
 
filePath = "C:\Test.xls"
 
Set objXL = CreateObject("Excel.Application")
 
    objXL.Application.Workbooks.Open (filePath)
    Set objActiveWkb = objXL.Application.ActiveWorkbook
    objActiveWkb.Application.DisplayAlerts = False
 
    With objXL
      .Range("A1:H1").Select
      .Selection.Interior.ColorIndex = 15
    End With
 
    With objXL
    .Selection.Borders(xlEdgeRight) 
    .LineStyle = xlContinuous 
    .Weight = xlThin 
    .ColorIndex = xlAutomatic 
    End With
 
    With objXL
        .Sheets("Tabelle 1").Select
        .Columns.AutoFit
    End With
 
    objActiveWkb.Save
 

[kadghar]

Hi everyone,
I'm getting error when running the following vbscript that formats an Excel sheet. It complains that object doesn't support the property "Selection.Borders".
Is the code for adding borders correct?

Try doing it like this:

    With objXL.Selection.Borders(xlEdgeRight) 
    .LineStyle = xlContinuous 
    .Weight = xlThin 
    .ColorIndex = xlAutomatic 
    End With
 

HTH

[fd1]

kadghar, I tried it but still got an "unknown runtime error" on the same line where Borders property is used.

[kadghar]

kadghar, I tried it but still got an "unknown runtime error" on the same line where Borders property is used.

Just curious:

are you using excels VBA??

Because if you're not, any other version of VB, even any VBA outside Excel, wont recognize the excel's constants such as xlContinuous, etc..

[fd1]

I'm running the code above as a .vbs script so I guess I am using it outside Excel vba. So what's the alternative? running the code in an Excel macro?

[kadghar]

I'm running the code above as a .vbs script so I guess I am using it outside Excel vba. So what's the alternative? running the code in an Excel macro?

well, no, every constant has its own numeric value, just debug the code in an excel's macro. (with F8 instead of F5) and while you're debugging it (while the yellow line is somewhere), you can pass the mouse over every constant, and check its numeric value, for example, instead of xlDown, you can use -4121. If you replace every constant with the numeric value, the issue will be solved.

[fd1]

Thanks kadghar, will try it out.