Open html file with excel2010 and save as excel2010 file.

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?
Fritz PaulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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
Rgonzo1971Commented:
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
Fritz PaulAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Fritz PaulAuthor Commented:
Sorry I see the zip file did not attach. I will attach the sample database.
0
Fritz PaulAuthor Commented:
0
Rgonzo1971Commented:
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
Fritz PaulAuthor Commented:
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
Rgonzo1971Commented:
pls give complete path of file
0
Fritz PaulAuthor Commented:
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
Rgonzo1971Commented:
Sorry can't help further
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fritz PaulAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.