Output to Excel???

[spinkung]

Hey all,

Ok, i'm trying to output some queries to an excel file. I need to pass some criteria. I'm hoping to only use one query and not have to duplicate them all for each instance.

Here's what i'm trying....

'output to sheet1
        sql = "select * from Qry_Claims_Damaged_by_Courier " & _
              "where courier = '" & courier & ""
        DoCmd.OutputTo acOutputQuery, sql, acFormatXLS, pth  
        
'output to extra sheets
        sql = "select * from Qry_Claims_Lost_by_Courier " & _
              "where courier = '" & courier & "'"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sql, pth

....but the the output object name (sql) isn't happy. It expects the actual query name which works fine but hasn't got the criteria (courier) passed to it.

Is there a way to achieve what i'm trying????

Thanks. Spin.

 

[KenHigg]

The long answer is to step trhough the code and see waht the actual sql statement looks like when it tries to execute.

The short answer (my best guess) is that you tried to set up the second quote wrong:

"where courier ='" & courier & "'"

??

Also, I've never tried the 'extra sheets' thing -

 

[spinkung]

Thanks Ken.


This works fine...

'output to sheet1
DoCmd.OutputTo acOutputQuery, "Qry_Claims_Damaged_by_Courier", acFormatXLS, pth

....which is ok if you've set the criteria in the query already but i'm trying to pass some criteria so i can loop through and create different reports.

so what i need is...

sql = "select * from Qry_Claims_Damaged_by_Courier " & _
        "where courier = '" & courier & ""
DoCmd.OutputTo acOutputQuery, sql, acFormatXLS, pth

The SQL is fine, i've tested it and it works. I just don't think the DoCmd.OutputTo likes being passed the variable (sql)??

BTW. Transfer just adds extra sheets to the excel workbook.

 

[KenHigg]

The OutPutTo should have no idea there's a variable involved as you'ra creating a sql string. Another option is to save the sql as a stored query and export that, if it works like you have it coded...

 

[spinkung]

cracked it.


I had to create some queryDefs in order to use in the output to excel command.


so...

    Set Qd1 = CurrentDb.CreateQueryDef("DamagedByCourier", "select * from Qry_Claims_Damaged_by_Courier where courier = '" & courier & "'")

...then...

'output to sheet1
        DoCmd.OutputTo acOutputQuery, "DamagedByCourier", acFormatXLS, pth      'Enter table/query to be sent to excel

ta, spin.

 

[KenHigg]

Glad you have it working -