Run-time error 438: Object doesn't support this property or method (VBA)

Question

This is driving me absolutely insane. I'm new to VBA and I compiled code line by line, adding more and more verifying it all worked within the same workbook using F8. The last bit I have to add is just opening a separate workbook, and now it's giving me errors each time. Here's my code:

Sub MasterXfer()
Dim mystring As String, wbName As String, dt As String, sdt As String, ldt As String
Dim wb1 As Workbook, wb2 As Workbook, mypath As String

wbNam = "Productivity "
dt = Sheet1.Range("B1").Value
sdt = Format(CStr(dt), "m.d.yy") & ".xlsx"
ldt = Format(CStr(dt), "yyyy") & "\" & Format(CStr(dt), "mm") & "_" & MonthName(Month(dt)) & "_" & Year(dt)

mypath = "S:\" & ldt & "\" & wbNam & sdt

Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(mypath) 'HERE'S WHERE IT ERRORS OUT

With wb1
lastrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
For x = 2 To lastrow Step 16
mystring = .Range("A" & x)

Stepping through this, it works fine. Then I get to the Set wb2 = Workbooks.Open line, and it successfully opens the target workbook, however immediately upon opening it the code stops and the error in question comes up.

If anyone at all can tell me what mistake I'm making I will name my firstborn after you.

Also, for the record, this isn't all of the code, just a bit past where the error is occurring. — mrbusto71
– mrbusto71, Commented Oct 19, 2016 at 21:33
What does debug.print mypath show at that point (look in the Immediate window with [ctrl]+G)? Why use a 'helper' function like MonthName when you can just use Format(date, "mmm") or Format(date, "mmmm") ? I seem to remember problems with workbook names containing periods; can you run a test without them? — user4039065
– user4039065, Commented Oct 19, 2016 at 21:35
lastrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row here the Worksheets(1) is not tied to wb1 since you're missing the leading period. Unless you meant it that way. — Tim Williams
– Tim Williams, Commented Oct 19, 2016 at 21:36
Heya @Jeeped! Do I type the debug.print mypath IN the immediate window, or is it supposed to go somewhere in the code? The short answer to the why question is simply that I'm fairly new to this and learning best practices as I go. Lastly, this is for my company and the destination workbooks are all formatted with periods for each week of the year, otherwise I'd name 'em something else. @Tim Williams would that cause the error in question? I see the hole in my logic and I'm gonna change it, just wondering if that might be the problem. — mrbusto71
– mrbusto71, Commented Oct 19, 2016 at 21:48
Under the Set wb2 = ... line, enter Debug.print mypath. Copy the line from the immediate window and paste it into the address bar of a Windows Explorer window. This will validate is the path you are building is to a file that exists. IF it fails to open copy the two paths (yours and the actual one) into notepad to see what might be missing. — Gary Evans
– Gary Evans, Commented Oct 19, 2016 at 22:22

score 1 · Accepted Answer · 2016-10-20 02:14:05Z

1

Your error if caused by this line mystring = .Range("A" & x). Workbook does not have a Range method. You need to change it to wb1.Worksheets(1).

You should also test if the file exists before opening it.

I included an alternate method of creating your file string using the backslash to escape characters in the Format functions Format parameter.

Sub MasterXfer()
    Dim wb2 As Workbook
    Dim mypath As String

    mypath = Format(Sheet1.Range("B1").Value, "\S:\\YYYY\\MM_MMMM_YYYY\\Pro\du\ctivit\y MM.DD.YY.xl\sx")

    If Len(Dir(mypath)) = 0 Then
        MsgBox "File not found" & vbCrLf & mypath
        Stop
        Exit Sub
    End If

    Set wb2 = Workbooks.Open(mypath)

    With ThisWorkbook.Worksheets(1)

        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For x = 2 To LastRow Step 16
            mystring = .Range("A" & x)
        Next

    End With

End Sub

Collectives™ on Stack Overflow

Run-time error 438: Object doesn't support this property or method (VBA)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related