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!
LVL 1
smyers051972Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ben_staplesCommented:
Do you want to shift the current data down + right one cell or overtype the headings?
0
zorvek (Kevin Jones)ConsultantCommented:
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
0
zorvek (Kevin Jones)ConsultantCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ben_staplesCommented:
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 & "\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

0
smyers051972Author Commented:
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!
0
smyers051972Author Commented:
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!
0
smyers051972Author Commented:
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 :)
0
zorvek (Kevin Jones)ConsultantCommented:
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
0
smyers051972Author Commented:
Error: Expected End of statement line 7 char 6
0
zorvek (Kevin Jones)ConsultantCommented:
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
0
ben_staplesCommented:
The code zorvek posted should work.
0
smyers051972Author Commented:
Going to test in the morning tomorrow when I get to work. Thank!
0
smyers051972Author Commented:
*Thanks!
0
smyers051972Author Commented:
Now I have an error on line 3, unable to get the Open property of the workbook class

Not sure what this would mean.
0
zorvek (Kevin Jones)ConsultantCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smyers051972Author Commented:
Kevin: Awesome!

Worked perfectly! Thanks again :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.