• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

Vb Script to Add Multiple SpreadSheets

I would like to be able to add multiple spreadsheets to a workbook. Here is what I tried to do, but I get an error 'Object doesnt support this property or method 'Workbooks.Sheets'

Set excel = WScript.CreateObject ("Excel.Application")
excel.Visible = false

excel.Workbooks.Sheets.Add, excel.Workbooks.Sheets(objWorkbook.Sheets.Count),19

Any help would be greatly appreciated.
0
drezner7
Asked:
drezner7
  • 6
  • 5
1 Solution
 
Patrick MatthewsCommented:
Dim xlApp, xlWb, xlWs

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible
Set xlWb = xlApp.Workbooks.Open("c:\folder\subfolder\file.xls")
' new file:
' Set xlWb = xlApp.Workbooks.Add

Set xlWs = xlWb.Worksheets.Add(After:=xlWb.Worksheets(xlWb.Worksheets.Count))
xlWs.Name = "Doh"
Set xlWs = xlWb.Worksheets.Add(After:=xlWb.Worksheets(xlWb.Worksheets.Count))
xlWs.Name = "Ray"
Set xlWs = xlWb.Worksheets.Add(After:=xlWb.Worksheets(xlWb.Worksheets.Count))
xlWs.Name = "Mee"

Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

Open in new window

0
 
Chris BottomleyCommented:
Close but more like:

excel.Workbooks(1).Sheets.Add,  excel.Workbooks(1).Sheets(excel.Workbooks(1).Sheets.Count),19

or if objWorkbook genuinely addresses the workbook then:

objWorkbook.Sheets.Add,  objWorkbook.Sheets(objWorkbook.Sheets.Count),19

Chris
0
 
drezner7Author Commented:
Thank you Matthew I think I did something wrong. I tried taking what you gave me an incorporated into my existing script, but something broke,.. Here is my code:


Option Explicit

'Variables
Dim sFile, sXLS, excel, objFSO, objFile, wbs, sText, strPath, strCommand, xlWS, excelWorkbook, objShell, objDialog, intResult, XA, XB, XC, XD, ws1, ws2, ws3, ws4, rwXB, rwXA, rwXC, rwXD

'Prompt C:\ Console Window
   ' If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
   ' strPath = Wscript.ScriptFullName
   ' strCommand = "%comspec% /c cscript  """ & strPath & """"
   ' Set objShell = CreateObject("Wscript.Shell")
   ' objShell.Run(strCommand), 1, True
   ' Wscript.Quit
'End If

Wscript.Echo "Processing Data . . . ."

'Open File Browser
Set objDialog = CreateObject("UserAccounts.CommonDialog")
objDialog.Filter = "Text Files|*.txt|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "C:\Sample"
intResult = objDialog.ShowOpen
 
If intResult = 0 Then
    Wscript.Quit

End If

'sFile = "C:\Sample\input.txt"

'Output File Location
sXLS = "C:\Sample\parse.xlsx"

Set excel = WScript.CreateObject ("Excel.Application")

excel.Visible = false

Set wbs = excel.Workbooks.Add

Set xlWs = wbs.Worksheets.Add(After:=wbs.Worksheets(wbs.Worksheets.Count))
xlWs.Name = "Doh"

Set xlWs = wbs.Worksheets.Add(After:=wbs.Worksheets(wbs.Worksheets.Count))
xlWs.Name = "Ray"

Set xlWs = wbs.Worksheets.Add(After:=wbs.Worksheets(wbs.Worksheets.Count))
xlWs.Name = "Mee"

Set objFSO = CreateObject("Scripting.FileSystemObject")

  Set wbs = excel.Workbooks.Open(sXLS)
 
 'Add Spreadsheet to Workbook
 
  wbs.WorkSheets(1).Name = "XA"
  wbs.WorkSheets(2).Name = "XB"
  wbs.WorkSheets(3).Name = "XC"
  'wbs.WorkSheets(4).Name = "XD"


 'Setup variable worksheet
  Set ws1 = wbs.workSheets("XA")
  Set ws2 = wbs.workSheets("XB")
  Set ws3 = wbs.workSheets("XC")
  'Set ws4 = wbs.workSheets("XD")

 
 
  Set objFile = objFSO.OpenTextFile(ObjDialog.FileName, 1, True)
 
