TransferSpreadsheet Problem

  • Thread starter Thread starter MARK
  • Start date Start date
M

MARK

I may have asked this question to the wrong group and if so, please
direct me to the correct place.

I have a query named "sumquery" on a single table named
FDNS_Production. The query looks for date parameters using the Between
function. I want to use the macro below to offer push button access to
the query and have the results automatically open in Excel. I
continuously get an error on the TableName part of the DoCmd below.
I'm not a VBA expert by any stretch. I have only the basics and enough
to get myself in trouble. Can someone help me understand why I keep
getting hung up on this. Is there a better way to do what I'm trying?

All help is appreciated as I learn.


Private Sub MonthlyReport_Click()
On Error GoTo Err_MonthlyReport_Click

Dim stDocName As String

stDocName = "sumquery"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
FDNS_PRODUCTION, MonthlyReport, True, "A1:AN12"

Exit_MonthlyReport_Click:
Exit Sub

Err_MonthlyReport_Click:
MsgBox Err.Description
Resume Exit_MonthlyReport_Click

End Sub
 
I may have asked this question to the wrong group and if so, please
direct me to the correct place.

I have a query named "sumquery" on a single table named
FDNS_Production. The query looks for date parameters using the Between
function. I want to use the macro below to offer push button access to
the query and have the results automatically open in Excel. I
continuously get an error on the TableName part of the DoCmd below.
I'm not a VBA expert by any stretch. I have only the basics and enough
to get myself in trouble. Can someone help me understand why I keep
getting hung up on this. Is there a better way to do what I'm trying?

All help is appreciated as I learn.

Private Sub MonthlyReport_Click()
On Error GoTo Err_MonthlyReport_Click

Dim stDocName As String

stDocName = "sumquery"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
FDNS_PRODUCTION, MonthlyReport, True, "A1:AN12"

Exit_MonthlyReport_Click:
Exit Sub

Err_MonthlyReport_Click:
MsgBox Err.Description
Resume Exit_MonthlyReport_Click

End Sub
Click to expand...

You have
Dim stDocName As String
stDocName = "sumquery"

but nowhere in your code do you use stDocName or "sumquery".
Note: You don't really need stDocname here at all.

In any event, the table name and spreadsheet names must be strings,
and it must include, as a string, the path to the spreadsheet.
Strings are surrounded by quotes.

Try it this way:

Note: There is no need, in this instance, to Dim stDocName. Just use
"sumaquery" directly in the code.

Private Sub MonthlyReport_Click()
On Error GoTo Err_MonthlyReport_Click

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"sumquery", "C:\SomeFolder\MonthlyReport.xls", True

Exit_MonthlyReport_Click:
Exit Sub

Err_MonthlyReport_Click:
MsgBox Err.Description
Resume Exit_MonthlyReport_Click

End Sub
===========
The above assumes the name of the spreadsheet to export to is
"MonthlyReport.xls", and it is located in "SomeFolder" on your "C"
drive. Change the code to your exact folder and location.

The data will be placed in a new or existing worksheet named
"subquery" in the spreadsheet.

NOTE: Read VBA help on the TransferSpreadsheet method.
You CANNOT use the Range argument when exporting data.
The export will fail!
If you wish to place the exported data in a particular Range in the
spreadsheet, do it after you open the spreadsheet. You can either Cut
and Paste manually or use code within the spreadsheet to do so.

I hope this has helped.
 
Fred, this worked GREAT!!!

My only question now is how do I make it so that the sheet is at the
font instead of at the end. When I opened Excel, there were 3 blank
default sheets and then the new sheet.

Also, is it hard to make it so that Excel automatically opens so that
the user doesn't have to try to find the sheet? If it is difficult,
I'll set up a shortcut on their Quick Launch bar.

Thank you for all your help.
 
Fred, this worked GREAT!!!

My only question now is how do I make it so that the sheet is at the
font instead of at the end. When I opened Excel, there were 3 blank
default sheets and then the new sheet.

Also, is it hard to make it so that Excel automatically opens so that
the user doesn't have to try to find the sheet? If it is difficult,
I'll set up a shortcut on their Quick Launch bar.

Thank you for all your help.
Click to expand...

After exporting the data to Excel the first time, move the new
worksheet to the first sheet manually.

Then just delete the data, (if you no longer need it) leaving that
worksheet where it is as the first sheet.
The next time you export to Excel, it will go to the same sheet which
will remain at the beginning of the workbook.

If you wish Excel to open after you transfer the data (each time), add
the following code after the TransferSpreadsheet.

Application.FollowHyperlink "C:\SomeFolder\MonthlyReport.xls"
 
Back
Top