Beacuse the export to Excel overwrites an existing file I have Access writng
'data dumps to Excel, then retrieve the data from the dump file to my report
template. This works for Daily, Weekly, Monthly reports and runs pretty
quick.

the excerpts are from access then the Excel report template, which then goes
on to manipulate the data. create various lines of business reports, and
distributes all the reports.

DumpFile = "\\reportDirectory\My Template TempDataDump.xls"
TemplateFile = "\\reportDirectory\" & RptPeriod & " MyReport Template.xls"


''' #6) Dump query results into XlS File
DoCmd.OutputTo Objecttype:=acQuery, ObjectName:="qry_Export_Excel",
outputformat:="MicrosoftExcel(*.xls)", _
outputFile:=DumpFile _
, AutoStart:=False

'''Run Templates
Set xlsApp = CreateObject("Excel.Application")
With xlsApp.Application
.Workbooks.Open TemplateFile
.Visible = True
.Run ("Code.TransferData")
.DisplayAlerts = False
.Workbooks.Close
End With
xlsApp.Quit

***************
Sub TransferData()
'This code is used in conjunction with Access
Dim ActivePath, SourceFile, This_File As String
Dim iLastCol, iLastRow As Integer

ActivePath = ActiveWorkbook.Path
This_File = ActiveWorkbook.Name
SourceFile = "My Template TempDataDump.xls"
Sheets("Data").Select
tRow = 'use whatever method you want to find the top (title row) of your
report table'
'This code preserves comments in title row

Range(Cells(tRow + 1, 1), Cells(tRow, 1).End(xlDown)).EntireRow.Delete
Cells(tRow, 1).EntireRow.ClearContents

Workbooks.Open Filename:=ActivePath & "\" & SourceFile
iLastCol = Cells(1, 1).End(xlToRight).Column
iLastRow = Cells(65535, 1).End(xlUp).Row
Range("A1").CurrentRegion.Copy
Windows(This_File).Activate

Cells(tRow, 1).Select
ActiveSheet.Paste
Columns("C:C").NumberFormat = "m/d/yy"
Rows(tRow).WrapText = True
Rows(tRow).Font.Bold = True
Selection.Interior.ColorIndex = xlNone
Range("a1").Select
Workbooks(SourceFile).Close Savechanges:=False
....other stuff

More than one way to skin a cat. ..

"Michel S." wrote:

Hi !

I'm working with Office XP, SP3.

I have a simple Access VBA module which creates an Excel Workbook, and
fills the first Worksheet with data coming from a query.

In the main loop, I only perform the following :

objExcel.ScreenUpdating = False
With xlsSheet.Cells(intRow, strColumn)
.Value = (recordset coresponding field value)
.HorizontalAlignment = (preset alignment)
If strNumberFormat < vbNullString Then
.NumberFormat = strNumberFormat
End If
.Interior.Color = (Color based on cell value)
.Font.Color = (Color based on cell value)
End With
objExcel.ScreenUpdating = True

The right side of the assignment values (recordset field name,
alignment, number format, etc.. ) are stored in an Array of User
Defined Type (one line per field) initialized once at the beginning of
the function.

There is a total of 29 fields only (columns A to AC), and for each
cell, only these 5 properties are affected.

Despite this, it takes a little more than 1 second to fill each row,
which appears very slow to me.

Since there are more than 1200 rows, it nearly takes 20 minutes to
perform the transfer.

I have cheked the functions returning the FG/BG colors based on the
field value and they take less than a second to execute in a 10000
iterations loop. I do not suspect they are related to the slow
performance.

What are other's experiences with the performance of this kind of data
transfer ?

Any suggestions to "optimize"/make it faster ?

BTW, because I have to format some columns and also colour many cells
based on their value (10 possible values), I'm afraid I can't use the
"global transfer" options availiable in Access.. Unless somebody has
an option unknown to me.


Thanks in advance !

FU2: microsoft.public.access