Using Access Form to call Excel VBA Modules in Multi-Step Process

[AlexB123]

Hi all,

I have a form that allows users to upload data to a database; I am updating the form, and re-writing the associated code contained in an Excel Macro Workbook to clean and prepare the data.

Originally, the user would press a single button on the form to call the first macro in the workbook and the first of three stages would run. After the first macro (excel) completed, the sub (in Access) would also be finished. The user would then either complete the next two steps then, or would save the workbook somewhere and open it again later to resume work (I believe they would also open the macro workbook, but I am not certain as the code is very unclear).

In order to run the next two steps to prepare the data, the user would use keyboard shortcuts (ctrl + shift + m, and ctrl + shift + n) to run the next to excel modules, in sequence, after they had completed whatever work was needed (NOTE: each step requires the user to perform other tasks in between the different modules, it cannot run all at once). The problem with the shortcuts is that they were not created using Excel's default methods; instead functions were added to the Excel VBA to obtain the keystate of the keyboard and pass those values into the two subs (I still do not understand how this worked).

My solution has been to save the file at each step, then to add more buttons --- i.e., the user runs the first macro as before, but then the file saves and closes. Next the user hits a new button to simply call and open the recent file to make changes, then save before closing. Then another button will call the second macro, which will save and close the file, and a fourth button will again call and open the modified file. Finally, the user will hit a fifth button to run the final macro.

My solution is not very good ... I am looking for better ways to do this. I am having a difficult time finding any sort of tips for working with excel files, via Access, in a similar manner. Can anyone provide some insight or recommendations?

Just looking for a different or a modified approach; happy to provide any necessary details or to clarify what is going on. I have added the code for the "shortcuts" below ...

Thanks!!!!

Declare Function GetKeyState Lib "User32" (ByVal vKey As Integer) As Integer
Const SHIFT_KEY = 16


Function ShiftPressed() As Boolean
'Returns True if shift key is pressed
    ShiftPressed = GetKeyState(SHIFT_KEY) < 0
End Function

Sub MacroPart3()
'
' MacroPart3 Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Do While ShiftPressed()
    DoEvents
Loop

'Code runs below ...

End Sub

 

[Micron]

Seems like automation would work for you. I don't have any experience with running excel macros from Access, but I do for opening Excel, then a workbook, then activating a sheet and doing things to it from Access. I found this site to be quite useful, although it is probably more about data transfer. However, there should be lots there to give you some guidance on how automation works. From the linked page, follow Data Imports and Exports then start with Exporting. If this alone isn't enough to complete your task, it will at least give you some clue as how to phrase your search engine inputs. I do think the code on declaring/setting the required objects will help a lot. For you, the missing part might be how to activate a macro from Access. Then again, when you learn the automation principles, you might not need the excel macros anymore.

 

[AlexB123]

Thanks Micron!

I will absolutely peruse this link. I also bought a reference book on "Mastering VBA for Office 2016" ... I have several on VBA and excel, or Access, but learning the best methods and approaches to problems across applications can be difficult; especially when the code originated with other people.

Do you have any experience declaring public variables in Excel? I'm wondering if I can declare the sheet names as public, then be able to run the macros with the same object variables (workbooks and worksheets) throughout my process?

 

[Micron]

I've read elsewhere that you can run an excel macro from access but haven't done that. Dont think you'd need a global variable in that case - unless the excel side needed one. I presume you dont actually mean a sub or function.

 

[AlexB123]

It's not the calling of macros from Access that I'm having trouble with ... It's the subsequent uses of excel that's messing me up. Calling the macro is fine, but then it's providing my users with time to make the manual updates that are needed, then continuing the process. My workbooks have fourteen worksheets, and my biggest issue is making sure the workbook names are consistent across the three phases of what I'm doing...

I suppose that I really need to think about how to phrase my question, and move it over to the Excel forum?

 

[Micron]

It's the subsequent uses of excel that's messing me up

but then it's providing my users with time to make the manual updates that are needed

Not sure I understand those statements. Sounds like you're trying to manipulate workbooks while people are editing them but surely that is not the case. Rephrasing might be a good idea since it appears I'm not grasping the issue. I'd have to understand the issue better before having any clue as to where this belongs.

