Transfer Access into Excel problem

[BorisGomel]

Hi ,

I hope you will help me with this samll problem .



I am trying to export query off Access into Excel spreadsheet and I am using this code below:

DimstDocName AsString
DimtheFilePath AsString
stDocName = "Mediation4"
DoCmd.OpenQuery stDocName

MsgBox "Look on your desktop for the report."

theFilePath = " Y:\My Documents\UpdateChecks\Checks”
theFilePath = theFilePath & stDocName & " "
DoCmd.TransferSpreadsheet acExport, 10, reportName, theFilePath, True

EndSub

Where
Mediation4 is name of query
Y:\My Documents\UpdateChecks\Checks-directory where is file xsl is located (Chekcs in this case)
The access debugs this by :" A value you eneterd for the speadsheettype argument is invalid".

The code is in the click event of command box

I am trying to fugure it out but so far got no success.

Thank you in advance for advice how to fix it .

The Best Regards.

[alansidman]

Change your docmd line to read

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, theFilePath, True

The acSpreadsheetTypeExcel9 is for Excel 2002.
The acSpreadsheetTypeExcel10 is for Excel 2003
The acSpreadsheetTypeExcel12 is for Excel 2007

Alan

[BorisGomel]

Good Aftetnoon Alansidman,

I insert your code and it works prefectly .

Thank you sir ,

Will mark as solved .

Sir , just one small though if you give me some hint .

The transferiinh Access Query into the Excel spreadheet is working fine , but it would be fine if the spreadsheet opens up once the transfer completed via Vb code in the same command button .I would not think about it , but when you helped me achived this , may be this small step would not be hard to help me with this

Thank you for your time sir .

Really appreacite your help and time.

[alansidman]

Add the following code to your current code to open the spreadsheet. change the path and file name as appropriate.

Dim xlTmp As Excel.Application
Set xlTmp = New Excel.Application
xlTmp.Workbooks.Open "C:\Excel1.xls"
xlTmp.Visible = True

Alan

[BorisGomel]

Thank you very much !!!

It works!!

Thank you again,

The Best Regards.