November 15th, 2012, 01:21 PM
Opening excel file through access via combobox selection
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.:
||Export, Excel Workbook, Qry Retrieve Test1 Data, G:\Access MG Exports\Test1.xlsx, No,
||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 .
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 Command54_Click()
Is there a way I can modify this code to open the excel file pertaining to the value chosen in the combobox?
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
November 15th, 2012, 07:11 PM
Concatenate variables. Reference to control on form is a variable.
Set myWorkbook = appExcel.Workbooks.Open("G:\Access MG Exports\" & Me.MyCombo3.Column(1) & ".xlsx")