Results 1 to 3 of 3

Export Query Results to Excel Template

  1. #1
    laterdater is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    4

    Export Query Results to Excel Template

    I am a total n00b when it comes to VBA and MS Access, so excuse any ignorance in this post...

    I have a module that will run a query and then two different buttons that will either show the data in MS Access or send it to a newly formed and named Excel Sheet--both of these commands were built with the auto macro generator. My data will have a constant number of columns but not rows.

    What I want to do is apply conditional formatting to everything exported to excel--specifically, I am splitting up my data by tab based on the "Measured Characteristic" property and then sorting that data based on "Part Number" and creating a header based on various other values (Date, Device, etc.). I made a macro in MS Excel that will do this all for me. I guess what seems to me to be the easiest way to get this formatting done would be to export my query to an excel sheet template with a "Raw Data" tab where this organizing macro exists and then have the MS Excel macro run automatically when the excel sheet is opened.

    What I can't figure out is how to export the data to a copy of this template and then have my Excel macro run. I also would like to let my users be able to save the file locally and to NOT be able to modify the template.



    I found this posting online where someone is trying to do something similar, but I honestly don't understand most of it (maybe just an explanation of this would really help): http://www.experts-exchange.com/Soft..._28186685.html

    Here is the automatically generated macro, if that helps:

    Code:
    Private Sub EIDSCRExcel_Click()
    On Error GoTo EIDSCRExcel_Click_Err
    
    
        DoCmd.OutputTo acOutputQuery, "qry-0550-ECRData", "ExcelWorkbook(*.xlsx)", "", True, "", , acExportQualityPrint
    
    
    
    
    EIDSCRExcel_Click_Exit:
        Exit Sub
    
    
    EIDSCRExcel_Click_Err:
        MsgBox Error$
        Resume EIDSCRExcel_Click_Exit
    
    
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,754
    export the data, then control XL to run your macro.
    PUT YOUR MACRO IN THE PERSONAL worksheet so it can run from any workbook.


    Code:
    SUB btnStart_Click()
    
    vFile = "c:\folder\myExclFile.xls"
    
       'export the workbook
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, vQry, vFile, True, vTab
    
    '*******************
    ' control XL
    '*******************
    'NOTE : you MUST have EXCEL object checkmarked in the VBE menu: TOOLS, REFERENCES
    
    dim xl As Excel.Application
    Set xl= CreateObject("excel.application")
    with xl
        .Visible = true
        .Workbooks.Open vFile
        
        .Application.Run ("MyMacro")
    end with
    set xl = nothing
    end sub

  3. #3
    laterdater is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    4
    So this method would not work because the person who exports the data may not have my macro. This is why I wanted to copy a template (stored on my shared drive.)

    Quote Originally Posted by ranman256 View Post
    export the data, then control XL to run your macro.
    PUT YOUR MACRO IN THE PERSONAL worksheet so it can run from any workbook.


    Code:
    SUB btnStart_Click()
    
    vFile = "c:\folder\myExclFile.xls"
    
       'export the workbook
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, vQry, vFile, True, vTab
    
    '*******************
    ' control XL
    '*******************
    'NOTE : you MUST have EXCEL object checkmarked in the VBE menu: TOOLS, REFERENCES
    
    dim xl As Excel.Application
    Set xl= CreateObject("excel.application")
    with xl
        .Visible = true
        .Workbooks.Open vFile
        
        .Application.Run ("MyMacro")
    end with
    set xl = nothing
    end sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 20
    Last Post: 03-13-2014, 12:50 PM
  2. Replies: 3
    Last Post: 01-07-2014, 03:39 PM
  3. Replies: 1
    Last Post: 10-28-2013, 12:32 PM
  4. Replies: 1
    Last Post: 04-30-2012, 05:10 PM
  5. Export table to excel using excel template
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 1
    Last Post: 06-27-2011, 02:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums