I need to read CSV files and convert them into Excel. I can do it by reading each line and then writing to each cell but that is too slow as there are thousands of records in a file. I was able to open the CSV file into an excel spreadsheet using the following code which I copied from the techsupport. However, it does not seem to work as I can't open it later. I'm not sure where the format of -4143 comes from and think that may be the issue since I can save it manually once the sheet is open and then it is fine. The pertinent code is as follows. Any help would be greatly appreciated.
BoxOpen("Creating Excel WorkBook",cText)
xlNormal = -4143
oAPP = ObjectOpen( "Excel.Application" )
oAPP.Visible = @true ; @FALSE
oAPP.DisplayAlerts = @FALSE
oWK = oAPP.workbooks
oWK.open(filename)
oACT = oAPP.ActiveWorkbook
oWKS = oAPP.Worksheets
oWS = oAPP.Worksheets(1)
oWS.Activate
; gosub AutoFit
oACT.SaveAs ( cXLS,xlNormal , , , , ,@True )
On the surface the code you supplied appears correct and -4143 should save the file as an Excel file format. You do not indicate how you are closing objects and instances which may hinder opening the file. Perhaps you could provide a .csv snippet to test.
Add a couple of file names to the OP's script and it worked just fine. No problems manually opening the file with Excel. The problem could be an issue with an alternate data stream or a problem with the permissions on the file itself or the folder containing the file.
I changed the extension from .xlsx to .xls and then I was able to open the file. Thanks for the replies.
Interesting. Apparently, MSFT decided the the file extension is the sole determiner of an Excel file's format.
Also, the 'SaveAs' method's second parameter is 'FileFormat'. You may be able to use that to create an XML version of your spread sheet, if that is your preference.
https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas.aspx (https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas.aspx)