Solved

Run Keyboard macro to help with data entry

Posted on 2016-11-02
4
42 Views
Last Modified: 2016-11-05
To create an invoice in our accounting package (MYOB) we need to transfer information from various fields displayed on an MS Access form into corresponding fields in MYOB. When I have to do many invoices I currently create a csv file from MS Access and import to MYOB but when I need to do one or two invoices it is quicker to just copy and paste data. However if I could create a keyboard macro then it would be even quicker.  

So is there any way of using VBA to run a keyboard macro that will enter a sequence like:
   CustomerName <Tab><Tab><Tab><Tab> OrderNumber <Tab> OrderDate <Tab> Description <Tab><Tab> Amount <Tab> JobId
0
Comment
Question by:Rob4077
  • 2
  • 2
4 Comments
 
LVL 57
ID: 41871889
<<However if I could create a keyboard macro then it would be even quicker.  >>

Yes, this is possible using SendKeys in VBA.   However you need to know which window is the MYOB one, which might get a little tricky, but is doable.

SendKeys sends keystrokes to the active window.   Here's an example of using the Windows Calculator:

Dim ReturnValue, I
ReturnValue = Shell("CALC.EXE", 1)	' Run Calculator.
AppActivate ReturnValue 	' Activate the Calculator.
For I = 1 To 100	' Set up counting loop.
	SendKeys I & "{+}", True	' Send keystrokes to Calculator
Next I	' to add each value of I.
SendKeys "=", True	' Get grand total.
SendKeys "%{F4}", True	' Send ALT+F4 to close Calculator.

Open in new window


Something you want to try and tackle?  I don't have MYOB here, so we'll have to feel our way through this.

Jim.
0
 

Author Comment

by:Rob4077
ID: 41871916
Hi Jim, I figured it would end up with Sendkeys.

What I want to enter is relatively basic, as I said in my question - that's the exact sequence I need. The part I thought would be tricky would be the step AppActivate ReturnValue. I don't need, or want, this macro function to launch MYOB, or open to the correct window - the user can do that. I just need to fire the sequence.

I thought there was some way of naming the function instead of allocating a number to it e.g. AppActivate "MYOB" but I don't know how to pick that up. Alternatively can I load the data into a keyboard macro and get the user to press an Fkey when the cursor is in the right spot?
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 41872081
<< Alternatively can I load the data into a keyboard macro and get the user to press an Fkey when the cursor is in the right spot?>>

 Your thinking more along the lines of a programmable keyboard, or one programmed at the OS level.  That's not the case here with SendKeys

Without doing the AppActivate in Access, you would be leaving it up to the user to switch windows.  The best that you could do then is:

1. Tell the user to "Click this, then switch to the MYOB window".
2. Wait XX seconds
3. Send the keystrokes and hope they did switch the windows.

This is why Sendkeys is generally considered "bad"; because it sends to the active window whatever that currently is, and the user can effect that as well by clicking another window in the middle of sending.

 I would do the above as a test to see if you can get it to work.   If so, then you either:

a. Leave it like that and hope for the best.
b. Dive into some windows api calls to find the MYOB window and make it active (not super hard, but will take a little bit of effort to identify the correct window).

Jim.
0
 

Author Comment

by:Rob4077
ID: 41875197
Hi Jim, thanks for your suggestions. I didn't go to the office on Friday and won't be back there till Monday now so won't be able to try anything till then.

MYOB enables several windows to be opened so if I were to try to code the entire process I would have to launch MYOB. Select the right tab then press the right button to open the needed window before pasting in the data. Not sure what I could do with api calls to do what is needed so I think option a is worth a shot. I am only going to use it to enter data without committing it (pressing enter) so if the user ends up in the wrong window they will have to make the correct. I had never thought of waiting a few seconds for the user to select the window before executing the Sendkeys sequence but, crude as it is, I think that will suffice for this application. All I want to do is make it a little easier.

Thanks again for your help
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question