Control V

[ussenterprise]

I don't get why it was working. And now it is not.

Please Login or Register  to view this content.

I know that everything works up to the Application.SendKeys statement.

I have manually run the ClearClipboard, checked to see if any data is in the clipboard both by looking at the home tab (Paste button) and by manually pressing control+v.
After I run the Sub, the proper cell is highlighted. But nothing happens (The 15 second wait more than compensates for the plink cmd to open, run, and close by more than 10 seconds).

I can go to the home tab and press the paste button and the data is placed.
I can manually press control+v and the data is placed.

I have attempted:

Please Login or Register  to view this content.

(As well as multiple other variations...)

I also tried these (with only the cell.activate and one of the below):

Please Login or Register  to view this content.

All of these get nothing. It's as though SendKeys is broken...

Any thoughts?

[Leith Ross]

Hello ussenterprise,

SendKeys communicates with whichever window is the ActiveWindow. Only one window is allowed to receive input at any given time. This is main reason it's use is discouraged in multiple application environments.

The other problem is synchronization. The Shelled program is running asynchronously. Once it it started, control is returned back to your procedure. This why the Wait statement is added. However, this is not a foolproof method.
If somethings happens to the other program, you have no way of knowing what the problem is and your VBA continues on.

If you were to explain in more detail what you want to achieve, I could probably suggest an alternate method.

[ussenterprise]

Hello Leith Ross,

Phooey! lol

Here's what I see happening when I press the button:

DOS prompt pops under for about 5-10 seconds depending on connectivity. Then closes.

At 15 seconds, the cell that I want the data in is activated (I can see the cell highlighted. Then, I have a MsgBox that pops up (for testing only...) after the controlV.

What I need is the output from a commandp laced in a specific cell relative to the device. There are hundreds of devices spread across many tabs. Each with 7 or 8 macros. (This macro is assigned to one of them at the end of the macro. All other commands are currently commented out so that only this part is active).

I would have thought that the cell.activate would have brought focus back to the spreadsheet and cell. Should I do an activeworksheet.active, then cell activate? (I'm going to go test that now...)

[ussenterprise]

Hello ussenterprise,

SendKeys communicates with whichever window is the ActiveWindow.

It's actually funny that you mentioned this. I selected one of the DOS windows (before commenting out...) while it was running and I saw the controlV placed in that window! (Which is why I added the 15 seconds..)

[ussenterprise]

Here is a truncated version of one of my macros:

Please Login or Register  to view this content.

What this does is checks for a device type 6 rows down from the button. If it matches, it then runs a plink command to the IP which is 7 rows down to output to a file in a directory on the C drive called Test\[whatever is in cell A1]\[the device 6 rows down]\[device hostname 2 rows up]_[Date/Time Stamp]_[command].text andand exit.
It continues for each line and all are setup this way, save for the VMAX which does not use IP for communication. It uses the SYMID, which is 8 rows down.
It then runs the command to get the version for each device piping to the clipboard, and attempts to place that data with a control+V 11 rows down.

The only think I can think of that may be interfering is, as you said, synchronization. With everything running asynchronously, perhaps I need a separate macro specifically for this function...(I hope not... it would mean I have to add another row and modify ALL of my macros to fit in! :o )

[ussenterprise]

So, here's what I did.
I add another row and added this macro:

Please Login or Register  to view this content.

And it works just fine.
I'll have to create individual macros apparently for each device type.
It looks as though the asynchronous nature of VBA is killing the macro.
Now to go back and edit my loops and single macros for the variables... (All day, I'm sure...)

[ussenterprise]

A co-worker has been working on something similar using Powershell along with VBA. He came up with this:

Please Login or Register  to view this content.

So far, this is working perfectly. I am now going to test this in a loop...

[ussenterprise]

SWEET!!!! I got it to work in a loop!!!!!!!!!!!!!!!!
I'll update my Tips post post haste... (post paste...)