Solved

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

Posted on 2008-10-17
16
386 Views
Last Modified: 2013-11-10
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!
0
Comment
Question by:smyers051972
  • 8
  • 5
  • 3
16 Comments
 
LVL 1

Expert Comment

by:ben_staples
ID: 22746022
Do you want to shift the current data down + right one cell or overtype the headings?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 22746024
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 22746044
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
 
LVL 1

Expert Comment

by:ben_staples
ID: 22746065
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
 
LVL 1

Author Comment

by:smyers051972
ID: 22746801
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
 
LVL 1

Author Comment

by:smyers051972
ID: 22746804
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
 
LVL 1

Author Comment

by:smyers051972
ID: 22746817
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 22746908
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:smyers051972
ID: 22747165
Error: Expected End of statement line 7 char 6
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 22747240
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
 
LVL 1

Expert Comment

by:ben_staples
ID: 22747413
The code zorvek posted should work.
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22754152
Going to test in the morning tomorrow when I get to work. Thank!
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22754156
*Thanks!
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22758013
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
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 22758631
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
 
LVL 1

Author Closing Comment

by:smyers051972
ID: 31507342
Kevin: Awesome!

Worked perfectly! Thanks again :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Formula or Macro to determine variance 17 75
Update As Well As Add 6 35
Excel macro runs twice 13 46
Issues with DAX Calculated Columns 6 0
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now