Solved

Run Keyboard macro to help with data entry

Posted on 2016-11-02
4
34 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now