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?

Improve company productivity with a Business Account.Sign Up

x
 
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.