Solved

Open html file with excel2010 and save as excel2010 file.

Posted on 2014-11-25
11
172 Views
Last Modified: 2014-11-26
If I download an html page and save it, I can open it in excel. If I then save it as an Excel file, I can read the table values into an Access table.

I need VBA to

Open the html file e.g. SACA.html from C:\SAQA_info\ using EXCEL (via Access) and then save it as C:\SAQA_info\SACA.xlsx
and close it.

Will you help please?
0
Comment
Question by:Fritz Paul
  • 6
  • 4
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40464375
can  you upload SACA.html?

try something like this

dim xlObj as object
set xlObj=createobject("excel.application")
      xlObj.workbooks.open "<path to html>"

     xlObj.activeworkbook.save as "<aaaa.xlsx>"
     xlObj.quit
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40464379
Hi,

pls try

Sub Macro()

 Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    
    strPath = "C:\SAQA_info\"
    strFileNoExt = "SACA"
    
    Set oExcel = CreateObject("Excel.Application")

    Set oBook = oExcel.Workbooks.Open(strPath & strFileNoExt & ".html")
    oExcel.Visible = True

    oBook.SaveAs (strPath & strFileNoExt & ".xlsx")
    oBook.Close False
    
    oExcel.Quit


End Sub

Open in new window

Regards
0
 

Author Comment

by:Fritz Paul
ID: 40464502
I have tried Rgonzo's code, but the html file is not opening.
When I open it manually  there is a load error on which I click on OK and it opens. I think that may be the proble.
I attach a zip file with a directory which I have on my C drive. The html file is also in there.
I also have a simple database with one form with one button to execute the code. The database opens on this form.
Please unzip and see if you can help.
SAQA-Info.html
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Fritz Paul
ID: 40464505
Sorry I see the zip file did not attach. I will attach the sample database.
0
 

Author Comment

by:Fritz Paul
ID: 40464519
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40464583
if your file is C:\SAQA_info\SAQA-Info.html

then try

 Sub Macro()

 Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    
    strFile1 = "C:\SAQA_info\SAQA-Info.html"
    strFile2 = "C:\SAQA_info\SACA.xlsx"
    
    Set oExcel = CreateObject("Excel.Application")

    Set oBook = oExcel.Workbooks.Open(strFile1)
    oExcel.Visible = True

    oBook.SaveAs (strFile2)
    oBook.Close False
    
    oExcel.Quit


End Sub

Open in new window

0
 

Author Comment

by:Fritz Paul
ID: 40464622
I tried this, but the code still freezes up on this line (It stays in running mode and does not go further in the code)

Set oBook = oExcel.Workbooks.Open(strFile1)

When I open manually it give s an error message like below and I suspect that that is the error message that the VBA does not handle.

Error message
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40464649
pls give complete path of file
0
 

Author Comment

by:Fritz Paul
ID: 40464701
The path is illustrated below.
It is "C:\SAQA_QUI\SAQA_Info.html"   copied from created shortcut
I attach the file and the database. If you save in "C:\SAQA_QUI\" you have my precise setup.
I use Office proff 2010.

The code that I have on my form is

    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    
    strFile1 = "C:\SAQA_QUI\SAQA_Info.html"
    '           "C:\SAQA_QUI\SAQA_Info.html"   copied from created shortcut
    strFile2 = "C:\SAQA_QUI\SACA.xlsx"
    
    Set oExcel = CreateObject("Excel.Application")

    Set oBook = oExcel.Workbooks.Open(strFile1)
    oExcel.Visible = True

    oBook.SaveAs (strFile2)
    oBook.Close False
    
    oExcel.Quit

Open in new window


Complete Path illustrated
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40466240
Sorry can't help further
0
 

Author Closing Comment

by:Fritz Paul
ID: 40466634
I am so thankful that you tried so hard to help.
Eventually it dawned on me that I should try to supply the "missing" style sheet and now I copy that together with the html file from the internet and save it where Excel is missing it when it opens the html file.
It work s very nice now.
I want to reward you. People may shy away from trying to help if it is a difficult subject.
Thanks again.
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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

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