Excel help needed

[dblaze1]

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!

Code Select Expand


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











[Deana]

Why two instances of excel? Here is some simplified code that should help:

Code (winbatch) Select Expand

;***************************************************************************
;**   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

[dblaze1]

Thanks - that does exactly what I need.  I'm awful with Excel automation!