Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Paste Data in Non-Office Program
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Paste Data in Non-Office Program
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". -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Paste Data in Non-Office Program
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").PasteSpecia l 'Go back to the cover sheet in the workbook. Sheets("initial project worksheet").Select End Sub Glenn "Bill Renaud" wrote: 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". -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Paste Data in Non-Office Program
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? -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Paste Data in Non-Office Program
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. -- Regards, Bill Renaud |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Paste Data in Non-Office Program
Thank you for the input.
I am afraid that Microsofts 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" wrote: 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. -- Regards, Bill Renaud |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Paste Data in Non-Office Program
<<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. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
microsoft office excel cannot paste the data | Excel Worksheet Functions | |||
eula pops up every time i open an office program. office 2003 ins. | Excel Discussion (Misc queries) | |||
How can I get Office to activate a command in a non Office program | Excel Discussion (Misc queries) | |||
How do I set the default e-mail program for MS Office XP? | Setting up and Configuration of Excel | |||
Microsoft Office Excel cannot paste the data | Excel Discussion (Misc queries) |