?
Solved

Auto create excel file and sheets using vbs script

Posted on 2011-10-17
3
Medium Priority
?
287 Views
Last Modified: 2012-08-13
Hi,
The below vbs code is run from a 'VBScript Script File'
It currently creates an excel file with one sheet that populates cell A1 with Field1,Field2,Field3

I would like to know how to:

-Create individual sheets for Field1,Field2,Field3, so the 1st sheet would be called 'Field1' and would have 2 columns: Field1, Description

The field list needs to be dynamic, so any number of fields can be passed into the vbs script.

I have attached an example of the output excel file.

Thank you
Dim xlApp
Dim xlBook
Dim xlSheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlApp.visible = True
'optionally make the sheet visible
'xlSheet.Application.Visible = True
xlSheet.Cells(1,1).Value = "Field1,Field2,Field3"
'etc
xlSheet.SaveAs "C:\Temp\TableTemplate.xlsx"
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

Open in new window

TableTemplate.xlsx
0
Comment
Question by:crompnk
  • 2
3 Comments
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36980225
This should get you started:

Dim xlApp
Dim xlBook
Dim xlSheet
inputx = array("A","B","C","E","H","L","M","N","P","R","S","T","W","U")
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
y=0
for each x in inputx
	y= y + 1
	if y > 3 then

		xlBook.worksheets.add	
	end if
next

y=0
for each x in inputx
	y= y + 1
	Set xlSheet = xlBook.Worksheets(y)
	xlBook.Worksheets(y).name = x
next

xlApp.visible = True
xlSheet.Application.Visible = True
'xlSheet.Cells(1,1).Value = "Field1,Field2,Field3"
'etc
xlSheet.SaveAs "C:\Temp\TableTemplate.xlsx"
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

Open in new window

0
 
LVL 9

Accepted Solution

by:
experts1 earned 1000 total points
ID: 36980450
See modification below:

This should add sheets according to number of fields in string.

Dim xlApp
Dim xlBook
Dim xlSheet
Dim sheet_names as String  'string for sheet names
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
sheet_names = "Field1,Field2,Field3"   'Assign sheet names to string
x_arr = Split(sheet_names, ",")   'Split sheet name string into array
'Add worksheets and 2 columns here
  for i = 0 to UBound(x_arr)
    xlApp.Worksheets.Add().Name = x_arr(i)
    xlApp.Worksheets(x_arr(i)).Cells(1,1).Value ="Field1"
    xlApp.Worksheets(x_arr(i)).Cells(1,2).Value ="Description"
  next
'Set xlSheet = xlBook.Worksheets
xlApp.visible = True
'optionally make the sheet visible
'xlSheet.Application.Visible = True
'xlSheet.Cells(1,1).Value = "Field1,Field2,Field3"

'etc
xlSheet.SaveAs "C:\Temp\TableTemplate.xlsx"
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
0
 
LVL 11

Assisted Solution

by:ScriptAddict
ScriptAddict earned 1000 total points
ID: 36980536
I did a little tweeking on the above experts1 code to get it to work on my PC.  
Dim xlApp
Dim xlBook
Dim xlSheet
Dim sheet_names 
'string for sheet names
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
sheet_names = "Field1,Field2,Field3"   'Assign sheet names to string
x_arr = Split(sheet_names, ",")   'Split sheet name string into array
'Add worksheets and 2 columns here
  for i = 0 to UBound(x_arr)
    xlApp.Worksheets.Add().Name = x_arr(i)
    xlApp.Worksheets(x_arr(i)).Cells(1,1).Value ="Field1"
    xlApp.Worksheets(x_arr(i)).Cells(1,2).Value ="Description"
  next
'Set xlSheet = xlBook.Worksheets
xlApp.visible = True
'optionally make the sheet visible
'xlSheet.Application.Visible = True
'xlSheet.Cells(1,1).Value = "Field1,Field2,Field3"

'etc
xlBook.SaveAs "C:\Temp\TableTemplate1.xlsx"
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing 

Open in new window

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

807 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