CODEWITHSUNDEEP
excelvbavbscript
oaklodge
oaklodge

Reputation: 748

VBA to VBS for ActiveWorkbook

I'm trying to convert some VBA code that I embed in Excel spreadsheets to save all tabs as CSVs into a VBS script that I can run from command line to do the same thing.

Set objFSO = CreateObject("Scripting.FileSystemObject")
src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)
Dim worksheetCount
workSheetCount = oExcel.Worksheets.Count
oExcel.DisplayAlerts = False
Dim counter
Dim currentWorkSheet
For counter = 1 to workSheetCount
    Set currentWorkSheet = oBook.Worksheets(counter)
    currentWorkSheet.Copy
    oExcel.ActiveWorkbook.SaveAs oBook & "-" & currentWorkSheet.Name & ".csv", 6
    oExcel.ActiveWorkbook.Close False
    ThisWorkbook.Activate
Next    
oExcel.DisplayAlerts = True
oBook.Close False
oExcel.Quit

The error occurs at the beginning of line "oExcel.ActiveWorkbook.SaveAs" and says object doesn't support this method. So I assume the Excel object doesn't have the ActiveWorkbook method? Is there a VBA->VBS conversion here, some other method I should be using?

EDIT

This works for me now:

Set objFSO = CreateObject("Scripting.FileSystemObject")
src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

Dim strCurDir
strCurDir = WshShell.CurrentDirectory & "\" & oBook.Name & "-"

Dim worksheetCount
workSheetCount = oExcel.Worksheets.Count

oExcel.DisplayAlerts = False

Dim counter
Dim currentWorkSheet
For counter = 1 to workSheetCount
    Set currentWorkSheet = oBook.Worksheets(counter)
    currentWorkSheet.Copy
    oExcel.ActiveWorkbook.SaveAs strCurDir & currentWorkSheet.Name & ".csv", 6
    oExcel.ActiveWorkbook.Close False
Next

oExcel.DisplayAlerts = True
oBook.Close False
oExcel.Quit

Upvotes: 1

Views: 2565

Answers (2)

Ebender2
Ebender2

Reputation: 1

Refer to the worksheet using the index number, or do a for each loop, where the each item is each wksh in workbook.worksheets.
Also, I moved the worksheet.close method to after the for loop, because it would close out your workbook before you were finished

Set objFSO = CreateObject("Scripting.FileSystemObject")
src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)
Dim worksheetCount
workSheetCount = oExcel.Worksheets.Count
oExcel.DisplayAlerts = False
Dim counter
Dim currentWorkSheet
For counter = 1 to workSheetCount
Set currentWorkSheet = oBook.Worksheets(counter)
    currentWorkSheet.Copy
    oExcel.Worksheets(counter).SaveAs oBook & "-" & currentWorkSheet.Name & ".csv", 6
    'ThisWorkbook.Activate
Next
oExcel.Worksheets(counter).Close False
oExcel.DisplayAlerts = True
oBook.Close False
oExcel.Quit

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149325

The problem is not with ActiveWorkbook. The problem is that you are not specifying a complete path.

Specify a path and it will work. For example

oExcel.ActiveWorkbook.SaveAs "C:\MyFolder\" & _
oBook.Name & "-" & currentWorkSheet.Name & ".csv", 6

You will get next error on ThisWorkbook.Activate. Fully qualify it and it will work.

TIP

You may want to remove the file extension and then save the file ;) Something like this

oExcel.ActiveWorkbook.SaveAs "C:\MyFolder\" & _
Left(oBook.Name, (InStrRev(oBook.Name, ".", -1, vbTextCompare) - 1)) & _
"-" & currentWorkSheet.Name & ".csv", 6

Upvotes: 2

Related Questions