asked on

Looking for a .VBS Script to create a header onto excel 2003.

I need a script in VB (.VBS) that when run will populate the 1st row and each column going left to right on every worksheet in a single excel file such as this:

ColA ColB ColC
1 ITEM DESCRIPTION PRICE
2
3

The sole purpose for thie script is to create a header only, there is more than 3 columns to add but if I could get the three and shown how to add in more columns that would be great! Please note, not needing a macro, this will all be run from a script at the command prompt for automation purposes.

Thank you!

ben_staples

Do you want to shift the current data down + right one cell or overtype the headings?

zorvek (Kevin Jones)

Public Sub SetUpHeaderRows()

Dim Worksheet As Worksheet
For Each Worksheet In Worksheets
Worksheet.[A1:C1].Value = Array("ITEM", "DESCRIPTION", "PRICE")
Next Worksheet

End Sub

Kevin

zorvek (Kevin Jones)

This is more compatible with VBScript. Assuming you have the workbook object defined as oWorkbook:

For Each Worksheet In oWorkbook.Worksheets
Worksheet.Range("A1:C1").Value = Array("ITEM", "DESCRIPTION", "PRICE")
Next

Kevin

ben_staples

That's just a macro, for a VBScript file it would be:

Option Explicit
On Error Resume Next
Dim directory, objExcel, workbook, worksheet
Set objExcel = CreateObject("Excel.Application")
'Gets the directory where our script is running from
directory = CreateObject("Scripting.FileSystemObject").GetParentFolderName(Wscript.ScriptFullName)
'Open XLS file
Set workbook = objExcel.Workbooks.Open(directory &amp; "\myfile.xls")
 
For Each Worksheet In Worksheets
	Worksheet.[A1:C1].Value = Array("ITEM", "DESCRIPTION", "PRICE")
Next Worksheet
 
objExcel.Workbooks.Close
objExcel.quit
objExcel = Empty
workbook = Empty

Open in new window

smyers051972

ASKER

Ben:
Will try it Monday when I get to work. Thank you for your reply!

I am going to replace "\myfile.xls" portion with the name of the actual spread sheet. Or should I do "C:\mydirectory\myfile.xls" ?

Thanks again!

smyers051972

ASKER

One last thing, does the workbook have to be empty or will it auto insert above existing data? I would probably prefer it to insert it above existing data.

Thanks!

smyers051972

ASKER

ok curiosity was killing me so I tried it here at home and ran the file on myfile.xls (a file i created) the bad news was i got an error on line 8 char 54 Expected ')'

Not sure where its wanting the ) to be

Thanks :)

zorvek (Kevin Jones)

Try this sequence:

Set ExcelApplication = CreateObject("Excel.Application")
Folder = CreateObject("Scripting.FileSystemObject").GetParentFolderName(Wscript.ScriptFullName)
Set Workbook = ExcelApplication.Workbooks.Open(Folder, "\Workbook.xls")
For Each Worksheet In Workbook.Worksheets
Worksheet.Range("1:1").Insert
Worksheet.Range("A1:C1").Value = Array("ITEM", "DESCRIPTION", "PRICE")
Next Worksheet
Workbook.Save
Workbook.Close False
ExcelApplication.Quit

Kevin

smyers051972

ASKER

Error: Expected End of statement line 7 char 6

zorvek (Kevin Jones)

Fixed:

Set ExcelApplication = CreateObject("Excel.Application")
Folder = CreateObject("Scripting.FileSystemObject").GetParentFolderName(Wscript.ScriptFullName)
Set Workbook = ExcelApplication.Workbooks.Open(Folder, "\Workbook.xls")
For Each Worksheet In Workbook.Worksheets
Worksheet.Range("1:1").Insert
Worksheet.Range("A1:C1").Value = Array("ITEM", "DESCRIPTION", "PRICE")
Next
Workbook.Save
Workbook.Close False
ExcelApplication.Quit

Kevin

ben_staples

The code zorvek posted should work.

smyers051972

ASKER

Going to test in the morning tomorrow when I get to work. Thank!

smyers051972

ASKER

*Thanks!

smyers051972

ASKER

Now I have an error on line 3, unable to get the Open property of the workbook class

Not sure what this would mean.

ASKER CERTIFIED SOLUTION