Solved

How do I import a htm file into a table

Posted on 2015-01-31
7
313 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 34

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 119

Accepted Solution

by:
Rey Obrero earned 500 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 34

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 34

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now