First thing that could help is to turn off updating here are a couple of
routines to do that: Call Updates_Off at the top of your routine then call
Updates_On at the end

Public Sub Updates_Off()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
End Sub
Public Sub Updates_On()
With Application
.StatusBar = False
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.EnableCancelKey = xlInterrupt
.Cursor = xlDefault
.Calculation = xlCalculationAutomatic
End With
End Sub

I know that formatting takes a very long time to apply cell by cell if you
do not turn off screen updating.

Another way you can speed things up (if you are interrogating/populating
your sheet's cells row by row, col by col I.E. using .cells or .offset to
reference each cell)... Use Variant arrays to load your sheets cells then
process the array, changing the values you need to change then load that
array back to the sheet.

Dim vaData as Variant
Dim lRow as long, lCol as long
Dim addr As string

' Load your worksheets data
vaData = Range(ActiveSheet.UsedRange.Address).Value
addr = ActiveSheet.UsedRange.Address

' Process the data 1 row at a time, 1 col at a time
For lRow = 1 to UBound(vaData, 1)
For lCol = 1 to UBound(vaData, 2)
if vaData(lRow, lCol) = "X" then
vaData(lRow, lcol) = ""
EndIf
'*** any other value changes to each row, col by col till
'*** the end of your data (you cant apply formatting here
'*** since your not directly referencing the cell)
Next lCol
Next lRow

' Put the array back to the same cells in the worksheet
ActiveWorksheet.Range(addr).value = vaData

Note that Variant data arrays must always be 2 dimensional for this to work.
The array defined is 1..X for each dimension based on the size of the block
of cells.

You can build a 2D array in code while parsing your XML file. then with one
statement load all that data to the block of cells. Just make sure it is
1..Rows and 1..Cols

hope that helps
--
Regards,
John


"KevinGPO" wrote:

I have programmed my own set of modules for Excel to read, parse a xml file
then scan the Excel worksheets for "import/bookmark" tags and do a replace,
pasting in the corresponding xml data. At the same time it does
formatting/style/colors, etc. which is taken from the xml attributes &
nodes.

I found a 759kb xml file takes ages to parse. The excel template file is
376kb. Importing usually takes 9-10 minutes long. Is there any way I can
optimise and speed things up (besides buying a faster PC - I have a P4
3.0GHz 512MB).


Is there anyway to create/generate a valid excel file without having to load
up Excel? What is this Office SDK and what can it do?