EDIT - OK, went back to review after posting this. It does sound like an Excel issue if you're saying you don't know how to call the functions or macros from within Excel because it uses some sort of keyboard "state". Not sure why you'd involve Access in controlling those functions, especially if users are not finished with their inputs. Usually, there is some sort of data exchange when using Access to control Excel (Automation). I have that interpretation based on your thread title, but that doesn't seem to be the case here.

 

[AlexB123]

OK, let me try my best to rephrase.

Apart from launching the excel applications, consider that Access has little to do... the macros are launched by Access, and the data is ultimately imported in to access after the three steps of data manipulation (performed entirely by excel). Originally, only the first of three macros was launched by Access. Following the completion of the first macro, the second and third macros would be run (from excel) by the combination of shortcut keys. The reason for this is that the macros mainly perform conditional formatting on the data ...

In step one the macro breaks the data into subsets, then identifies which clients are missing info, and then creates SQL strings. After step one, the user takes the SQL strings and pastes them into a web browser tool. The tool spits out the query results. These are then pasted back into the workbook, and step two is ready.

In step two, the macro evaluates different criteria to determine if the customer request can be completed. If the request cannot be completed, that row is highlighted and labeled with the tag "Research" -- then step two is done. The user must now make use of several different proprietary systems to research the missing data, and then manually enter that data into the workbook. Then the user is ready for step three.

In step three, the macro prepares the data for upload in to Access. When it completes, the user is instructed to import the data back in to Access via the original form that began the process (via another form button).

To summarize: Twenty years ago, this entire process was done manually. Over time, several users wrote excel macros to speed up work flow, then they built Access databases. Much of the actual "work", the research pieces, will always be manually done. That leaves excel to prep, clean, and format the data so that (1), the data can be used to query a "real" database, and (2), excel organizes the data in to tabs and informs the user on further research that must be done, and (3), completes the process and imports all the updated data back into Access.


The shortcuts that were created (which allowed the user to launch parts two and three via excel) no longer work. My current issue is getting parts two and three to work together ... essentially, they are the same workbook (the same tabs, the same number of sheets, the same base data, but at different steps in the process), but I have had to save each step as a different workbook; i.e., they work "successively" ... instead of oneWb.xlsx called by macroWb.xlsm, I have oneWb.xlsx, twoWb.xlsx and threeWb.xlsx all created and run by macroWb.xlsm.

The issue with "

providing my users with time to make the manual updates that are needed ​

" is this: since my users need time to upload the SQL Strings the data warehouse (and then copy the results back to the WB) at the end of Step One, and then time to perform the research needed at the end of step two, I have created three form buttons. I don't know of any other way (besides the original shortcut approach) to launch and complete the process with a single block of code attached to one button.

Does any of that make sense? Please let me know if I can shed light on any of this, I'm having a difficult time finding the words to describe my problems and my needs.

This is certainly a complicated business process ... and would probably be made much easier if I could do the ETL and data cleaning/prep in another program besides Excel ... but I've got to work with what I have.

Thank you immensely, Micron!!!

 

[Micron]

Before I try to digest all of that, it seems that the goal hinges on you having some sort of 'flag' that provides the status so that the procedure knows whether or not to run only one macro or two, or all. If you had that somewhere in Access, it would be easier, otherwise, Access is going to have to look in the workbook for some value that provides the info. If you can't create this flag, I don't see how it's practical to try to run macros/code on a workbook from outside the workbook when the workbook isn't ready.

 

[AlexB123]

I think this is exactly the sort of advice I was looking for ...

How might this flag work? Would this be embedded in a sub? Or somehow in the form itself? Possibly a text box?

 

[Micron]

Likely a table, since that's where data should be, and this would be data. You might need a table for it, even if it's only one record. Can't say how exactly since I don't know the business process, but I can 'frinstance. Maybe a user gets a phone call or email "data is at stage 2". No idea if you wait for completion or not at this point. Or user goes in db rather than emailing someone. At some point, user goes into db and opens form and selects the appropriate "stage" value from a combo and saves the record. Whenever the db procedure is run to work with the workbook, you first get the status. Again, I have no idea if this is for one workbook or not. If not, then do you enter all the workbooks in a table and flag them individually? Or do you keep this flag/status in a worksheet? I have no way of knowing, but you probably do or could make the decision.