[RESOLVED] VBA coding to export Access 2007 query results into existing Excel 2007 spreadsheet
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
Re: VBA coding to export Access 2007 query results into existing Excel 2007 spreadshe
For anyone who sees this and is interested, I finally figured it out.
To work around this export to an Excel 2007 workbook with Macros issue:
1) Convert Excel File to .xlsb format (Binary format). Excel version 12 .xlsb format is format 9.
2) Use this VB command in Access
DoCmd.TransferSpreadsheet acExport, 9, "<Query NAme>", "C:\<Location>\<FileName>.xlsb"
This will create a tab named the same as the query. The first time you do this you need to let Access make that tab in you file. After that Access will replace the data on the tab each time you rerun the macro with the code in step 2 in it.
Hope this saves someone else all the time I spent figuring this out.
Re: VBA coding to export Access 2007 query results into existing Excel 2007 spreadshe
Thanks for sharing your solution, I'm sure others will find it useful. :thumb:
As you now have it sorted out, could you please do us a little favour, and mark the thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)
You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).
Re: [RESOLVED] VBA coding to export Access 2007 query results into existing Excel 200
Here's a code that works for me.
I am using Windows 7 with Office 2007.
This code is in the On Click properties of a control button, on a Form in Access 2007.
When I click the control button it opens a select query and also sends the data from the query to an existing Excel 2007 work-book which is in a meacro enabled format .xlsb , in this format because the Excel work-book has a macro, which I use to create an Outlook 2007 Distribution List using the data that came from Access.
My only problem is that I need to find a way to stop the column headings from transferring from the Access query to the Excel work-sheet.
Here's the code.
Code:
'------------------------------------------------------------
' Command104ContrDonatWeekly_Click
'
'------------------------------------------------------------
Private Sub Command104ContrDonatWeekly_Click()
On Error GoTo Command104ContrDonatWeekly_Click_Err
DoCmd.OpenQuery "Contributors Who Donated in Past Week", acViewNormal, acEdit
xlfile = "C:\Users\Michael1\Desktop\KSN\DistributionListWeekly.xlsb"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, _
"Contributors Who Donated in Past Week", xlfile, True, "EmailList"
Shell "Excel.exe " & xlfile, vbNormalFocus
Command104ContrDonatWeekly_Click_Exit:
Exit Sub
Command104ContrDonatWeekly_Click_Err:
MsgBox Error$
Resume Command104ContrDonatWeekly_Click_Exit
End Sub
Can anyone assist me to stop the transfer of the column headings?
.
Re: [RESOLVED] VBA coding to export Access 2007 query results into existing Excel 200
Isn't the 'true' parameter in your TransferSpreadsheet the HasFieldNames option?
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, _
"Contributors Who Donated in Past Week", xlfile, False, "EmailList"
No MS access here atm, I could be wrong!