Numberformat: automation from MS Access

[faberk]

From access i am creating excel workbooks and worksheets within each
workbook. I am using a template, which opens with one worksheet. I add the
data I need to this sheet, copy the sheet forward and loop through my
recordset until recordset = EOF.

My problem is that the number formatting does not copy forward from the
original sheet. The original sheet remains the same, but each sheet my
routine copies, contains incorrect formatting. I even attempted to format
the cells in the code, but it will not "take". See the section of my code
below that attempts to do this.

xlApp.Range("E15").Value = "'" & strPer1 'Period 1 label
xlApp.Range("E16").Value = rsData.Fields(3).Value 'Period 1 value+
xlApp.Range("E16").NumberFormat = "#,##0_);[Red](#,##0_)"

What am i doing wrong? Is it an excel default issue? template?

Thanks in advance

 

[arunkhemlai]

My problem is that the number formatting does not copy forward from the

original sheet. The original sheet remains the same, but each sheet my
routine copies, contains incorrect formatting. I even attempted to format
the cells in the code, but it will not "take". See the section of my code
below that attempts to do this.

xlApp.Range("E15").Value = "'" & strPer1 'Period 1 label
xlApp.Range("E16").Value = rsData.Fields(3).Value 'Period 1 value+
xlApp.Range("E16").NumberFormat = "#,##0_);[Red](#,##0_)"

What am i doing wrong? Is it an excel default issue? template?

Sorry not to answer your actual question, but the Copy() method might help:

'---------------------------------

' The two sheets can come from different workbooks.
Dim srcWSheet As Worksheet
Dim dstWSheet As Worksheet

Set srcWSheet = ThisWorkbook.Worksheets(1)
Set dstWSheet = ThisWorkbook.Worksheets(2)

' Copy one cell from src -> dst
srcWSheet.Range("E16").Copy dstWSheet.Range("E16")

' Copy a range from scr -> dst
srcWSheet.Range("A1:B5").Copy dstWSheet.Range("E3:F7")

' Copy the whole sheet
srcWSheet.Rows.EntireColumn.Copy dstWSheet.Range("A1")

'---------------------------------

arunkhemlai