CODEWITHSUNDEEP
excelcsvvbscript
Adam Harvey
Adam Harvey

Reputation: 55

Trying to convert XLS to CSV and CSV to XLS using VBScript. Receiving error: 800A03EC (Unable to get the Open property of the Workbooks class)

I'm trying to open a CSV then use the SaveAs method to save it as an XLS. Also vice-versa in another script. I accidentally had the file format codes wrong before and was not getting this error. The CSV would in fact open. I accidentally had made the CSV format 2 (which is actually SYLK) and the XLS, 6 (which is actually CSV).

I've looked all over, and most of what I can find has to do with using an incorrect argument (which I have checked multiple times). The rest is for ASP, and suggests changing permissions in Component Services (which probably wouldn't be an issue anyway, since I can get the Open method to work with different formats).

So I'm at a loss as to how to proceed. If I can't even use the Open method, then I'm kind of stuck. If it was as simple as thee SaveAs method not working for this task, I could get around that. But I need to be able to open an XLS using the Open method (since I'm also trying to do XLS to CSV). CSV to XLS can be fixed another way, probably, since the Open method seems to work sometimes.

Anyway, my code for the CSV to XLS is below. The XLS to CSV is essentially identical to this. It just flips the format codes and uses different paths for the files.

strName = "MidCSVTemp.csv"
strSaveName = Month(Now) & "." & Day(Now) & "." & Year(Now) & ".xls"
strPath = "C:\Users\adam\Documents\" & strName
strSavePath = "C:\Users\adam\Documents\" & strSaveName

'Options for Workbook.Open
intUpdateLinks = 0
boolReadOnly = False
intFormat = 6

'Options for SaveAs
intFileFormat = 56

Set objExcel = CreateObject("Excel.Application")
Set objWorkBook = objExcel.Workbooks.Open(strPath,intUpdateLinks,boolReadOnly,intFormat)
Call objWorkBook.SaveAs(strSavePath,intFileFormat)
Call objWorkbook.Close

Upvotes: 1

Views: 684

Answers (1)

Bond
Bond

Reputation: 16311

I think the issue is that you're using the Format parameter of the Workbooks.Open() method like it's the FileFormat parameter. It shouldn't be xlCSV (6), which is a FileFormat constant. According to the docs, Format should be one of the following values:

  • 1 = Tabs
  • 2 = Commas
  • 3 = Spaces
  • 4 = Semicolons
  • 5 = Nothing
  • 6 = Custom

Since you're passing a value of 6, it's expecting that you also include the Delimiter argument. And since you're not including it, you're getting an error.

You should be able to open a CSV without specifying the Format parameter (Excel seemed to guess the delimiter correctly for me without having to specify it). But, to be safe, pass a value of 2 for a comma-delimited (CSV) file.

intFormat = 2
Set objWorkBook = objExcel.Workbooks.Open(strPath,intUpdateLinks,boolReadOnly,intFormat)

Upvotes: 1

Related Questions