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

    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0

    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 .

    Code:
    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:

    Code:
    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?

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

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,620
    Rep Power
    650
    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.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm

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, 10:10 AM
  2. Error while opening excel from ASP file
    By sripriyak in forum ASP Development
    Replies: 0
    Last Post: September 19th, 2008, 01:46 PM
  3. Problem in opening excel file
    By pravinth in forum ASP Development
    Replies: 7
    Last Post: April 3rd, 2008, 03:57 AM
  4. Opening excel file
    By todd2006 in forum ASP Development
    Replies: 3
    Last Post: March 31st, 2008, 01:18 AM
  5. Opening an excel file
    By Neider in forum .NET Development
    Replies: 0
    Last Post: June 23rd, 2003, 04:21 PM

IMN logo majestic logo threadwatch logo seochat tools logo