Sending query results to Excel

[TimT]

I'm trying to send the results of a query to Excel and I'm having trouble
with the code.

Sub AutomateExcel()

Dim strSQL As String
strSQL = "SELECT [qryName].* FROM [qryName]"

Dim xlApp As Variant
Dim xlBook As Variant
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Excel\Reports\ExcelReports.xls")
xlApp.sheets("Sheet1").Range("A1") = strSQL
xlBook.Close SaveChanges:=True
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing



End Sub

 

[Nick via AccessMonster.com]

strSQL = "SELECT [qryName].* FROM [qryName]"

Tim,

Assuming qryName is the name of a predefined query, just use the DoCmd.
TransferSpreadsheet method in VBA.

If you are using the SQL string to build a query in code then export it, you
may need to create a QueryDef object and export the querydef.

Hope I understood your question, and hope it helps.

-Nick

 

[Klatuu]

That is not going to work. There are two ways you can do this. If all you
want to do is get the results of a query to an excel spreadsheet, it is
easier to use the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acExport, , "qryName",
"C:\Excel\Reports\ExcelReports.xls", True

If, on the other hand, you need to do formatting or other actions in the
spreadsheet, you can use the CopyFromRecordset method. For that you can do
it this way:

Sub AutomateExcel()
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset( "qryName")

Dim xlApp As Variant
Dim xlBook As Variant
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Excel\Reports\ExcelReports.xls")
xlApp.sheets("Sheet1").Range("A1").CopyFromRecordset rst
xlBook.Close SaveChanges:=True
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
rst.Close
Set rst = Nothing


End Sub

 

[TimT]

I made the following changes and get a runtime error 3071 - to complex?
I also wanted to send the results to sheet1 range a1

Function AutomateExcel()

Dim strSQL As String
Dim strQName As String
Dim strFPath As String

strFPath = "C:\Excel\Reports\ExcelReports.xls"

strQName = "qrySTATE_CALC_4_TentTax by Division by State"

strSQL = "SELECT strqname.* FROM strqname"


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQName,
strFPath


End Function

 

[Klatuu]

That error indicates there is a problem in the query. Try running the query
manually to see if you get the same problem.

I made the following changes and get a runtime error 3071 - to complex?
I also wanted to send the results to sheet1 range a1

Function AutomateExcel()

Dim strSQL As String
Dim strQName As String
Dim strFPath As String

strFPath = "C:\Excel\Reports\ExcelReports.xls"

strQName = "qrySTATE_CALC_4_TentTax by Division by State"

strSQL = "SELECT strqname.* FROM strqname"


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQName,
strFPath


End Function

I'm trying to send the results of a query to Excel and I'm having trouble
with the code.

Sub AutomateExcel()

Dim strSQL As String
strSQL = "SELECT [qryName].* FROM [qryName]"

Dim xlApp As Variant
Dim xlBook As Variant
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Excel\Reports\ExcelReports.xls")
xlApp.sheets("Sheet1").Range("A1") = strSQL
xlBook.Close SaveChanges:=True
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing



End Sub

 

[TimT]

Brilliant! thank you!
There were parameters in the query.

That error indicates there is a problem in the query. Try running the query
manually to see if you get the same problem.

I made the following changes and get a runtime error 3071 - to complex?
I also wanted to send the results to sheet1 range a1

Function AutomateExcel()

Dim strSQL As String
Dim strQName As String
Dim strFPath As String

strFPath = "C:\Excel\Reports\ExcelReports.xls"

strQName = "qrySTATE_CALC_4_TentTax by Division by State"

strSQL = "SELECT strqname.* FROM strqname"


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQName,
strFPath


End Function

I'm trying to send the results of a query to Excel and I'm having trouble
with the code.

Sub AutomateExcel()

Dim strSQL As String
strSQL = "SELECT [qryName].* FROM [qryName]"

Dim xlApp As Variant
Dim xlBook As Variant
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Excel\Reports\ExcelReports.xls")
xlApp.sheets("Sheet1").Range("A1") = strSQL
xlBook.Close SaveChanges:=True
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing



End Sub