VBA Run-time error ‘438’ - Unknown Reason

[MWSC18]

Hello,

Developing a code in Excel VBA for work (code at the end of this post) and I am running into the following error a few times in the code:

Run-time error ‘438’:
Object doesn’t support this property or method

This code (full code posted below at end) is executed in one workbook, opens another, does some actions, and then repeats two more times for two other workbooks. The error seems to happen when I interact with any other workbook I open as part of the code. For example, the error is first encountered when executing the bolded line below:

Dim WBName As String
WBName = "S:\Sales_Order_Tracking\Backlog\Sales Order Log - Peter.xlsm"
Workbooks.Open Filename:=WBName, ReadOnly:=True

The 438 error is thrown, I click “Ok,” the code is immediately exited/cancelled, but the file WBName does in fact open in read-only mode. Then, I immediately run it again while the file WBName is open, and it does NOT give me a 438 error when executing that line. However, it then has a problem with the following line, giving the same 438 error:

Set cRangePK = SOLPK.Range("A2", lastcell)

I also noticed in the line above Set cRangePK when it determines which cell address lastcell is, it’s grabbing that from the TBCData sheet and NOT the PKData sheet. It’s like the program does not even know that Sales Order Log – Peter exists or is open and isn’t interacting with it…

I am beyond confused because I use a nearly identical code in another project and it has worked flawlessly for years. The above code also worked in a simple proof of concept in some test files for this current project.
Test code below - works as intended:

Sub Workbook_Open()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.Cursor = xlWait
Application.DisplayStatusBar = True
Application.StatusBar = "Importing Medical Data..."

Dim medmaster, backlog As Workbook
Dim meddatash, backlogdatash As Worksheet
Dim cRangeMed As Range
Dim lastcell As String

Set backlog = Workbooks("BacklogPullTest.xlsm")
Set backlogdatash = backlog.Sheets("AllDataList")

'Opens the Medical Data File
Dim WBName As String

WBName = "S:\TESTING\A\B\BacklogMasterTest.xlsm"
Workbooks.Open Filename:=WBName, ReadOnly:=True

'Clears any existing data on Backlog Data Sheet
backlogdatash.Cells.ClearContents

Set medmaster = Workbooks("BacklogMasterTest.xlsm")

'Copies data from MedMaster to Backlog
Set meddatash = medmaster.Sheets("MedData")
meddatash.Activate
lastcell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
 Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Address
Set cRangeMed = Range("A1", lastcell)
backlogdatash.Range("A1", lastcell).Value = cRangeMed.Value

'Closes MedMaster without saving
medmaster.Close (False)

Application.Calculation = xlCalculationAutomatic
Application.Cursor = xlDefault
Application.StatusBar = False
Application.ScreenUpdating = True

backlog.RefreshAll 'Refreshes all sheets and data connections

End Sub

Now in the main project I am seeing problems when really all I did was change names of files and sheets. I’m not sure if there is some property in the Sales Order Log files that is set differently somehow that is causing the problem, but if so, I wouldn’t even know what to look for to know what that might be.

Thanks for any insight or resolutions!

Full code for current project:

Sub Update()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.Cursor = xlWait
Application.DisplayStatusBar = True
Application.StatusBar = "Importing Sales Order Log data..."

Dim SOLPK, SOLGF, SOLSS, TBC As Workbook
Dim PKData, TBCData As Worksheet
Dim cRangePK As Range
Dim WBName, lastcell As String

Set TBC = Workbooks("Total Backlog Compile.xlsm")
Set TBCData = TBC.Sheets("Backlog Data")

'Clears any existing data on Backlog Data Sheet
TBCData.Activate
If TBCData.Range("BD2") = "" Then GoTo NoData
lastcell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
 Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Address
TBCData.Range("A2", lastcell).ClearContents
'-----------------------------------------------------------------------------------------------------------------
NoData:
'Opens Sales Order Log - Peter
WBName = "S:\Sales_Order_Tracking\Backlog\Sales Order Log - Peter.xlsm"
Workbooks.Open Filename:=WBName, ReadOnly:=True
Set SOLPK = Workbooks("Sales Order Log - Peter.xlsm")

'Copies data from Sales Order Log - Peter to Total Backlog Compile
Set PKData = SOLPK.Sheets("Backlog_Template")

PKData.Activate
lastcell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
 Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Address
Set cRangePK = SOLPK.Range("A2", lastcell)
TBCData.Range("A2", lastcell).Value = cRangePK.Value

'Closes Sales Order Log - Peter without saving
SOLPK.Close (False)

Application.Calculation = xlCalculationAutomatic
Application.Cursor = xlDefault
Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

 

[Domenic]

With the following line of code . . .

Set cRangePK = SOLPK.Range("A2", lastcell)

. . . you're missing the sheet reference. By the way, with the following declaration...

Dim SOLPK, SOLGF, SOLSS, TBC As Workbook

. . . only TBC is declared as Workbook, your other variables are declared as Variant. To declare each one as Workbook...

Dim SOLPK As Workbook, SOLGF As Workbook, SOLSS As Workbook, TBC As Workbook

And the same thing for your other similar lines of declarations.

Hope this helps!

 

[rlv01]

I tried this

Dim WBName As String
WBName = "S:\Sales_Order_Tracking\Backlog\Sales Order Log - Peter.xlsm"
Workbooks.Open Filename:=WBName, ReadOnly:=True

and did not have any issues

You might try this variation to see if you can get more info about the problem

    Dim WB As Workbook
    Dim WBName As String
    
    WBName = "S:\Sales_Order_Tracking\Backlog\Sales Order Log - Peter.xlsm"

    On Error Resume Next
    Set WB = Workbooks.Open(Filename:=WBName, ReadOnly:=True)
    On Error GoTo 0
    
    If WB Is Nothing Then
        MsgBox "There was a problem opening the workbook"
        Exit Sub
    End If