Change Format of Excel column using VBScript

[gtdalsanto]

I am opening an Excel/CSV file using VBScript. What I am trying to do is to select a column in the spreadsheet, then change the format to a custom format. I am trying to format the column as a number with a fixed size of 10 digits. So the result would be for an input of 12345, the format change would make it 0000012345, basically adding the leading zeros.

Currently, I use the following VB Function to open the spreadsheet:

Sub OpenExcelFile(filename)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = True
Set xlBook = xlApp.Workbooks.open(filename)
end sub


Then I select the column I want with the following vbcode:

xlBook.ActiveSheet.Range("A:A").Select

At this point, I have the column I want selected, and I want to format it to be a number always with 10 digits. I would think it would be something like the following:

xlbook.ActiveSheet.Selection.NumberFormat = "0000000000"

but this statement causes a VBScript error of the following:

VBScript runtime error: 438
Object doesn't support this property or method: xlbook.ActiveSheet.Selection.

Does anyone know how to format an excel column from Macro Scheduler using VBScript?

Thanks for your help.

Gary

[Marcus Tettmar]

From the excel help:

Application.Selection Property
Returns the selected object in the active window for an Application object.

[Place cursor on Selection in the VBA editor and hit F1]

So Selection is a property of application, not sheet. So your code should be:

xlApp.Selection.NumberFormat = "0000000000"

[xlApp being the Excel.Application object you created]

[gtdalsanto]

I should have caught that. As always, thanks for the quick reply.

Gary