Macro to Paste Data in Non-Office Program

[Guest]

I am fairly new to VBS, but dabble as much as I am able by addapting other
people's solutions. I need to learn how to do the following:

I want a macro to:
Select an address in Excel (let's say, "Sheet1 A1"),
Open another Microsoft program (i.e. "C:\Program.exe");
The program opens with the curser in the correct box automatically;
I want to then paste the data from Excel into the already selected box.

I am working with something like this:

Sub Macro1 ()
ThisWorkbook.Worksheets("sheet1").Range("a1").Copy
Shell "c:\program.exe", vbNormalFocus
Selection.PasteSpecial
end sub

The problem is that I don't know how to make the paste work in the new
program.

Vista,
Excel 07,
The non-office program is Microsoft Streets.

Glenn

 

[Bill Renaud]

See the "SendKeys Statement" in Visual Basic Help.

SendKeys "^V", True

....should send the Ctrl+c to the application, causing it to "paste from
clipboard".

 

[Guest]

Thank you. After spending some time playing with Sendkeys, I got this
working code: [I know, the code is sloppy, but it works.]

Sub GetMap2()

'Goto the correct sheet.
Sheets("maps").Select

'Get the address from the Excel sheet.
ActiveSheet.Range("n3").Copy

'Select the cell where the map will be pasted.
Range("a5").Select

'Open the map program.
Shell "c:\program files\microsoft streets & trips\streets.exe",
vbNormalFocus

'Delay so the VBA program does not go on ahead while the map program opens.
WaitTime1 = Now + TimeValue("0:00:14")
Application.Wait WaitTime1

'Paste the address, then copy the map into the clipboard.
'The wait times prevent the VBA from moving faster than the map program.
Application.SendKeys "^v", False
Application.SendKeys "~", False

WaitTime1 = Now + TimeValue("0:00:03")
Application.Wait WaitTime1

Application.SendKeys "%e", False
Application.SendKeys "m", False

WaitTime1 = Now + TimeValue("0:00:03")
Application.Wait WaitTime1

'Close the map program.
Application.SendKeys "%f", False
Application.SendKeys "x", False
Application.SendKeys "n", False

'Paste the map in Excel
ActiveWorkbook.ActiveSheet.Range("a5").PasteSpecial

'Go back to the cover sheet in the workbook.
Sheets("initial project worksheet").Select

End Sub

Glenn

 

[Bill Renaud]

One final observation; both instances of Selecting a worksheet:

Sheets("maps").Select
and
Sheets("initial project worksheet").Select

should probably be (for example):

Sheets("maps").Activate

In general, you Activate Sheets and individual Cells, you Select Ranges
(multiple cells). Sometimes, using Select instead of Activate can cause
problems or run-time errors. (I think that the macro recorder does not
always record code the way it really should be.)

Example (step through this):

Range("A1:C3").Select
Range("B2").Activate

See the result?

 

[Bill Renaud]

Forgot to also mention that you probably should check the return value of
the Shell function. See the example in Help.

You might also want to consider using the "AppActivate Statement" after the
Shell statement.

 

[Guest]

Thank you for the input.

I am afraid that Microsoft’s help files are about as useful to me as a
doctoral thesis on differential calculus is to a first grader. All I know
about VBA is what I have gleaned from here.

Glenn

 

[Bill Renaud]

<<All I know about VBA is what I have gleaned from here.>>

FYI: It is hard to learn VBA programming from this newsgroup alone. Wander
through some of the MVP web sites (see
http://www.mvps.org/links.html#Excel) for tips and code samples.

For some book references, see one of my replies to the thread "How do I
create a picklist macro?" started on 10/31/07 4:02 PM by Bsweat6.