1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Rep Power

    Opening excel file through access via combobox selection

    Good afternoon!

    I have a combobox that has a set of values, for simplicity's sake we'll say the values are Test1,Test2,Test3, etc. For each of these values I have a query set to retrieve information pertaining to only that item from my database. I then made a macro exactly like the following for each entry in the combobox to export the information to excel.:

    Action Arguments
    TransferSpreadsheet Export, Excel Workbook, Qry Retrieve Test1 Data, G:\Access MG Exports\Test1.xlsx, No,
    MsgBox Test1 is now updated on the G drive under "Access MG Exports"., Yes, Information, Update complete!

    So this macro takes the data and exports it to an .xlsx file on my computer's G drive, and to run the macro I simply have a button that will start the macro that is tied to the chosen value in the combobox .

    Private Sub Command54_Click()
    DoCmd.RunMacro Me.MyCombo3.Column(1)
    End Sub
    What I am trying to do now is after the macro generates the file have it automatically open in excel. I have found some vba code that I am able to assign to a separate button, however I must specify in the code which file it will open:

    Private Sub Command63_Click()
    Dim appExcel As Excel.Application
     Dim myWorkbook As Excel.Workbook
      Set appExcel = CreateObject("Excel.Application")
        Set myWorkbook = appExcel.Workbooks.Open("G:\Access MG Exports\Test1.xlsx")
        appExcel.Visible = True
        Set appExcel = Nothing
        Set myWorkbook = Nothing
    End Sub
    Is there a way I can modify this code to open the excel file pertaining to the value chosen in the combobox?

  2. #2
  3. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    The Great Land
    Rep Power
    Concatenate variables. Reference to control on form is a variable.

    Set myWorkbook = appExcel.Workbooks.Open("G:\Access MG Exports\" & Me.MyCombo3.Column(1) & ".xlsx")
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.

Similar Threads

  1. Opening an excel file from VB in Access
    By Sneakz in forum Microsoft Access Help
    Replies: 4
    Last Post: February 9th, 2010, 11:10 AM
  2. Error while opening excel from ASP file
    By sripriyak in forum ASP Development
    Replies: 0
    Last Post: September 19th, 2008, 02:46 PM
  3. Problem in opening excel file
    By pravinth in forum ASP Development
    Replies: 7
    Last Post: April 3rd, 2008, 04:57 AM
  4. Opening excel file
    By todd2006 in forum ASP Development
    Replies: 3
    Last Post: March 31st, 2008, 02:18 AM
  5. Opening an excel file
    By Neider in forum .NET Development
    Replies: 0
    Last Post: June 23rd, 2003, 05:21 PM

IMN logo majestic logo threadwatch logo seochat tools logo