Solved

Run Keyboard macro to help with data entry

Posted on 2016-11-02
4
47 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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