Help using VBA to control external program

[astr0zombi3s]

Hello Excel Wizards,

I'm working on a project for my company that requires me to control an external program from within excel.

I'm able to use the call shell command to open the program but the problem I'm running into is that there are 2 fields I need to populate within the external program from data within my worksheet. Once those two fields are populated I need to run the function of the program and return data from a 3rd field on the program.

Through reading on forums it looks like I might be able to accomplish some of this using the sendkeys functionality.

In order to do that though it appears I need to define the object and tell excel where to actually put the data in. This is where I'm a smig out of my depth of experience as I've never really written anything through VBA.

Is this the best way to go about this? Define object then use sendkey?

Any advice or guidance is much appreciated, I'm not exactly sure what information I can provide to you to make this more helpful in order to illicit a response. Please let me know and I'll provide whatever I can.

Thanks,
AstroZombi3s

 

[astr0zombi3s]

Hello Excel Wizards,

I'm working on a project for my company that requires me to control an external program from within excel.

I'm able to use the call shell command to open the program but the problem I'm running into is that there are 2 fields I need to populate within the external program from data within my worksheet. Once those two fields are populated I need to run the function of the program and return data from a 3rd field on the program.

Through reading on forums it looks like I might be able to accomplish some of this using the sendkeys functionality.

In order to do that though it appears I need to define the object and tell excel where to actually put the data in. This is where I'm a smig out of my depth of experience as I've never really written anything through VBA.

Is this the best way to go about this? Define object then use sendkey?

Any advice or guidance is much appreciated, I'm not exactly sure what information I can provide to you to make this more helpful in order to illicit a response. Please let me know and I'll provide whatever I can.

Thanks,
AstroZombi3s

Additional clarification:

I'm using excel 2007
I want to send data from E2 and G2 to the 2 fields on the program which we'll call the matrix and return the value the matrix produces based on those numbers.

 

[NdNoviceHlp]

So what type of application is the program called "matrix"? An .exe? Fields in where...pdf, word, note, XL, else? Maybe a bit more info. will get you help. Dave

 

[astr0zombi3s]

So what type of application is the program called "matrix"? An .exe? Fields in where...pdf, word, note, XL, else? Maybe a bit more info. will get you help. Dave

Thank you for your response and suggestion.

The matrix is an .exe file the fields that need to populate the matrix with are in excel. All information is flowing back and forth between the matrix and the excel workbook and that is it.

What other information could I provide to help illluminate?

 

[NdNoviceHlp]

So from XL, you are running some .exe that grabs some input data from one XL file, does some calculations and then outputs to a different XL file. Where's the problem? Do you not have access to the .exe code? What does the.exe code do that can't be done with XL VBA? Dave

 

[astr0zombi3s]

So from XL, you are running some .exe that grabs some input data from one XL file, does some calculations and then outputs to a different XL file. Where's the problem? Do you not have access to the .exe code? What does the.exe code do that can't be done with XL VBA? Dave

That is correct, from XL i'm running a .exe that grabs some imput data from one XL page of the workbook, the program does some calculations and then returns a value from a database that I do not have access to. I want that data that is pulled into the program to then be returned to the XL file into another column.

Unfortunately I do not have access to the code of the .exe file. The program pulls data from our database (which we also cannot access directly except through this program) the program takes a set of customer information and returns a price by item for our clients (because it is dealing with product pricing and margins, not many people can actually have access to this information). To manually use the program is very time intensive so I'm hoping to eliminate that by having a macro that will take the two fields of data needed and return the value 1 at a time with a 20 second timeout inbetween operations.

 

[NdNoviceHlp]

I'm not sure that what you're doing is legal. If you had access to the .exe then it would be fairly straight forward. Send keys (yuck) is maybe the only option unless there some output that XL can access? Here's some module code that may help. Call the getposition to get the current mouse location. Then call the setposition to return the mouse to that location. Then call leftclick. It may help. I think this is DK or Jaafar's code. Maybe it'll help. Good luck. Dave

Private Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Private Declare Function GetCursorPos Lib "user32" _
                                      (lpPoint As POINTAPI) As Long
Type POINTAPI
    x As Long
    y As Long
End Type
Public z As POINTAPI
Private Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, _
ByVal dX As Long, _
ByVal dY As Long, _
ByVal dwData As Long, _
ByVal dwExtraInfo As Long)
Private Declare Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As Long)
' Flags used with mouse_event
Private Const MOUSEEVENTF_ABSOLUTE = &H8000& ' absolute move
Private Const MOUSEEVENTF_LEFTDOWN = &H2     ' left button down
Private Const MOUSEEVENTF_LEFTUP = &H4       ' left button up
Private Const MOUSEEVENTF_MIDDLEDOWN = &H20  ' middle button down
Private Const MOUSEEVENTF_MIDDLEUP = &H40    ' middle button up
Private Const MOUSEEVENTF_MOVE = &H1         ' mouse move
Private Const MOUSEEVENTF_RIGHTDOWN = &H8    ' right button down
Private Const MOUSEEVENTF_RIGHTUP = &H10     ' right button up
Private Const MOUSEEVENTF_WHEEL = &H800      ' wheel button rolled
Public Sub leftClick()
  ' Click the mouse, with delay to simulate human timing.
  mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
  Sleep 20
  mouse_event MOUSEEVENTF_LEFTUP, 0, xpt, 0, ypt
End Sub
Sub GetMousePosition()
    GetCursorPos z
End Sub
Sub SetMousePosition()
If x < 0 Or x > 1024 Or y < 0 Or y > 768 Then Exit Sub
    SetCursorPos z.x, z.y
End Sub