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.
Rob4077Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this codes, from access

dim xlObj as object, strFile as string
strFile="<path and filename>"
set xlObj = CreateObject("excel.application")
     xlObj.application.displayalerts=false
     xlObj.Workbooks.Open strFile

    xlObj.activeworkbook.saveas strfile, fileformat:=56

xlObj.quit
0
 
Dan CraciunIT ConsultantCommented:
From Excel, save it as a xls or xlsx file, then you'll be able to import it into Access.

HTH,
Dan
0
 
Rob4077Author Commented:
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?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
PatHartmanCommented:
Did you try GetExternalData/More/HTML?
0
 
Jeffrey CoachmanMIS LiasonCommented:
<No Points Wanted, just FYI>
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
Then this file is actually a .xlsx file...
So if you changed the extension to.xlsx, ...it should open in Excel 2007 or higher without issue.

JeffCoachman
0
 
Dan CraciunIT ConsultantCommented:
The xslx is a zip. Do you think you'll see html code in it if you open it with Notepad?
0
 
Rob4077Author Commented:
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
0
All Courses

From novice to tech pro — start learning today.