rwXA = 2
rwXB = 2
rwXC = 2
rwXD = 2
 
  Do While Not objFile.AtEndOfStream
 
    sText = objFile.ReadLine


If Trim(Left(sText, 4)) = "XA" Then

   ws1.Range("A" & rwXA).Value = mid(sText,1, 4)
   ws1.Range("B" & rwXA).Value = mid(sText,5, 10)
   
    rwXA = rwXA+1
    End if
   
  Loop

  objFile.Close
  excel.DisplayAlerts = False
  wbs.Save
  wbs.Close 0
  excel.DisplayAlerts = true
  excel.quit
  set excel = nothing
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
Chris BottomleyCommented:
Reference your original, and the corrected post I made earlier please note the fundamental error was the reference to worksheets rather than worksheet.

Other than that your code was fine.

Chris
0
 
drezner7Author Commented:
Thank Chris, I have gone back to my original post and I have made the changes based on your suggestions and I came up with this: (But, it is thrwoing an 'out of Range Error ') I am assuming I did something wrong

Set excel = WScript.CreateObject ("Excel.Application")
excel.Visible = false

excel.Workbooks(1).Sheets.Add, excel.Workbooks(1).Sheets(excelWorkbooks.Sheets.Count),19
0
 
Chris BottomleyCommented:
You still didn't quite copy it all:

excel.Workbooks(1).Sheets.Add,  excel.Workbooks(1).Sheets(excel.Workbooks(1).Sheets.Count),19

Missed a dot in the count and the workbooks(1) qualifier

Chris
0
 
drezner7Author Commented:
Thank you Chris, I have copied the line correcty, but it is still throwing the same error


Set excel = WScript.CreateObject ("Excel.Application")

excel.Visible = false

excel.Workbooks(1).Sheets.Add,  excel.Workbooks(1).Sheets(excel.Workbooks(1).Sheets.Count),19

Set objFSO = CreateObject("Scripting.FileSystemObject")
0
 
Chris BottomleyCommented:
You are running this within your overall framework such that for example there is a workbook open in excel?

Chris
0
 
Chris BottomleyCommented:
for example:


Set excel = WScript.CreateObject ("Excel.Application")

excel.Visible = false
Set wbs = excel.Workbooks.Add

excel.Workbooks(1).Sheets.Add,  excel.Workbooks(1).Sheets(excel.Workbooks(1).Sheets.Count),19

Set objFSO = CreateObject("Scripting.FileSystemObject")

Open in new window

0
 
drezner7Author Commented:
If I understand your question correctly, I do not have an open workbook, the script calls the workbook to open. I think I may have a problem. I think the way it is written is that it is trying to place worksheets in a closed book, based on my code here.

Set excel = WScript.CreateObject ("Excel.Application")
excel.Visible = false

excel.Workbooks(1).Sheets.Add,  excel.Workbooks(1).Sheets(excel.Workbooks(1).Sheets.Count),19

The workbook gets opened here:

Set objFSO = CreateObject("Scripting.FileSystemObject")

  Set wbs = excel.Workbooks.Open(sXLS)
 
 'Add Spreadsheet to Workbook
 
  wbs.WorkSheets(1).Name = "XA"
  wbs.WorkSheets(2).Name = "XB"
  wbs.WorkSheets(3).Name = "XC"
  'wbs.WorkSheets(4).Name = "XD"
0
 
drezner7Author Commented:
Chris, based on your question I have solved the problem. My assumption was correct and I was putting the code in the wrong place. Thank you so, I am still learning this language, but it is a lot of fun. Thank you for all of your help, your suggestions of the code works great.

thank you
0
 
Chris BottomleyCommented:
Used a bit of pick and mix to produce:

Chris
sXLS = "C:\Sample\parse.xlsx"
Set excel = WScript.CreateObject ("Excel.Application")
excel.Visible = false

The workbook gets opened here:

Set objFSO = CreateObject("Scripting.FileSystemObject")

  Set wbs = excel.Workbooks.Open(sXLS)
  wbs.Sheets.Add,  wbs.Sheets(wbs.Sheets.Count),19

 'Add Spreadsheet to Workbook
  
  wbs.WorkSheets(1).Name = "XA"
  wbs.WorkSheets(2).Name = "XB"
  wbs.WorkSheets(3).Name = "XC"
  'wbs.WorkSheets(4).Name = "XD"

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now