SaveAs and Excel Automation

L

L Norton

I am saving a workbook as HTML in VB.Net -- What I would like to do is
save each page separately but when I loop through each sheet in the
file a folder is created with an HTML file for every sheet in the
workbook. Also, I would like to save these pages without the tab strip
at the bottom.

I have tried saving each sheet as its own xls file (SaveAS xls with
appropriate extension) but get a tab sting then as well. How can you
save individual sheet in a workbook without the strip at the bottom. I
must be missing something but can't find a saveAs setting to remove
this...

Here is the relevant code:


objExcel.Workbooks.Open("C:\MyDocs\MyFile.xls")
For Index = 1 To objExcel.ActiveWorkbook.Worksheets.Count

Try
myFileHTML = CStr(System.DateTime.Now.Millisecond) +
CStr(System.DateTime.Now.Minute) + CStr(System.DateTime.Now.Minute) +
objExcel.Worksheets.Item(Index).Name + ".html"

objExcel.ActiveSheet.SaveAs(Filename:="C:\Inetpub\wwwroot\ExcelHTMLFile\"
+ myFileHTML, FileFormat:=xlHtml)

Catch ex As Exception
.....
End Try

Next
 
R

Rob Bovey

Here's a VBA example that exports each worksheet as an individual file
with the same name as the worksheet. You shouldn't get any sheet tabs or
extra folders using this method:

Sub SaveSheetsAsHTML()

Dim wksSheet As Excel.Worksheet
Dim wkbBook As Excel.Workbook
Dim szFileName As String

Set wkbBook = Application.ActiveWorkbook

For Each wksSheet In wkbBook.Worksheets
' Change this to your own path and filename.
szFileName = "E:\Temp\" & wksSheet.Name & ".htm"
wkbBook.PublishObjects.Add(xlSourceSheet, _
szFileName, wksSheet.Name, "", _
xlHtmlStatic, wksSheet.Name, "").Publish True
Next wksSheet

End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
R

Rob Bovey

Martyn said:
Rob, I'm using XL2K on XP and it fails with the Publish Method.
Click to expand...

Hi Martyn,

That's the same configuration I wrote it on and it works OK here. Did
you change the path in the szFileName variable to a valid path on your
system?

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
M

Martyn

Hi Rob,
Yes, I did change the path to the most acceptable "C:\"
No...same result. The compiler says:

Method 'Publish' of object 'Publish Object' failed

highlighted text is the

wkbBook.PublishObjects.Add(xlSourceSheet, _
szFileName, wksSheet.Name, "", _
xlHtmlStatic, wksSheet.Name, "").Publish True

part. Somehow XL is not happy about it.
p.s.: (-the above statement is written as a single complete line too-)

Hope we/someone can work it out...:)
Sincerely
Martyn
 
L

L Norton

Thanks! That worked perfectly.

LN


Rob Bovey said:
Here's a VBA example that exports each worksheet as an individual file
with the same name as the worksheet. You shouldn't get any sheet tabs or
extra folders using this method:

Sub SaveSheetsAsHTML()

Dim wksSheet As Excel.Worksheet
Dim wkbBook As Excel.Workbook
Dim szFileName As String

Set wkbBook = Application.ActiveWorkbook

For Each wksSheet In wkbBook.Worksheets
' Change this to your own path and filename.
szFileName = "E:\Temp\" & wksSheet.Name & ".htm"
wkbBook.PublishObjects.Add(xlSourceSheet, _
szFileName, wksSheet.Name, "", _
xlHtmlStatic, wksSheet.Name, "").Publish True
Next wksSheet

End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
Click to expand...
 
M

Martyn

Hi Rob,
I think I got it at solved at the end with your tremendous help. The problem
is/was that, I assigned the execution of the macro to a command button in my
file. When I run the macro via VBE it worked all right. But when I try to
use the button instead to call the macro, the error occured.
The only trouble lies ahead is the "slow opening" of the *.htm files
created. Maybe it is because of the large area beeing used (65*** lines x
256 columns) to copy into an html file. Any comments on that will be
wellcomed.
Thanks a million Rob
Martyn
 
Top