Hello,
I got a worksheet with about 280 sheets
Each sheet that have a certain name needs to be converted and outputed as .html
To convert a single sheet goings well, but putting this on a loop seems like not so good, but i need to do it somehow. Im using Excel 2003.
So.. It works, but very slow. Any solutions to speed up this? SOmetimes when Excel is happy I can see some htm output but usally not at all!
I know the system of sheets for each item is very bad idea. But as it is not the sultion has to work.
I have runned the subrutine without the WITH statement, and all is fine. it hangs / not doing anything when its about to do the statement. Seems like the PublishObjects is a really resource hog.
Is there any way to "Reset" Publishobjects since it has a default index builtin e.g. Publishobjects(1), next is publishobject(2) as default as i think. But i need a Clear of the publishobjects after each iteration perhaps? Maybe then the memory wont eat up so much, or what do you think? if so, how to I reset the object?
I have never seen publishobjects used in a loop before since i came across this nasty solution.
Best regards
Mike
I got a worksheet with about 280 sheets
Each sheet that have a certain name needs to be converted and outputed as .html
To convert a single sheet goings well, but putting this on a loop seems like not so good, but i need to do it somehow. Im using Excel 2003.
Code:
Sub ExportToHTML ()
Dim Tempsheet As Worksheet
Dim thisName As string
Dim myRange As range
Dim outputName As String
Dim dataWs As Worksheet
Dim i As Integer
set dataWs = worksheets("nameIdentifer")
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False
For i 2 To 100 Step 1
thisName = dataWs.Cells(i, 1)
Set thisSheet = Worksheets(thisName)
Set myRange = Intersect(thisSheet.UsedRange, thisSheet.range("A1:C10"))
outputName = i & ".htm" '1.htm, 2.htm etc
With ThisWorkbook.PublishObjects.Add(_
SourceType:=xlSourceRange, _
Filename:=outputName, _
Sheet:=myRange.Parent.Name, _
Source:=myRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
.AutoRepublish = False
End With
Next i
End Sub
So.. It works, but very slow. Any solutions to speed up this? SOmetimes when Excel is happy I can see some htm output but usally not at all!
I know the system of sheets for each item is very bad idea. But as it is not the sultion has to work.
I have runned the subrutine without the WITH statement, and all is fine. it hangs / not doing anything when its about to do the statement. Seems like the PublishObjects is a really resource hog.
Is there any way to "Reset" Publishobjects since it has a default index builtin e.g. Publishobjects(1), next is publishobject(2) as default as i think. But i need a Clear of the publishobjects after each iteration perhaps? Maybe then the memory wont eat up so much, or what do you think? if so, how to I reset the object?
I have never seen publishobjects used in a loop before since i came across this nasty solution.
Best regards
Mike