Private Sub DoIt_Click()
' Connect with Excel and establish communication paths
Set xl = CreateObject("Excel.sheet")
t = Topics.Text
t1 = Mid(t, 2, InStr(t, "]") - 2)
t2 = Right(t, Len(t) - InStr(t, "]"))
xl.Application.Windows(t1).Activate
xl.Application.Sheets(t2).Select
ip = Application.DDEInitiate(App:="PTW", Topic:="PSL")
' Check Status of IPMS Connection & Menu Readiness
If GetIP(ip, 2, 6, 9) <> "MENU" Then MsgBox "Ip Error 3&1": Stop
' Access IPMS Menu 501, Option 1 for SKU information lookup
SendIP ip, "89<enter>"
SendIP ip, "1<enter>"
SendIP ip, "1<enter>"
SendIP ip, "68000000000000000000<enter>"
SendIP ip, "<pgdn><enter>"
8
' To use a list of SKUs from a Spreadsheet, use the following loop section.
' Begin loop
For x = begin.Value To finish.Value
xl.Application.Cells(x, 1) = GetIP(ip, 3, 41, 69)
xl.Application.Cells(x, 2) = GetIP(ip, 3, 74, 79)
xl.Application.Cells(x, 3) = GetIP(ip, 3, 40, 64)
xl.Application.Cells(x, 4) = GetIP(ip, 9, 18, 32)
SendIP ip, "<enter>"
xl.Application.Cells(x, 5) = GetIP(ip, 21, 22, 24)
SendIP ip, "<F7>"
SendIP ip, "<pgdn>"
If GetIP(ip, 5, 18, 19) <> 68 Then x = 10000
Next x
' loop done. ip back to the menu
SendIP ip, "<f12>"
SendIP ip, "<f3>"
DDETerminateAll
End Sub
I am pretty confident that my entire issue is the "connection" between VBA and Excel. I have similar macros that are simply doing entry in the IPMS system referenced above, and that system is working fine. All of the above commands and more are working just fine, it is only the connection between the running VBA macro written in a Word document accessing an Excel sheet that seems to be the problem.