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

 

[xenou]

The shortcuts that were created (which allowed the user to launch parts two and three via excel) no longer work.

Do you want to fix the shortcuts or is that moot now?

 

[AlexB123]

If I could get them to work, it would probably make my life easier ...

 

[xenou]

The way I remember it you set the shortcuts in the macro dialog box:
https://www.excelcampus.com/vba/excel-vba-macro-shortcuts/#13

That looks like what you've got here (possibly)