Exporting to Excel

  • Thread starter Thread starter Jazz57
  • Start date Start date
J

Jazz57

Good afternoon

i have some data on a Query that i want to transfer to Excel.

I have allready my command but i want the file from the path to be achieved
by a construction and concatenated with the rest of the path.

Explaning with the code:

Private Sub CmdExcel_Click()

Dim ficheiro As String
Dim anof, mesf As String

anof = Year(Date)
mesf = Month(Date)

ficheiro = "AVD" & anof & mesf & ".xls"

DoCmd.TransferSpreadsheet acExport, , "QDadosParaExcel", "C:\Producao\"
& "ficheiro"", yes, , True"

-----------------------------------------
When i put a debug.print instruction after it is created the ficheiro , i
received the following name: AVD200712.xls which is correct

When i try to make it works with the instruction i have it doesn't work and
i receive the message:

Run-Time error '3436'

Failed on creating the file

I suppose this message has something to do with the process that i made to
construct the name of my file.

Hope that someone can help me

Many thanks in advance for the help and support

End Sub
 
DoCmd.TransferSpreadsheet acExport, , "QDadosParaExcel", "C:\Producao\"
& "ficheiro"", yes, , True"
Click to expand...

The quote marks " round "ficheiro" and afterwards are incorrect. Also,
you seem to be trying to pass True to the UseOA argument. As far as I
can tell this does not do anything (the Access 2003 documentation here
http://msdn2.microsoft.com/en-us/library/aa220766(office.11).aspx says
it is not supported). Try this:

DoCmd.TransferSpreadsheet acExport, , "QDadosParaExcel", _
"C:\Producao\" & ficheiro, True
 
Back
Top