saving xls as html


Posted by Bryce Winter on December 27, 2001 8:41 AM

I need to open an xls file using the application object and save the xls workbook as an xlHTML file. I am having trouble with the syntax for the .SaveAs method. As this can not be run in a macro, I think the syntax is a little different. Here's what I have so far.

Function saveAsHTML()
Dim objXLApp
Dim objXLBook
'create excel application object
set objXLApp = CreateObject("Excel.Application")

If IsObject(objXLApp) Then

'we have an application object. continue with translation
msgBox("Created object successfully. Ready to create workbook object.")
'open the workbook specified on uploadReport in the session("filename") var
set objXLBook = objXLApp.Workbooks.Open("<%=strFile%>")
If IsObject(objXLBook) Then
msgBox("Opened workbook object successfully. Ready to open file.")
'we have the workbook object. continue with translation
objXLBook.SaveAs("test4.htm", xlHtml)
objXLBook.Close 'Close the workbook
set objXLBook = nothing 'destroy excel wkbk object
Else
msgBox("There was an error creating the workbook object.")
End If
set objXLApp = nothing 'destroy excel app object
Else
'there was an error creating the excel application object
msgBox("Could not create Excel Application Object.")
set objXLApp = nothing 'manually destroy app object just in case.
End If
End Function

Everything goes as planned until I hit the saveas line. Any suggestions?

Posted by Mike on December 27, 2001 10:41 AM

Try:

result=objXLBook.SaveAs("test4.htm", xlHtml)

It looks like it needs to return an argument.

-Mike

Posted by Juan Pablo G. on December 27, 2001 12:06 PM

Or try

objXLBook.SaveAs "test4.htm", xlHtml

Excel complains a lot about lack or excess of parenthesis.

Juan Pablo G.

Posted by Bryce Winter on December 28, 2001 11:39 AM

The problem was that the filename argument is a Long not a string when being used outside of excel and not in a macro. Now the question is where do I find the numerical values of the file types supported by xl? Or try objXLBook.SaveAs "test4.htm", xlHtml Excel complains a lot about lack or excess of parenthesis. Juan Pablo G.



Posted by Mike on December 29, 2001 8:54 PM


<SNIP>

Here are all the types listed in the Excel VBA help.

xlAddIn
xlCSV
xlCSVMac
xlCSVMSDOS
xlCSVWindows
xlCurrentPlatformText
xlDBF2
xlDBF3
xlDBF4
xlDIF
xlExcel2
xlExcel2FarEast
xlExcel3
xlExcel4
xlExcel4Workbook
xlExcel5
xlExcel7
xlExcel9795
xlHTML
xlIntlAddIn
xlIntlMacro
xlSYLK
xlTemplate
xlTextMac
xlTextMSDOS
xlTextPrinter
xlTextWindows
xlUnicodeText
xlWJ2WD1
xlWK1
xlWK1ALL
xlWK1FMT
xlWK3
xlWK4
xlWK3FM3
xlWKS
xlWorkbookNormal
xlWorks2FarEast
xlWQ1
xlWJ3
xlWJ3FJ3