Object does not support this property or method VBA error

Question

[New question split by moderator from this old Q&A]

Hello, I have the same problem. I have a co-worker creating macro file on a PC which works on his end, but when he sends it to me, I get an error message everytime (I'm on a Mac with Excel X version).

The error says this :

---

"Run time error '438':

Object does not support this property or method

---

Here's the macros script:

[code]

Sub FetchData()

'

' Macro1 Macro

'

'

    Dim myRow As Long

    Dim myStr As String

    myStr = "http://ycharts.com/companies/" & Range("B1") & "/historical_data/gross_profit_margin?start_month=" & Month(Range("B2")) & _

            "&start_day=" & Day(Range("B2")) & "&start_year=" & Year(Range("B2")) & "&end_month=" & Month(Range("B3")) & "&end_day=" _

            & Day(Range("B3")) & "&end_year=" & Year(Range("B3"))

    Application.ScreenUpdating = False

    With ActiveSheet.QueryTables.Add(Connection:= _

        "URL;" & myStr _

        , Destination:=Range("$A$9"))

        .Name = _

        "gross_profit_margin?start_month=3&start_day=1&start_year=2003&end_month=5&end_day=1&end_year=2007_1"

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = False

        .RefreshPeriod = 0

        .WebSelectionType = xlEntirePage

        .WebFormatting = xlWebFormattingNone

        .WebPreFormattedTextToColumns = True

        .WebConsecutiveDelimitersAsOne = True

        .WebSingleBlockTextImport = False

        .WebDisableDateRecognition = False

        .WebDisableRedirections = False

        .Refresh BackgroundQuery:=False

    End With

    myRow = WorksheetFunction.Match("Pro Data Export", Range("A:A"), 0)

    Range("A7:XFD" & myRow).Select

    Selection.Delete Shift:=xlUp

    myRow = WorksheetFunction.Match("Get Historical Data ", Range("A:A"), 0)

    Range("A" & myRow & ":XFD1048576").Select

    Selection.Delete Shift:=xlUp

    Range("A5") = "Historical Gross Profit Margin Data"

    Range("A6").Select

    ActiveWorkbook.Save

    Application.ScreenUpdating = True

End Sub

Sub Clear()

'

' Macro2 Macro

'

'

    Application.ScreenUpdating = False

    Range("A5:XFD1048576").Select

    Selection.Delete Shift:=xlUp

    Range("A6").Select

    ActiveWorkbook.Save

    Application.ScreenUpdating = True

End Sub

[/code]

Answer 1

It's not the same problem at all... (which is why this was moved to a new thread.

Even though there is substantial compatibility between versions of XL, there are still differences. In this case, a quick glance at the Object Browser reveals that the code is attempting to set QueryTable properties that are Win-XL only.

Replace the WIth..End With block with this:

With ActiveSheet.QueryTables.Add(Connection:= _

"URL;" & myStr _

, Destination:=Range("$A$9"))

.Name = _

"gross_profit_margin?start_month=3&start_day=1&start_year=2003&end_month=5&end_day=1&end_year=2007_1"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

' .PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = False

'.RefreshPeriod = 0

'.WebSelectionType = xlEntirePage'

' .WebFormatting = xlWebFormattingNone

'.WebPreFormattedTextToColumns = True

' .WebConsecutiveDelimitersAsOne = True

'.WebSingleBlockTextImport = False

' .WebDisableDateRecognition = False

'.WebDisableRedirections = False

.Refresh BackgroundQuery:=False

End With