Numberformat: automation from MS Access
 

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

Numberformat: automation from MS Access
 

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





Thanks in advance