How do I import a htm file into a table
A computer system that we use generates a weekly report that can be exported to excel. The file created has a .xls extension but when I open the file MS Excel says it's not in the correct format. Nevertheless it will open the file into a standard spreadsheet.
When I looked at the file with NotePad I realised it's actually a HTML file and if I change the extension to .html and launch it, it opens the spreadsheet in my web browser.
What I need to do is import the data into an Access table so I can then extract some of the data and save it in a format that we need for another application.
When I looked at the file with NotePad I realised it's actually a HTML file and if I change the extension to .html and launch it, it opens the spreadsheet in my web browser.
What I need to do is import the data into an Access table so I can then extract some of the data and save it in a format that we need for another application.
ASKER
That works but it means I have to:
1. export the file,
2. open it,
3. do a save as using the same name and make sure I save as xls file
4. replace the existing file with the same name.
Is there an easy way I can get MS Access to do that? Or do I need to get Access to launch an Excel session and do the process? If I do that, can I suppress the warning that the file is not in a proper xls format?
1. export the file,
2. open it,
3. do a save as using the same name and make sure I save as xls file
4. replace the existing file with the same name.
Is there an easy way I can get MS Access to do that? Or do I need to get Access to launch an Excel session and do the process? If I do that, can I suppress the warning that the file is not in a proper xls format?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you try GetExternalData/More/HTML?
<No Points Wanted, just FYI>
So if you changed the extension to.xlsx, ...it should open in Excel 2007 or higher without issue.
JeffCoachman
The file created has a .xls extension but when I open the file MS Excel says it's not in the correct format. Nevertheless it will open the file into a standard spreadsheet.Then this file is actually a .xlsx file...
When I looked at the file with NotePad I realised it's actually a HTML
So if you changed the extension to.xlsx, ...it should open in Excel 2007 or higher without issue.
JeffCoachman
The xslx is a zip. Do you think you'll see html code in it if you open it with Notepad?
ASKER
Hi Jeff, Just tried renaming the extension to xlsx but it still came up with the same warning so your conclusion doesn't apply to this type of file.
Dan, your solution worked but I wanted to automate it
Rey, your solution worked perfectly. Thanks
Dan, your solution worked but I wanted to automate it
Rey, your solution worked perfectly. Thanks
HTH,
Dan