472,796 Members | 1,756 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,796 software developers and data experts.

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

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.
Mar 5 '08 #1
8 9511
1,356 Expert 1GB
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.
Mar 5 '08 #2
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.
Mar 5 '08 #3
1,356 Expert 1GB
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.
Mar 5 '08 #4
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
'app disappears but is still in processes
'everything seems to be closed
Set ObjXLBook = Nothing
Set ObjXL = Nothing
Mar 6 '08 #5
1,356 Expert 1GB
‘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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 6 '08 #6
1,356 Expert 1GB
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).
Mar 6 '08 #7
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.
Mar 7 '08 #8
1,356 Expert 1GB
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.
Mar 7 '08 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

by: fkulaga | last post by:
Hi all, I have a problem with the issue in the subject, i have all data in one big excel file, in a denormalized form, and on the other side, i have mysql database with many tables, which is...
by: Ashish Tiwari | last post by:
Can i read excel file into asp.net 2.0
by: Steve | last post by:
Hi All, I have an Excel file being delivered to a shared drive where I only have read access on a daily basis. The files arrive in the following format 'Filename+timestamp.xls' Example:...
by: JFKJr | last post by:
Hello everyone! I am trying to export Access table data into Excel file in such a way that the table field names should be in the first line of each column in excel file followed by field data, and...
by: jitendrawel | last post by:
how can be import excel file in mysql database through PHP code. Please send me complete code ASAP. Thanks. Best regards, Jitendra Kumar
by: vikassawant | last post by:
Hi, I want to import Excel file data in JTable.For that I am using following Code, String excelFileName = "Team1.xls"; File file = new File(excelFileName ); //‘file’ is the file you...
by: orked | last post by:
i tried to import excel file into gridview but the code made this exception (Failed to map this path''xmlTest.xml") i don't know why my code: public void fillGrid() { DataSet ds =...
by: provor | last post by:
Hello, I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this...
by: dowlingm815 | last post by:
I am importing an Excel 97-2003 file into Access 2003. The number of rows within the Excel file is 101,9992. When completed the Access import, only 65,535 are import. Is Access limited, if so,...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.