accessing Excel sheet properties

[jluckhoff]

Hi,

Im trying to devise a way to take a range and pull out each cell i
there's value, formula, font etc etc.

I have managed to do this so far in a very clumsy way by loopin
through the range as in the code below but this is proving very slow.

The idea is to serialise the sheet to xml so that I can transfer it t
a remote client and deserialise it there back into xl.

If anyone has dealt with the kind of problem I would really appreciat
any comments you might have to make it go faster.

here is the code:



Dim Excel As New Excel.Application
Dim rng As Excel.Range
Dim int As Integer
Dim address As String
Dim Cellcount As Integer


int = 0
'open the existing workbook
Excel.Workbooks.Open("c:\test.xls")

'MsgBox(rng.Value)

'Msdn Ref for code below

'ms-help://MS.MSDNQTR.2003JUL.1033/vbaxl10/html/xlhowhowtoloopthrougharangeofcells.htm



Cellcount = Excel.Worksheets("Sheet2").usedrange.Cells.count
1
'MsgBox(Cellcount)



Dim DataArr(Cellcount, 5)
For Each rng In Excel.Worksheets("Sheet2").UsedRange.cells
'Here we add the cell ref, value, font and formula to the array
DataArr(int, 0) = rng.Cells.Address
DataArr(int, 1) = rng.Value
DataArr(int, 2) = rng.Font.Name
DataArr(int, 3) = rng.Formula
If rng.Font.Bold = True Then
DataArr(int, 4) = "True"
End If
DataArr(int, 5) = rng.Font.Color
int = int + 1

Next

' here is the code that builds the array back into the ne
worksheet
'create new worksheet
Dim WSheet As New Excel.Worksheet
'Adding new worksheet to excel workbooks
WSheet = Excel.Workbooks.Add.Worksheets.Add
WSheet.Name = "CC1Duplicate"

'Now write the values, formulas and format to the cell in th
new sheet
int = 0
'the arraylen is still hardcoded and should be replaced b
Ubound
While int <= Cellcount
address = DataArr(int, 0)
Excel.Range(address).Value = DataArr(int, 1)
Excel.Range(address).Font.Name = DataArr(int, 2)
Excel.Range(address).Formula = DataArr(int, 3)
Excel.Range(address).Font.Bold = DataArr(int, 4)
Excel.Range(address).Font.Color = DataArr(int, 5)
int = int + 1
End While
'we want to see what is happening
Excel.Visible = True

'********************************************

 

[Robin Hammond]

The only thing I can see in your code that would speed it up would be not to
add information for empty cells.

at the start of the loop you could add

if isempty(rng.value) then goto Continue
The Continue label comes before your next statement.

However, I looked very hard at replicating the SaveAsXML functionality of XP
in 2000 over a year ago, wrote about 80% of the functionality, then gave up
in disgust due to number 3 below, and you have a lot more to deal with than
just the fonts and values. If you are truly trying to recreate the original
at the far end you need to deal with

1. named ranges
2. formulas that refer to named ranges
3. styles (a nightmare in the xp xml spec since all styles are enumerated at
the start of the spec, meaning you need to check every single cell in the
sheet to work out all the possible formats that exist in the original file.
This is ineviatably very slow for a big workbook).
4. hidden ranges
5. protection of sheets and ranges
6. forget charts, they are not even in the xp spec.
7. what if the sheet refers to a cell outside the sheet. what about remote
links.

A better question might be, how are you doing the data transfer to the
remote client? Surely you would be better off sending the workbook, or more
likely pulling the workbook from the other end. If you have IIS running on
the source machine, there a very handy api call URLDownloadToFile that can
pull it off your server from the client end.

Robin Hammond
www.enhanceddatasystems.com