VBA Macro plus Sendkey

[devdog99]

Good day all,

I am a total newbe to VBA. I used to use a ghosting program till I could no longer utilize thumbdrives on govt computers. So now I am trying my hand at VBA.

I want to copy a list of report numbers from an excel file labeled "Daily Tools" from column B of sheet 1, then switch to a non Office program and run the rest of the VBA using Send keys.

The SendKeys portion is flawless and the Macro will work in the excel file by itself. How do I get them to talk to each other, and then loop untill there are no longer any report numbers?

Dont laugh to hard at my coding, like I said, I'm a self taught newbe and work with what I know.

Thank you



Sub EOSToolbox()


'
' MCNCopy Macro
'
' Keyboard Shortcut: Ctrl+m
'
Range("B2").Select
Selection.Copy

AppActivate ("NALCOMIS IMA - [Maintenance]")

SendKeys ("%ms"), True
SendKeys ("^v~"), True
SendKeys ("%j"), True
SendKeys ("%p"), True
SendKeys ("{TAB}"), True
SendKeys ("{TAB}"), True
SendKeys ("{TAB}"), True
SendKeys ("%i"), True
SendKeys ("username{TAB}"), True
SendKeys ("password"), True
SendKeys ("~"), True
SendKeys ("%S"), True
SendKeys ("GV~"), True
SendKeys ("%b"), True
SendKeys ("%x"), True
Application.Wait (Now() + TimeValue("0:00:02"))



End Sub

 

[Teeroy]

Hi, and welcome to the forum. Don't worry about judging, everyone's a newbie at some point .

You may need to be a bit clearer about what you are trying to achieve. Does the other program need to interact with the spreadsheet and how will the information transfer occur? By clipboard, CSV etc.

If you want to start from the other program, and it isn't an office product, then you may need to integrate the two using VBScript (which is different to VBA). On the plus side sendkeys is actually a VBScript technology so you've got that part worked out.

The trickiest part about using sendkeys is ensuring that the correct window has focus otherwise you can really create havoc.

 

[Michael M]

@Teeroy
Yay, 2000 posts...well done !!....

 

[devdog99]

Hi, and welcome to the forum. Don't worry about judging, everyone's a newbie at some point .

You may need to be a bit clearer about what you are trying to achieve. Does the other program need to interact with the spreadsheet and how will the information transfer occur? By clipboard, CSV etc.

If you want to start from the other program, and it isn't an office product, then you may need to integrate the two using VBScript (which is different to VBA). On the plus side sendkeys is actually a VBScript technology so you've got that part worked out.

The trickiest part about using sendkeys is ensuring that the correct window has focus otherwise you can really create havoc.

Thank you for your reply.

I would like to give screen shots, but this is a govt program and would probably be frowned upon. Let me give a bit of back story on what is actually happening.

In one program, called NALCOMIS, I get a series of toolboxes that I need to electronically sign off. Meaning I am saying all tools are accounted for after work is completed.

I export this list of toolbox numbers to excel. The excel workbook is called Daily Tools. All data is on sheet 1 and in column B.

What I want the whole macro to do is, copy the first tool box number from B2 and continue with the rest of the programing string after "AppActivate ("NALCOMIS IMA - [Maintenance]")". On the programing string I provided on my inital post, everything under "AppActivate ("NALCOMIS IMA - [Maintenance]")" works perfectly. What I want it to do is go to screen "X", paste the toolbox number, apply a user name and password, do a final check and continue to the next work order. Applying the next tool box number. Its getting the copied data from the excel spreadsheet to paste into my other program where my problem lays. And continue to loop untill reaching the end of toolbox numbers in column B, which can vary in length.

I know my data for copying the toolbox numbers:
Range("B2").Select
Selection.Copy
is missing a lot, I just don't know how to procede with it.


Is that a little bit more information?

 

[Michael M]

Assuming the SendKeys do the work required....would this help
But how do you get back to the workbook ?
Is that what the last couple of SendKeys Do ??

Sub EOSToolbox()
' Keyboard Shortcut: Ctrl+m

Dim lr As Long, r As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
For r = 2 To lr
Range("B" & r).Copy
AppActivate ("NALCOMIS IMA - [Maintenance]")
SendKeys ("%ms"), True
SendKeys ("^v~"), True
SendKeys ("%j"), True
SendKeys ("%p"), True
SendKeys ("{TAB}"), True
SendKeys ("{TAB}"), True
SendKeys ("{TAB}"), True
SendKeys ("%i"), True
SendKeys ("username{TAB}"), True
SendKeys ("password"), True
SendKeys ("~"), True
SendKeys ("%S"), True
SendKeys ("GV~"), True
SendKeys ("%b"), True
SendKeys ("%x"), True
Application.Wait (Now() + TimeValue("0:00:02"))
Next r
End Sub

 

[Teeroy]

@Teeroy
Yay, 2000 posts...well done !!....

Thanks @Michael M, I've got a long way to go to equal your tally

 

[Teeroy]

Tool control, what fun .

I don't miss that but it is a necessary evil. Luckily I work in a different aspect of aviation now.

I think that the loop that @Michael M added will solve your problem for you.