CODEWITHSUNDEEP
vbaexcel
mrbusto71
mrbusto71

Reputation: 47

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

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.

Upvotes: 0

Views: 7492

Answers (1)

user6432984
user6432984

Reputation:

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

Upvotes: 1

Related Questions