Components:
1) Access 2007 select query
2) Excel 2007 workbook with multiple tabs in either XLSM (Macro Enabled) or XLSB (Binary) format

I need to export the query from the Access database directly into the Excel spreadsheet.

I have tried saving to a file and then loading from excel via code where end user selects the file but they complain about speed because they have to do it for each account they work (file process & save takes less than 10 seconds and the import/open in excel maybe 10 sec more - yeah I know but you know users).

My issue is I cannot find any code that will let me load the query results directly into the Modeling spreadsheet.

I did read that Microsoft, in their great wisdom, didn't allow this into macro enabled workbooks (.xlsm) but that was a 2 yr old post.

I have also tried to use the .xlsb (binary format) but no luck.

I can get it to create a new file with the .xlsb extension, but it is corrupt and Excel will not open it.

Below is the current code with the most recent 2 attempts in red.

Here is the current code:



Dim dba As DAO.Database
Dim qdf1 As QueryDef
Dim strQuery3 As String
Dim coid As String
Dim AcctNum As String
Dim FlNm As String



DoCmd.Close acQuery, "Analysis_File_Qry"



Set dba = CurrentDb
Set qdf1 = dba.QueryDefs("Analysis_File_Qry")



strQuery3 = "SELECT tblConvVar_CustAcct.[PRODUCT ID], tblConvVar_CustAcct.[WF ELEMENT ID], tblConvVar_CustAcct.[WF ELEMENT DESCRIPTION], tblConvVar_CustAcct.[XAA TRANCODE DESCRIPTION], tblConvVar_CustAcct.[CUSTOMER COID], tblConvVar_CustAcct.[CUSTOMER NUMBER], tblConvVar_CustAcct.[CUSTOMER NAME], tblConvVar_CustAcct.[GROSS REVENUE DIFFERENCE], tblConvVar_CustAcct.[ELEMENT DIFFERENCE TOTAL CHARGE], tblConvVar_CustAcct.[XAA VOLUME], tblConvVar_CustAcct.[XAA UNIT PRICE]," & _
" tblConvVar_CustAcct.[XAA TRANCODE SERVICE CHARGE] , tblConvVar_CustAcct.[WF VOLUME], tblConvVar_CustAcct.[WF UNIT PRICE], tblConvVar_CustAcct.[WF ELEMENT SERVICE CHARGE], tblConvVar_CustAcct.[PR DESIGNATION]" & _
" FROM tblConvVar_CustAcct" & _
" WHERE (((tblConvVar_CustAcct.[CUSTOMER NUMBER])=[Forms]![Account_Analysis_Tool_lookup]![ACCT_Num]) AND ((1)<>False) AND ((tblConvVar_CustAcct.[PRINT OPTION]) Is Null Or (tblConvVar_CustAcct.[PRINT OPTION])='Y') AND ((tblConvVar_CustAcct.[XAA GROSS REVENUE])<>0));"

'coid = [Forms]![Account_Analysis_Tool_lookup]![Cust_Coid_II]
AcctNum = [Forms]![Account_Analysis_Tool_lookup]![ACCT_Num]

qdf1.SQL = strQuery3

DoCmd.OpenQuery "Analysis_File_Qry"
DoCmd.Requery

FlNm = "C:\AcctFiles\" & AcctNum & " - Analysis_File_Qry"

'DoCmd.OutputTo acQuery, "Analysis_File_Qry", "MicrosoftExcel(*.xlsb)", "C:\AcctFiles\preanalysis_EID_price_modeler.xlsb!Account_element_drilldown", True, ""

'DoCmd.TransferSpreadsheet 1, 8, "Analysis_File_Qry", "C:\AcctFiles\preanalysis_EID_price_modeler.xlsb", True





DoCmd.OutputTo acOutputQuery, "Analysis_File_Qry", _
acFormatXLS, FlNm & ".xls", False

DoCmd.Close acQuery, "Analysis_File_Qry"


End Sub


Any Ideas?

Thanks