Export to Excel Macro

[NISMOJim]

Hello,
I have a form with a date range parameter for the user to select a start & end date, which is the condition for a query. The "On Click" property on the form's button is to run the following Macro...

Transfer Spreadsheet
Transfer Type: Export
Spreadsheet Type: Excel Workbook
Table Name: qryMonthlyChartUnion
File Name: T:\Reporting\Charts\Monthly.xlsx
Has Field Names: No
Range: (blank)
OpenReport
Report Name: rptMonthlyTracking
View: Print Preview
Filter Name: (blank)
Where Condition: (blank)
Window Mode: Normal

The idea is to send the information from the query to an existing Excel sheet that has the same name as the query the data is coming from. A different sheet arranges the data, and a third sheet in the same workbook has a chart built on the data. The report has an unbound OLE object linked to the chart to show up at the top of the report, and also has a detailed breakdown of the data directly from the query below it.

When I open the database, select the date range & click the "OK" button, everything works perfectly. The problem comes when I change the date range & run the report again. The detailed information is correct, but the chart is still the same as the last time the report was run. The query works fine, but for some reason it won't re-export the query to the spreadsheet each time the macro is run. How can this be fixed?

[ItsMe]

I am not an expert in Excel spreadsheets VBA and such but it sounds like your Workbook is the part that is not updating the chart. You say the chart is in the workbook but what gets updated by Access is in separate worksheet? The chart is based on the data in another worksheet?

[NISMOJim]

No, all 3 sheets are in the same workbook, Monthly.xlsx. Like when you open a new Excel workbook, there are 3 sheets in the workbook. I think I messed up my terminology before, sorry. When I keep playing with it, it does export and show the correct chart every once in a while, but there is no consistency with the chart data being updated, only the detailed data that comes right from the query is correct each time.

[ItsMe]

Right, so data gets exported to one worksheet and a chart in another worksheet (within the same workbook) displays stuff that is relevent to the data in the other worksheet.

If your chart thing is not reflecting hte changes in the other worksheet then I suspect there is an issue with your "Workbook".

[NISMOJim]

The thing is, when I look at the datasheet that should have the new data on it, it is still the data from the previous time the report was run. For some reason, the macro isn't exporting the data, but it is opening the report. No error messages ever pop up, it just opens with the old data, which displays the old chart. Then I can run it again, and sometimes the data is exported, and the chart shows up correctly. Any idea why the Transfer Spreadsheet command doesn't work every time?

[ItsMe]

All I can think is the date parameters are not getting added to the Where criteria. I would spend some time looking at qryMonthlyChartUnion, and compare what is inside to the dates on the form, with the dates on the report, and the dates in the spreadsheet. Determine if there are discrepancies and or a pattern.

My experience with transferspreadsheet is limited to VBA and creating brand new spreadsheets, not exporting into ranges or named cell ranges, etc. I am not sure how Access handles existing data in cells it is supposed to transfer data into.

[NISMOJim]

The data in the query based on the date parameters shows up right every time. This is what is displayed on the report below the chart. No problem at all with this data. The only problem is actually transferring the data from the query to Excel doesn't work each time. If the data does transfer over, then the chart shows up with the right data too. So there is no problem with the query, the OLE object on the form, or the charting part of the spreadsheet. Just the transfer part of the macro, which I have also rewritten a few times, with the same results.

[ItsMe]

Without it being in VBA I do not know where to begin to start trouble shooting it, other than to consider the date parameters or the fact that you are writing to cells that already have data within them. The fact that you state it works sometimes and not others would indicate there is a discrepency somewhere.

[NISMOJim]

I'm finally getting back to this old problem. Does anyone else have any idea why the query won't export? I'm now running Windows 7 with Access 2010, but the problem remains the same. Next to the ImportExportSpreadsheet is a yellow triangle that says "Unsafe Action", even though the spreadsheet is in the same trusted location as the database. I also tried ExportWithFormatting with the same results.