Results 1 to 5 of 5

Thread: [RESOLVED] VBA coding to export Access 2007 query results into existing Excel 2007 spreadsheet

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2011
    Posts
    4

    Resolved [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

  2. #2

    Thread Starter
    New Member
    Join Date
    Aug 2011
    Posts
    4

    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.

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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.


    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).

  4. #4
    New Member
    Join Date
    Apr 2012
    Posts
    1

    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?

    .

  5. #5
    Hyperactive Member Granty's Avatar
    Join Date
    Mar 2001
    Location
    London
    Posts
    439

    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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width