Transferring data from Access to Excel in VBA very slow

[Michel S.]

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

 

[Dave Patrick]

I think I'd be inclined to use the TransferSpreadsheet method then later
open the XLS using the excel model and do something like;

With Columns("B:B")
.FormatConditions.Delete
.FormatConditions.Add 1, 5, "100"
.FormatConditions(1).Interior.ColorIndex = 3
End With


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| 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
|
|

 

[goss9394]

Michel -
Use ActiveX Data Objects (ADO)
It will perform much faster

See xldennis' site for complete tutorials
www.excelkb.com

HTH
-goss

 

[Michel S.]

Thanks for your suggestions, Dave and Goss9394.

I used a mix of your suggestions along with other I found while
searching on the net.

I finally choose to put the values in a variant array (couldn't use
TransferSpreadSheet method because of the requied columns order) and
then assing the array to a range with a statement like :

xlsSheet.Range(strStart, strEnd) = varData

The total time requied to fill the array and assign it to the range is
now... less than 1 second.


Remains now to format the columns.. Unfortunately, the
FormatConditions collection is limited to 3 items.. I need options
than that.

Since I don't need FormatConditions flexibility (the resulting sheet
will be read only and the values won't change), maybe I can resort to
another method like conditional rage assignment (matrix operations ?).

I guess I'll have to search a little to find the most effective way to
perform this..

If you ever have a hint, let me know ! ;o)

Thanks again.




Dave Patrick a formulé la demande :

I think I'd be inclined to use the TransferSpreadsheet method then later
open the XLS using the excel model and do something like;

With Columns("B:B")
.FormatConditions.Delete
.FormatConditions.Add 1, 5, "100"
.FormatConditions(1).Interior.ColorIndex = 3
End With

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

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

 

[Michel S.]

Thanks goss,

Please see my reply to Dave.

PS: thanks for the site reference - it's now in my favorites.. ;-)

(e-mail address removed) a couché sur son écran :

 

[Dave Patrick]

You can use a query to rearrange the columns in any order.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
<snip>
(couldn't use
| TransferSpreadSheet method because of the requied columns order)
<snip>

 

[Michel S.]

Well, I know, but I prefer to limit the number of queries for future
maintenance purposes.

The query I'm using is also used to "feed" a combo box, and I don't
want to change the columns order of the combo.

But given the performance boost the array gives, I'm willing to build a
For.. Next loop to put the fields in the required sequence.. ;-)


By the way, I don't know what happened with the Worksheet.Cells(x, y)
internal processing, but it is a lot slower than with Excel 97.

Only to put the columns headers, it originally took 3 seconds to
initialize with the "For Each Cell" form.. (for 28 cells !). With a
variant array, it takes now .11 seconds . lol

And I'm using a P4-2.4Ghz machine, with 2 Gb RAM..

Go figure !

Thanks again !!!



Dave Patrick a utilisé son clavier pour écrire :

 

[Candyman]

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. ..