Exporting to Excel is VERY slow

[matey56]

Hi all,
I have a report with just a few hundred records. I have an Excel button with a ExportWithFormatting macro. It takes about 10 mins to generate the Excel file and save it to the desktop. What am I doing wrong? How can I speed this up? Any ideas?

[CJ_London]

No idea what you are doing wrong until we know what you are actually doing. Show all the code and sql you are using plus what you are exporting - a screenshot of the report.

as a means of eliminating the formatting as a reason - how long to export without formatting?

[Minty]

The code must be doing something very odd, I can gather 80000 records from a cloud based Azure SQL Server and apply individual formatting to 70+ columns in about a minute and a half.
As CJ said show us your code.

[matey56]

What code are you referring to? It's a macro (see below). Should I code it instead? The backend is a cloud based Azure SQL Server connected via linking. I would screenshot the report, but it has people's names and other business proprietary stuff. It's just a pretty simple report with about 20 columns and 300+ rows of text, dates and numbers.

[CJ_London]

no attachment visible. If this is a picture, click on the 'insert image' button and insert it that way

[Minty]

Is the report based on a query? If you open that on it's own how long does it take to load the entire recordset?
If that takes ages to run then post up the SQL of the Query. Along with the picture of the Macro. VBA would be better in the long term as it's more flexible.

[matey56]

The report is based on a SELECT query. I'm just realizing now that there are 4 calculated fields in it. However, when I run the query the dataset loads instantly.

Here is the SQL for the query

SELECT dbo_72_tbl_Label_Amendment_Schedule.*, IIf(IsDate([Label_Valid_To]),DateAdd("m",-4,[Label_Valid_To]),"") AS Final_Dec_Date_New_or_Reprint, IIf(IsDate([Target_Production_and_Sales]) And IsNumeric([Months_Out]),DateAdd("m",-2,[Market_Label_Due_to_Mfg_Labeling]),"") AS Market_Label_Printable_Due, IIf(IsDate([Target_Production_and_Sales]) And IsNumeric([Months_Out]),DateAdd("m",-[Months_Out],[Target_Production_and_Sales]),"") AS Market_Label_Due_to_Mfg_Labeling, IIf(IsNull([EPA_Expiration_Timing]),"",IIf(IsDate([EPA_Stamp_Date_Driving_18_Month_Expiration]),DateAdd("m",[EPA_Expiration_Timing],[EPA_Stamp_Date_Driving_18_Month_Expiration]),"")) AS 18_Month_Expiration
FROM dbo_72_tbl_Label_Amendment_Schedule
ORDER BY dbo_72_tbl_Label_Amendment_Schedule.[Product Name];

Here's the macro to export the report to Excel:

[Minty]

So it's a single table and some calculated fields.
I would remove the order by clause as it's pointless as that is set by the reports own sorting and grouping.

I have just realised that you are exporting a report not a query. I bet it's the time it takes to format however many pages of printing that would be.
The report will take the data add the sorting and grouping then create the pages, then finally export it.

Why not just export the data directly to excel, I've never seen an export of a report look even vaguely useful?

What's special about the report formatting that you can't do it directly?

[matey56]

I can export the data directly to Excel. How do I do that?

[Minty]

In VBA it couldn't be simpler

Dim qryName as String
Dim strSave File as String
Dim xlApp            As Object        'Excel.Application
Dim xlWb             As Object        'Workbook
Dim xlWS             As Object        'Worksheet

 qyrName = "YourQueryName"
 strsaveFile = CurrentProject.Path & "\SavedExports\Myfilename.xlsx"
 DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile , True

 Set xlApp = CreateObject("Excel.Application")
 Set xlWb = xlApp.Workbooks.Open(strsaveFile )
 xlApp.Visible = True

This will export your query directly to a spreadsheet then open it.

[matey56]

In VBA it couldn't be simpler

Dim qryName as String
Dim strSave File as String
Dim xlApp            As Object        'Excel.Application
Dim xlWb             As Object        'Workbook
Dim xlWS             As Object        'Worksheet

 qyrName = "YourQueryName"
 strsaveFile = CurrentProject.Path & "\SavedExports\Myfilename.xlsx"
 DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile , True

 Set xlApp = CreateObject("Excel.Application")
 Set xlWb = xlApp.Workbooks.Open(strsaveFile )
 xlApp.Visible = True

This will export your query directly to a spreadsheet then open it.

I'm getting a Run-time error '2495': The action or method requires a Table Name argument.

The debugger takes me here. After entering the query name what am I missing? Am I adding the table name even though I'm exporting the query?

DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True

[Welshgasman]

Qyrname and qyrname?

You should have option explicit in every module?

[matey56]

Qyrname and qyrname?

You should have option explicit in every module?

I don't know what this means.

[Minty]

It's a typo in my code.

Dim qryName as String
Dim strSave File as String
Dim xlApp            As Object        'Excel.Application
Dim xlWb             As Object        'Workbook
Dim xlWS             As Object        'Worksheet

 qryName = "YourQueryName"
 strsaveFile = CurrentProject.Path & "\SavedExports\Myfilename.xlsx"
 DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile , True

 Set xlApp = CreateObject("Excel.Application")
 Set xlWb = xlApp.Workbooks.Open(strsaveFile )
 xlApp.Visible = True

[Welshgasman]

Sorry, I compounded it with my own typo.
One of them is qryName.