Running an Excel Macro from Access

[ukdane]

Is it possible to Press a Button on an Access 2003 form that will open and run some code in an Excel Workbook, and then once the code has run, extract the results from Excel, and place them in a table in Access 2003?

Ideally, I'd like to avoid using Excel altogther, however the data I want to place into the access table comes from a different program, and the only way I know to get the data from it is by copying the screen to the clipboard, and importing the clipboard contents to Excel. Once there, a series of Formulas and VBA reformat the screen dump into valid data that can be used in Excel or Access. So I need to use Excel to reformat the data before it is copied into Access.

Help greatly appreciated-
Cheers

[CreganTur]

Ideally, I'd like to avoid using Excel altogther, however the data I want to place into the access table comes from a different program, and the only way I know to get the data from it is by copying the screen to the clipboard, and importing the clipboard contents to Excel.

Is it possible to export data from this other application as a text file? I'm just curious about what your other options could be.

It is very possible to do what you want, I just want to explore your process a little more so we can get the best fir for you:thumb

[ukdane]

It might be possible in the future to do this, as a text file (that would relieve a headache for me) but currently it's not an option.

[CreganTur]

Here's an option for you:

You could create a linked table out of your Excel spreadsheet, and then run SQL or VBA to format the data and pull it into your tables.

I've attached an example database that shows how I accomplish this- it will create a linked table out of your spreadsheet, run some validation against it, and then pull the data from the spreadsheet into an Access table.

[ukdane]

Thanks for you help. I'll have a closer look at it tomorrow, and see if I can untilise it.

One thing I don't think I can see, is that I need the VBA in Access to activate a macro in Excel prior to it creating the link table.

This is because the Macro in Excel is what pastes the code from the clipboard into Excel, and then places it into the fields that I can place into the linked table.



I have a fully functioning program already up and running in Excel. The problem is that it runs using a shared workbook, so that everyone in the dept can update it simultaneously. This isn't ideal, hence my looking into adapting to an acess program. However I can't at this stage see a way to do this without processing the data through Excel.

Edit: I've been thinking about your earlier question about other methods of extracting the data.
The other program is a Reflection program, and therefore has a base in VBA. I'm certain therefore that I could assign the various information to a series of variables. (in fact I'm almost certain that it already exists in tabular form somewhere, as it is a database program) If that is the case, could access be used to extract the data from the variables in the reflection program, and place it directly into an access table?

[stanl]

What would your data look like if it were copied from the clipboard into Notepad instead of Excel - could you perform text parsing and obtain the same results as with Excel. Another question: when the data is obtained from the other application, is that a manual process or are you using sendkeys of an API?

Stan

[ukdane]

Spent an hour on the phone, apparently the Reflection program can connect directly to access.

I now have the information in a set of variables, and now need to "feed" it to access.

I need to open the correct access database, (using VBA), and insert the data into the correct fields on the table.

[CreganTur]

I need to open the correct access database, (using VBA), and insert the data into the correct fields on the table.
Using a connection, either DAO or ADO, would probably be the best way for you to accomplish this.

[ukdane]

Sorted thanks.

Next problem.....

I need to combine an sql with an if function.

Imagine I have extracted the variable "customernumber" from my main program, this number is a unique "primary key" in the database.

I need the script to do two things.

It needs to check the database to see if the customer number already exists in the database table.
1) If it does NOT exist, then all it needs to do is run an Insert SQL (no problem)
2) If it does exist, it then needs to ask the user if they want to overwrite the existing data. If they reply no, then it will run the Insert SQL. If they reply yes, then it needs to run an Update SQL.

Remember this code is being run in the parent programs vba, and not in the access database, but will need to be parsed to the database.

Also, if the code runs the Insert SQL, I've noticed that the end user doesn't see the change unless they close and then reopen the table.
Ideally, I need the user to be able to see the amended / inserted data instantly. Any ideas how best to approach this? If they view the data through a query, should I just run an update query function to "get" the new data?

Cheers