TransferSpreadsheet with SQL String

[naungsai]

Dear folks
In the place of “Table Name” below, I have put the name of one of my sql table.
DoCmd.TransferSpreadsheet acExport, , "qF01", "D:\Stock.xls", True, "Form01"
I want to know whether I could put the sql string like “Select * from qF01”.
Thanks in advance.

 

[Galaxiom]

Create a QueryDef with the query string as its SQL property.

Then use that in the transfer.

 

[naungsai]

Dear Friend
Sorry for double posting. I have try the following post.

[SIZE=3][FONT=Times New Roman]Private Sub Export_to_Excel_Click()[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Dim curDatabase As DAO.Database[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim QueryName As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim qryDef As DAO.QueryDef[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim strSelect As String[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Set curDatabase = CurrentDb[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]QueryName = ""[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]strQueryName = QueryName[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]'strSelect = "SELECT " & strCrit & " AS Period,"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]'strSelect = strSelect & strCritNO & " AS 1stDate,"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]strSelect = "SELECT qF01.Period, * FROM qF01 WHERE (((qF01.Period)=forms!FFMMIS!txtPeriod));"[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Set qryDef = CurrentDb.QueryDefs(strQueryName)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]qryDef.SQL = strSelect[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]qryDef.Close[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , strSelect, "D:\10.FFM\FFMTables.xls", True, "Form01"[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]End Sub[/FONT][/SIZE]

But it doesn't work. Please help me check it.

 

[vbaInet]

Are you getting an error?

 

[Galaxiom]

You need to give the querydef a name. That name then needs to be used as an argument in the TransferSpreadsheet Method.

 

[boblarson]

Or you could download the Export To Excel Function from my website and paste it into a standard module and then just call it and pass the SQL string instead of a table name or query name. It works great and no querydefs needed.

 

[vbaInet]

I'm always confused where to look for stuff on your site Bob

Under which section would I find the Access to Excel code?

 

[vbaInet]

Found it!

Code Snippets

 

[vbaInet]

So there you go:

http://www.btabdevelopment.com/ts/default.aspx?PageId=10