TransferSpreadsheet Command error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new to VBA programming. I think I am doing this right but keep getting
the below error when typing in my TransferSpreadsheet Command. I am trying to
write code so my query after being opened and run via form exports the
results into Excel. My queries name is Subcycle Data and I want it to have a
field heading row. Can anyone see where I am going wrong?

Compile Error- Expected: Expression

DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"Subcycle
Data",,True,,,
 
You're not supplying a file name to which to export. You also don't need the
commas at the end.
 
I am new to VBA programming. I think I am doing this right but keep getting
the below error when typing in my TransferSpreadsheet Command. I am trying to
write code so my query after being opened and run via form exports the
results into Excel. My queries name is Subcycle Data and I want it to have a
field heading row. Can anyone see where I am going wrong?

Compile Error- Expected: Expression

DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"Subcycle
Data",,True,,,
Click to expand...


1) You haven't told Access into what spreadsheet to export the data.

2) If you have no arguments to use after your last argument (i.e.
True,,,), you do not need to add commas.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Subcycle
Data", "c:\MyFolderName\SpreadsheetName.xls", True

The above will transfer your query data into a worksheet named
"Subcycle Data" in a spreadsheet named "SpreadsheetName.xls" in the
folder "MyFolderName".
 
It says that is optional, but I can insert something in there. What if I
just want it to create a new excel file? Should I put something in like
C:\Documents and Settings\What if I want a generic user name?\My
Documents\book1 ?
 
How do I get it to open up and show me the excel file when it is finished and
not show me the results in Access? Thanks for everyones help. This is the
finishing touches on a long project and I am no good at this VBA stuff.
 
Remember that you need to be able to find the workbook again, so it's rather
important that you know where it is!

I suppose you could use "C:\Documents and Settings\All Users\My
Documents\book1.xls", or you can grab the code from
http://www.mvps.org/access/api/api0008.htm at "The Access Web" and use
"C:\Documents and Settings\" & fOSUserName & "\My Documents\book1.xls" or,
even better, grab the code from http://www.mvps.org/access/api/api0054.htm
and use fGetSpecialFolderLocation(CSIDL_PERSONAL) & "\book1.xls"

If you need to know whether or not the file already exists, you can use the
Dir function:

If Len(Dir(strFileLocation)) > 0 Then
' The file exists. Prompt the user to delete.
If MsgBox(strFileLocation & " already exists." & _
"Do you want to delete it?", vbYesNo + vbQuestion) = vbYes Then
Kill strFileLocation
End If
End If
 
How do I get it to open up and show me the excel file when it is finished and
not show me the results in Access? Thanks for everyones help. This is the
finishing touches on a long project and I am no good at this VBA stuff.
Click to expand...

In Access, don't open the query at all. The TransferSpreadsheet will,
behind the scenes, run and transfer the data. You won't see it.

To then open the spreadsheet (after transferring the data) add one
line of code:
Application.FollowHyperlink "c:\MyFolderName\SpreadsheetName.xls"

Of course, change the path to whatever your actual spreadsheet path
is.

Then click on the worksheet, if there is more than one in the
workbook.
 
Back
Top