Solved

How do I import a htm file into a table

Posted on 2015-01-31
7
341 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 35

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Question about DB Schema 27 56
deduplicating based on criteria 2 21
Help with DoEvents 8 28
MS Access from Delphi 31 32
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

803 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