Access Can't Import An Excel File Unless It Is Open

[shenkel55]

I'm using Access and Excel 2003. Using either the import wizard or code, I have the same problem. This problem only happens with Excel files automatically generated by Corp IT. If I try to do an import and the Excel file isn't open I get the following error: "The wizard is unable to access information in the file "...path info... "Please check that the file exists and is in the correct format." If the files are opened directly in Excel, it has no error reading the format.

The Excel file exists, its file extension is .xls and the properties have it listed as Microsoft Excel Worksheet. If you open the Excel file and select save as from the File menu, you get a strange looking screen. The file name box has the file name and the save as type box says "Web Page" by default. There is also a Publish button. So the local IT thinks that corporate IT is getting this file into some different format than an Excel file - but it has the same .xls name.

Any Excel files we create ourselves and copy to the network don't need to be open to be imported. They also do not have that weird save as box.

So the question is (since Corporate IT will not help or make any changes) what are they doing to the files that's causing them to have the save as web page as the default type? What can I do to circumvent the need to open every file before I import it, if anything?

I've considered using automation to open every file in the background and then perform the import process, but it kept leaving an open instance of Excel in the background, no matter what I tried, so I abandoned that idea with the thought that there had to be something easier.

[Denburt]

It is possible that they could be using a different version of office which is confusing MS Access however from what you are describing I think it is more likely that they are using an html file and someone possibly changed the extension.
I would suggest opening the file in notepad and take a look to see if this is the case if it is you should be able to open it and import it using a file stream.

[shenkel55]

I just opened the .xls file in notepad and it is indeed an html file. I didn't know Excel could open that just like a workbook (it looks like it has an Excel namespace). Thank you. I will look into the file stream idea.

[Denburt]

Exactly as I thought, if you would like you could open the file then save it as an
excel sheet close it then perform the import procedure. So many options....

Glad I could help, oh and also you didn't post any code but I think your problem with opening the file then closing it comes into play for a lot of people. Most of the time people create an instance of the excel app using say createObject() or New excel.app then they open the excel sheet. Generally when you open an excel sheet it will start an instance of the excel app then and there so actually you ended up with two instances of excel and only one closes. If you would like to post the code you are using for this then I will gladly look it over for you.

[shenkel55]

I abandoned my old code a couple of months ago. I rewrote it today after reading your message, and it seems to open and close Excel instances as it should. If you see any gotchas coming, let me know.

Also this is supposed to be an automated app... I don't want to be here opening and saving tables, and I'm not sure the end users want to go through that procedure... and the way the files are coming in isn't going to change

Dim ObjXL As Excel.Application
Dim ObjXLBook As Excel.Workbook
Dim ObjXLSheet As Excel.Worksheet

Set ObjXL = CreateObject("Excel.Application")
Set ObjXLBook = ObjXL.Workbooks.Open(strPath)
Set ObjXLSheet = ObjXLBook.Sheets(1)
‘I can’t get that line to run if I use the name of the worksheet instead of the index (?)

ObjXL.Visible = True

‘this is an attempt to find the last populated cell in the sheet, but it returns a row that’s 6 higher than the last row – I think it’s the html and blank lines they put in there

Dim LastCellAddress As String

LastCellAddress = ObjXL.ActiveCell.SpecialCells(xlLastCell).Address
MsgBox LastCellAddress

‘end that attempt

Set ObjXLSheet = Nothing
ObjXLBook.Close
'app disappears but is still in processes
ObjXL.Quit
'everything seems to be closed
Set ObjXLBook = Nothing
Set ObjXL = Nothing

[Denburt]

‘I can’t get that line to run if I use the name of the worksheet instead of the index (?)

‘this is an attempt to find the last populated cell in the sheet, but it returns a row that’s 6 higher than the last row – I think it’s the html and blank lines they put in there

Looks good just a couple of things, you may want to change the following to false before distribution. They may want top view it but they have to realize that clicking in a cell or changing to another sheet could interrupt the procedures and or cause it to hang.

ObjXL.Visible = True

Strange that you are not able to set this using a sheet name you should be able to. I just checked to verify and it worked fine on this end.
Set ObjXLSheet = ObjXLBook.Sheets("Sheet1")

Yeah finding the last row can be a real pain sometimes. Is there a reason you are looking to grab the last row? Generally speaking you can just import that sheet and not need to specify a range. If you feel the need to find that address then try saving the sheet as an actual excel sheet ObjXLSheet.SaveAs("c:\tmp\newTEST.xls") then try to see if it will grab the last row. You can also try transfertext() method and specify that html tables name but for that to work you may need to rename the xls extension to html.

HTMLTableName Optional Variant. A string expression that's the name of the table or list in the HTML file that you want to import or link. This argument is ignored unless the transfertype argument is set to acImportHTML or acLinkHTML. If you leave this argument blank, the first table or list in the HTML file is imported or linked. The name of the table or list in the HTML file is determined by the text specified by the <CAPTION> tag, if there's a <CAPTION> tag. If there's no <CAPTION> tag, the name is determined by the text specified by the <TITLE> tag. If more than one table or list has the same name, Microsoft Access distinguishes them by adding a number to the end of each table or list name; for example, Employees1 and Employees2.

Renaming a file is simple enough:

Name oldFilename As newFilename
 

I think I covered it all if you have any more questions comments I would love to hear them. If I think of anything else I will post back.

[Denburt]

Oh and if you really have to you can always try activating cell 65536 and try to do the same just going up (can't remember the command).

[shenkel55]

I am having trouble with the code leaving an open instance of Excel today. I stuck an importspreadsheet line in the center of the open and close Excel, where the "find the last cell" code was. This is the line

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strFileName, strPathName, FieldNamesInRowOne

So why is that stopping it from closing Excel in the processes screen of task manager? Is that command opening another instance of Excel? I thought everything was going so well yesterday. Could you please let me know if you have any ideas? Otherwise the whole thing works beautifully.

The reason for me reading the last cell really has more to do with my other posted q (that you answered). I'm new to this forum, so I don't know how much I can intermingle the topics. I will try to send a message about that part.

[Denburt]

Interesting effect, I just ran it that way myself and received the same results Excel did keep an instance alive. So did you try to save it as an Excel file? If you did save it as an excel file then you should be able to close it, then import it and you shouldn't have this issue.