Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Opening excel file through access via combobox selection

Results 1 to 2 of 2
Share This Thread →
  1. #1
    dutch245 is offline Registered User
    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
    June7's Avatar
    June7 is offline Moderator
    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    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

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates