VBA Code not working since upgrading to MSOffice 2016

[PaulPerger]

I have several dozen pieces of VBA code that are used to process information by automatically entering data stored in an excel workbook into an Inventory System (IPMS).

More specifically, the Code is written in Word, where I provide some detail in Word (File Group # and which Excel document to get information from) and then RUN the code from Word. It then Opens the IPMS system to the appropriate menu, loads the data input screen and proceeds to grab data from the selected Excel file and input that data into the IPMS Inventory system. I also grab data from the screen in IPMS and input that into the Excel document so I know which lines of data were successfully entered, etc.

This process has worked VERY well for over a decade, but I was recently forced to upgrade to Office 2016 and now none of my VBA code that calls excel works properly. Commands are still sent to IPMS properly, but doing anything in Excel no longer works. Below is some code from one of my pieces of VBA code. For simplicity of identification, VBA Code is in BLUE text.

Same_Directory = xl.Application.ActiveWorkbook.Path - This command does not work at all. It does not return an error, "Same_Directory" is just left defined as "".

' Open Destruction Request Log and Find EOF Cell
xl.Application.Workbooks.Open (Same_Directory & "\Destruction Request Log.xls")
xl.Application.Range("G2").Select

' Switch to Current Destruction and Copy Info for Tally Sheet
xl.Application.Windows(CurrentDestruction).Activate
xl.Application.Range("AG1").Select
xl.Application.Cells(1, 33) = "=J5"
xl.Application.Cells(2, 33) = "EOF123"
xl.Application.Range("AG1").Select
xl.Application.Selection.Copy

None of this code works as it once did. "xl.Application." seems to no longer be a valid piece of code. I am hoping there is a simple piece of code to replace this as a "Find & Replace" would be a super easy fix.

Any help is MUCH appreciated!!
Thanks

[SamT]

What is xl.Application?
Either xl is defined as an Excel Application or it's not.

If it is. then I think all you need is

xl.ActiveWorkbook.path
or
With xl
X = .ActiveWorkbook.Path
Note. I don't use Excel > 2003, so...

It's really hard to say, because the problem is not where you think it is. We will need to see the entire code leading up to where the problem manifests, or you can upload the Word Doc by going "Advanced" and using "Manage attachments"

[PaulPerger]

It had been so long since I wrote these that I had forgotten that I had defined xl as the Excel Application. So, I have been looking at that line of code and can't find any reason why it suddenly doesn't work... Following is all of the code that last week worked just fine, and has for probably 10 years, but no longer does... Hopefully this helps...


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.

[SamT]

xl is a Worksheet.
xl.Application returns Excel itself, the Application the Workbook belongs to.
Therefore
xl.Application.Cells(x, 1) = Excel.Cells(x, 1); Not a proper syntax, your older versions might have been generous and accepted it, but still...

May I suggest that you explicitly declare and set the Various Excel Objects you must deal with.

Note that this code may require a bit of tweaking after studying the Document it resides in and the Excel Object it is working on

'Late Binding
Dim xlApp As Object
Dim xlBook as As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks(t1) 'Don't know how this book got opened
Set xlSheet = xlBook.Sheets(t2) 'Don't know if this sheet exists in Workbooks(t1)

'Then, in your code use the appropriate Object variable
With xlSheet
For x = begin.Value To finish.Value
.Cells(x, 1) = GetIP(ip, 3, 41, 69)
.Cells(x, 2) = GetIP(ip, 3, 74, 79)
.Cells(x, 3) = GetIP(ip, 3, 40, 64)
.Cells(x, 4) = GetIP(ip, 9, 18, 32)

SendIP ip, "<enter>"
.Cells(x, 5) = GetIP(ip, 21, 22, 24)

SendIP ip, "<F7>"
SendIP ip, "<pgdn>"

If GetIP(ip, 5, 18, 19) <> 68 Then x = 10000 'Exit For?
Next x