How to store Cell format?

[Bullfrog]

Is there a way to store formating information of a cell/range along
with the value of it inside an object variable or similar structure?
Here code in question.

Private Sub GetRanges_Click()

Dim xlwb As Excel.Workbook
Dim xlws As Excel.Worksheet
Dim fullpath As String
Dim temp As Variant ' should this be a range object?

fullpath = "C:\Documents and Settings\smg706\Desktop\Traffic Desktop\"

Set xlwb = Workbooks.Open(fullpath & "ge_matrix.xls")
Excel.Application.Visible = True

Set xlws = xlwb.Sheets(1)

xlws.Activate

temp = Range("B9:AM9") ' range value or object?

Set xlws = xlwb.Sheets(2)
xlws.Activate

Range("b9:am9") = temp

' The above line copies all cell values to the new sheet, but does not
' preserve the cell formatting.
End Sub


LIke the comment says
' The above line copies all cell values to the new sheet, but does not
' preserve the cell formatting.

This code is running inside a MS access form, and accessing Excel.
If there is no automated way, I will just have to loop for each cell in
the range, and apply formating according to cell value after the
transfer of data.


Any help will be appreciated.

Thanks,

Sylvain

 

[Guest]

You can give this a try...

Private Sub GetRanges_Click()

Dim xlwb As Excel.Workbook
Dim fullpath As String

fullpath = "C:\Documents and Settings\smg706\Desktop\Traffic Desktop\"

Set xlwb = Workbooks.Open(fullpath & "ge_matrix.xls")
Excel.Application.Visible = True

with xlwb
.Sheets(1).Range("B9:AM9").copy .Sheets(2).Range("B9:AM9")
end with

 

[Bullfrog]

You can give this a try...

Private Sub GetRanges_Click()

Dim xlwb As Excel.Workbook
Dim fullpath As String

fullpath = "C:\Documents and Settings\smg706\Desktop\Traffic Desktop\"

Set xlwb = Workbooks.Open(fullpath & "ge_matrix.xls")
Excel.Application.Visible = True

with xlwb
.Sheets(1).Range("B9:AM9").copy .Sheets(2).Range("B9:AM9")
end with

I had seen that before, but I was planning on saving this variable in a
array for processing. Maybe even save it in access. Access OLE can
only save Workbook and worksheet objects.
Thanks anyways.

 

[Guest]

In your code Temp is a variant which ultimately is a 2D array of values. You
can make it into a 1D array something like this...

temp = Application.Transpose(Range("B9:AM9"))

 

[NickHK]

You will need to develop a "serialize" method, whereby you create some
(string) format that preserves the cell information.

Private Function Serialize(argRange As Range) As String
Dim TempStr As String

With argRange
TempStr = "Value:" & .Value
TempStr = TempStr & ",NumberFormat:" & .NumberFormat
TempStr = TempStr & ",Address:" & .Address
'etc...
End With
Serialize = TempStr
End Function

However, I feel it would be easier to let Excel handle this, as that's its
design.
You could create a WB for each entry, which is only ~15K and save yourself a
lot of trouble.

Another alternative is to study Excel's BIFF format and use that.
http://www.wotsit.org/search.asp?s=BIFF
http://chicago.sourceforge.net/devel/docs/excel/

NickHK