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

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 *


"L Norton" wrote in message
om...
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

Rob, I'm using XL2K on XP and it fails with the Publish Method.

"Rob Bovey" wrote in message
...

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 *


"L Norton" wrote in message
om...
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

"Martyn" wrote in message
...
Rob, I'm using XL2K on XP and it fails with the Publish Method.

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 *


"Rob Bovey" wrote in message
...

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 *


"L Norton" wrote in message
om...
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

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


"Rob Bovey" wrote in message
...
"Martyn" wrote in message
...
Rob, I'm using XL2K on XP and it fails with the Publish Method.

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 *


"Rob Bovey" wrote in message
...

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 *


"L Norton" wrote in message
om...
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

Thanks! That worked perfectly.

LN


"Rob Bovey" wrote in message ...
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 *


"L Norton" wrote in message
om...
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