I have a file from a client that has 3 tabs. The tabs are not consistently named from month to month but the name of the tab determines which client # we use to load the data into our system. The columns are not the same from tab to tab. What I need to do for down line processing is to save each tab as a separate CSV file naming the file with the name of the tab which I've standardized to make it consistent for the down line process.
Everything up to the SaveAs line works fine. It keeps failing with "Unable to get the SaveAs property of the workbook class". I've spent hours trying different ways to do this can't figure it out. The debug file is attached but the Winbatch.ini portion appears to contain stuff from another process.
Any help would be appreciated!
ctlname = "C:\xx\Bayfront+Collections+02.03.14.xlsx"
;Start Excel - this will be the copy that saves the pasted data as CSV
objXL = ObjectCreate("Excel.Application")
objXL.Visible = @TRUE
objXL.DisplayAlerts = @FALSE
xlconst = ObjectConstantsGet(objXL)
objWorkbooks = objXL.Workbooks.Add
wscount = objXL.Workbooks(1).Worksheets.Count
If wscount < 1 ;make sure at least one sheet is made
objXL.Worksheets.Add
EndIf
;Start 2nd copy of Excel to open the client XLSX file
objXL2 = ObjectCreate("Excel.Application")
objXL2.Visible = @TRUE
objXL2.DisplayAlerts = @FALSE
objWorkbook2 = objXL2.Workbooks
objWorkbook2.Open(ctlname)
objActiveWorkbook2 = objXL2.ActiveWorkbook
wscount2 = objXL2.Workbooks(1).Worksheets.Count
For i = 1 to wscount2
objWorksheet2 = objXL2.Worksheets
objWS2 = objXL2.Worksheets(i)
objWS2.Activate
sheetname = objXL2.WorkSheets(i).Name
If StrIndexNC(sheetname, "gyn", 1, @FWDSCAN) > 0 Then newname = "BayfrontOBGYN.csv"
If StrIndexNC(sheetname, "fmc", 1, @FWDSCAN) > 0 Then newname = "BayfrontFMC.csv"
If StrIndexNC(sheetname, "ccc", 1, @FWDSCAN) > 0 Then newname = "BayfrontCCC.csv"
objXL2.ActiveSheet.UsedRange.Copy
objXLWorkbook = objXL.Workbooks
objXLActiveWorkbook = objXL.ActiveWorkbook
objWorksheet = objXL.Worksheets
objWS = objXL.Worksheets(1)
objWS.Activate
objWS.Paste
objWorkbooks.SaveAs (newname, 6 , , , , ,@TRUE)
objActiveWorkbook2.Close
objXL2.Quit
objXL2.Quit
objXL2 = ""
objWorksheet2 = ""
objWS2 = ""
Next
objXL.Quit
EXIT
Why two instances of excel? Here is some simplified code that should help:
;***************************************************************************
;** Save Individual Excel Worksheets into CSV files
;**
;** Purpose: Save Individual Excel Worksheets into CSV files
;** Inputs: Multisheet Excel file
;** Outputs: {sheetname}.CSV files saved to script directory
;** Reference:
;**
;**
;** Developer: Deana Falk 2014.02.27
;***************************************************************************
source_xls = "C:\TEMP\Data\Excel\multisheet.xls"
xlCSV = 6 ; Excel constant value for the file format xlCSV.
ObjExcel = ObjectCreate("Excel.Application")
ObjExcel.Visible = @TRUE
ObjExcel.DisplayAlerts = @FALSE
ObjWrkBook = ObjExcel.Workbooks.Open ( source_xls )
; Get Worksheet Names
For x = 1 To ObjWrkBook.Worksheets.count
ObjWrkSheet = ObjWrkBook.Worksheets(x)
wrksheetname = ObjWrkSheet.name
; Specify the CSV file path
csvname = DirScript(): wrksheetname: '.csv'
if FileExist( csvname ) then FileDelete( csvname )
; Save Activate Worksheet
ObjWrkSheet.Activate
ObjActSheet = ObjWrkBook.ActiveSheet
ObjActSheet.SaveAs(:: Filename="%csvname%", FileFormat=%xlCSV%, CreateBackup=0)
ObjWrkSheet = 0
ObjActSheet = 0
Next
ObjWrkBook.Close()
ObjExcel.Quit
ObjExcel = 0
Exit
Thanks - that does exactly what I need. I'm awful with Excel automation!