Not sure if this is possible or not but I'll post anyways...
I multiple Excel files that on open prompt the user. the first is a message box that asks the user if they want to run the macros...if Yes, then there are a few inputs that are required from the user. when all is said and done a report is formatted to a nice and neat table to import into Access or Pivot report. There are 6 files that all have the same prompts just modify different reports.
I need these files to remain the same as many users use these.
The fun part....I am trying to automate a different project that will pull in the tables from Excel via a form and vba. I have the code to open excel, which works just fine...NOW I want to have on the access form a set of options that will be used to respond to the Excel prompts. EI - a box on the form that will ask "Do you run the macros?" you answer yes or no and click run, then the response you gave will be used for each file rather than having click yes or no on the Excel msgbox. Also there will be fields for all the inputs that Excel requires. essentially this will make the user of the access database able to enter the requirements once rather than 6 times.
I don't need all the files to run at once...they can run one after the other.
Is this possible? It tried to do a send keys from Access after open of the Excel file but since the first msgbox in Excel in the Workbook open() its still in the opening phase when asking to run the macros...thus the sendkeys does fire but not until after I respond to the msgbox.
Any help would be great. Thanks Jonathan
I multiple Excel files that on open prompt the user. the first is a message box that asks the user if they want to run the macros...if Yes, then there are a few inputs that are required from the user. when all is said and done a report is formatted to a nice and neat table to import into Access or Pivot report. There are 6 files that all have the same prompts just modify different reports.
I need these files to remain the same as many users use these.
The fun part....I am trying to automate a different project that will pull in the tables from Excel via a form and vba. I have the code to open excel, which works just fine...NOW I want to have on the access form a set of options that will be used to respond to the Excel prompts. EI - a box on the form that will ask "Do you run the macros?" you answer yes or no and click run, then the response you gave will be used for each file rather than having click yes or no on the Excel msgbox. Also there will be fields for all the inputs that Excel requires. essentially this will make the user of the access database able to enter the requirements once rather than 6 times.
I don't need all the files to run at once...they can run one after the other.
Is this possible? It tried to do a send keys from Access after open of the Excel file but since the first msgbox in Excel in the Workbook open() its still in the opening phase when asking to run the macros...thus the sendkeys does fire but not until after I respond to the msgbox.
Any help would be great. Thanks Jonathan
