Looping through worksheets in an Excel (xls) file using classic ASP on a win 2008 server

Question

Hi,

I have just migrated an older ASP solution to a windows 2008 server and everything works out fine except for using ADOX.Catalog to list all the worksheets in an uploaded xls file. I use the following code:

<% Set objExcelCon = Server.CreateObject("ADODB.Connection") With objExcelCon .Provider = "Microsoft.ACE.OLEDB.12.0" .ConnectionString = "Data Source=" & strFilePath & "\" & strFileName & ";Extended Properties=Excel 12.0;" .Open End With

Set objAdo = Server.CreateObject("ADOX.Catalog")

' Set ADOX activeConnection objAdo.activeConnection = objExcelCon

' Loop through tables (worksheets) For i = 0 To objAdo.Tables.Count - 1 response.Write objAdo.Tables(i).Name Next

Set objExcelCon = Nothing Set objAdo = Nothing %>

When I run this i get no error message or anything. Anyone got any idea whats causing this?

Best regards, Roy

Answer 1

First of all, are you sure you get no error messages? No "On Error Resume Next" tricking you?

Also, since you are using this specific version, is this the new style ZIP/XML (xlsx) file format?

Answer 2

You do have

 <% Option Explicit %>

at the top of your page, no ? Do you can see that you are not using a variable you have not used before ?

Also, write out the i value in the loop, so you see whether you are looping something.