Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I import a htm file into a table

Posted on 2015-01-31
7
Medium Priority
?
508 Views
Last Modified: 2015-01-31
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.
0
Comment
Question by:Rob4077
7 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 40581312
From Excel, save it as a xls or xlsx file, then you'll be able to import it into Access.

HTH,
Dan
0
 

Author Comment

by:Rob4077
ID: 40581326
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40581342
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 40

Expert Comment

by:PatHartman
ID: 40581495
Did you try GetExternalData/More/HTML?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40582031
<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
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 40582166
The xslx is a zip. Do you think you'll see html code in it if you open it with Notepad?
0
 

Author Comment

by:Rob4077
ID: 40582180
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question