"Bizzare" Sendkey Code in Excel  
Author Message
U m a n g





PostPosted: Visual Basic for Applications (VBA), "Bizzare" Sendkey Code in Excel Top

Following is my code...

Range("A1").Select 'Select cell A1
Application.SendKeys "BIZZARE{ENTER}", True

'Enters BIZZARE in A1 and focus goes to B2

Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "{UP}", True 'Again focus on A1
Application.Wait (Now + TimeValue("0:00:02"))

'Range("A1").Select 'Just in case {UP} doesnt work

Selection.Copy 'Path 1
Range("B2").Select 'Path 1
ActiveSheet.Paste 'Path 1

Application.SendKeys ("^C") 'Path 2
Range("B2").Select 'Path 2
Application.SendKeys ("^V") 'Path 2

My code wanted to write BIZZARE in cell A1 and then also copy that contents to cell B2.But when I run this code then only BIZZARE appears in cell B2 and not A1.I tried to do the copying using 2 different methods, Path 1 or Path 2. If i comment both Path1 and Path2 statements then BIZZARE appears in cell A1.

Can anyone explain why this is happening so.

PS: If i remove Path1 or Path2 statements then BIZZARE appears on cell A1 perfectly.




Microsoft ISV Community Center Forums2  
 
 
Cindy Meister





PostPosted: Visual Basic for Applications (VBA), "Bizzare" Sendkey Code in Excel Top


<shudder>Why are you trying to use SendKeys

This is only a last resort, when nothing else is available. And
certainly, you can accomplish what you need in Excel without using
SendKeys. Have you tried using the macro recorder

-- Cindy (Word MVP)


 
 
magicalclick





PostPosted: Visual Basic for Applications (VBA), &quot;Bizzare&quot; Sendkey Code in Excel Top

LOL, I didn't know I can use SendKeys as last resort.

Anyway, yeah, use record to learn most of avaliable functions, like Range("A1").FormulaC1L1 = "BAZZAR", then Simply Range("A1").Copy Range("B1").

Only few things can't be recorded, like arrow keys. They use Range().select instead of record arrow keys.


 
 
U m a n g





PostPosted: Visual Basic for Applications (VBA), &quot;Bizzare&quot; Sendkey Code in Excel Top

Cindy and Magicalclick,

I acknowledge that the methods that you have suggested are far better than the one I am using. I was trying to learn sendkeys yesterday and was succesful in getting "BIZZARE" in cell A1.Then I started a macro recorder and copied the contents to cell B2. Both when working individually are fine, but when I try to combine both of them it doesnt work.

Can you explain this Thanks in advance.

Also PS:

I had once written 2 macros for counting out the number of JPG in all subfolder and if check if any other file is present other than a JPG(like a BMP,which I could then manually save as a JPG and save space).The second macro was to check if these subfolders might have any folders in them(which had to be removed). I was unable to combine as they used the recursive "Dir()" function. Is it possible for a macro to send a signal to another macro with its "EXIT Code", I mean that it has successfully completed or had some error.The Wait() didnt work here.



 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), &quot;Bizzare&quot; Sendkey Code in Excel Top

There's no reason this shouldn't be possible -- the issue lies in the way your two subroutines are interacting. Here's a quick code sample that uses SendKeys to enter a value in cell A1, then copy and paste it to cell B2.

With ThisWorkbook.Worksheets("Sheet1")
.Activate
.Cells(1, 1).Select
SendKeys "{F2}TEST~", True
.Cells(1, 1).Copy
.Cells(2, 2).PasteSpecial Paste:=xlPasteValues
End With

In answer to your second question: yes, it is possible. Instead of defining your routines as Sub, define them as Function. For example, replace:

Private Sub DoAction(bTestValue As Boolean)
' I can't return a value!
End Sub

with:

Private Function DoAction(bTestValue As Boolean) As String
DoAction = IIf(bTestValue,"True","False")
End Function



 
 
magicalclick





PostPosted: Visual Basic for Applications (VBA), &quot;Bizzare&quot; Sendkey Code in Excel Top

Maybe it has something to do with your wait command. I am just guessing here because I never used wait in my application. Just didn't see the need for doing that. And I am not really confident on its behavior. Since SendKey already has the build-in wait, I think you don't need to use application wait.

I am not sure what happens when SendKey triggers another event. I am hopping that the build-in wait will suspend the method until the triggered event finishs, otherwise you are going to have major puzzle to solve.


 
 
magicalclick





PostPosted: Visual Basic for Applications (VBA), &quot;Bizzare&quot; Sendkey Code in Excel Top

Oh yeah, I see what you mean.

Application.SendKeys "BIZZARE{ENTER}", True
MsgBox "Macro1"

It did not type the letters nor showing the msgbox. I have no idea what happened.


 
 
U m a n g





PostPosted: Visual Basic for Applications (VBA), &quot;Bizzare&quot; Sendkey Code in Excel Top

Exactly Magicalclick that is even a short and simple description of my main problem.Thanks. I am giving your code with some modifications so the other people can figure out why this is happening.

Sub Macro1()
Range("A1").Select 'Select cell A1
Application.SendKeys "BIZZARE{ENTER}", True
End Sub

It will enter BIZZARE in Cell A1.That is good..(and the True as the second parameter should ensure that the sendkeys are send first then executed and then the next statement is executed.)But now see the following code(I have added one more line to the same code)

Sub Macro1()
Range("A1").Select 'Select cell A1
Application.SendKeys "BIZZARE{ENTER}", True
MsgBox "Macro1 Finished at Cell A1"
End Sub

Now comes the unexpected.When I execute this code then neither BIZZARE is entered in Cell A1 nor do I see the Msgbox which can intimate me that the work is finished. Also I hear around many Beeps on my computer.Thats it.

Anyone who can jump in and figure this BIZZARE code.



 
 
magicalclick





PostPosted: Visual Basic for Applications (VBA), &quot;Bizzare&quot; Sendkey Code in Excel Top

Actually you should try duck thing's code. It works. It looks like the "With" statement has certain features embeded, not just for cleaner code. Good luck.