VBA to make key entries into application

[KENNY]

Hello,

I'd love to be able to take data from an Excel worksheet
and place it ("key")into another application. For example:

Column A Column B Column C
Name Address Phone

Bob 123 Main 612-123-4567
Joe 456 Elm 555-123-9876



The mechanics would be enter A2 data, go to new field in
destination application, enter B2 data, go to new field,
enter C2 data, click SAVE in destination application,
click NEW, and start process over for row 3 (Joe)....

I know some VBA, and a little about .bat files. Any help
getting me started would be greatly appreciated

TIA!

 

[NickHK]

KENNY,
What is the "destination application" ?

NickHK

 

[Harald Staff]

Hi Kenny

Here's a way of typing:

Sub test()
Dim R As Long
Dim C As Long
Dim V As Long
V = Shell("C:\WINDOWS\NOTEPAD.EXE", 1)
DoEvents
Application.Wait Now + TimeSerial(0, 0, 1)
For R = 1 To 2
For C = 1 To 3
Application.SendKeys Cells(R, C).Value
Application.SendKeys "{TAB}"
Next
Application.SendKeys "{RETURN}"
Next
End Sub

Note that this is /not/ a good way to automate one application from another.
Sendkeys is like typing blindfolded and can be used reliably only in
strictly controlled environments.

Can this "another application" import files ? Looks like some database
thing, it should handle an import from Excel or from a textfile.

HTH. Best wishes Harald

 

[KENNY]

It is a propietary (still in development) office
management system (web based). I think the steps are Copy
from a cell in Excel, go to destination app, paste, and so
on ..... back and forth.

Thanks.

 

[KENNY]

Thanks for the response.... Unfortunately, no -- it can't
be imported (yet).

 

[Tim Williams]

If it's a web application then you may be able to automate IE from Excel.

There have been a couple of threads along these lines recently in this
group. try Googling the newsgroup archives.

Some simple code:
'***************************************
Option Explicit

Dim IE As InternetExplorer

'create an instance of IE and nevigate to a page.
Sub CreateAndLoad()

If IE Is Nothing Then
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
End If

IE.navigate "http://www.yahoo.com"

'wait while the page loads
Do While Not IE.readyState = READYSTATE_COMPLETE
Loop

End Sub

'enter some info into a form element
Sub EnterInfo()
IE.document.s.p.Value = "Entered from Excel"
End Sub





Tim.

 

[Tim Williams]

More....

for most applications you can use "getObject()" to return a running
instance. Not possible with IE so here's a way to do the same thing.
In one way it's an improvement on GetObject since you can select which
instance you want to work with, based on the URL of the currently-loaded
page.

Tim.



Sub test2()
Dim o

Set o = GetIE("http://www.yahoo.com")

If Not o Is Nothing Then
MsgBox o.document.body.innerHTML
Else
MsgBox "No Yahoo page found"
End If

End Sub


Function GetIE(sLocation As String) As Object

Dim objShell As Object, objShellWindows As Object, o As Object
Dim sURL As String
Dim retVal As Object

Set retVal = Nothing
Set objShell = CreateObject("Shell.Application")
Set objShellWindows = objShell.Windows

For Each o In objShellWindows
sURL = ""
On Error Resume Next
'check the URL and if it's the one you want then
' assign it to the return value
sURL = o.document.Location
On Error GoTo 0
If sURL Like sLocation & "*" Then
Set retVal = o
Exit For
End If
Next o

Set GetIE = retVal

End Function