CODEWITHSUNDEEP
macosvbaexcel
mynameisJEFF
mynameisJEFF

Reputation: 4239

Import tables from internet failed (Mac)

Basically I have the following code, which I produced on a PC desktop using Excel 2010. And it worked just fine. However, when I transferred the file to Mac and executed the getStockDataTest() subroutine using Excel 2011, it failed miserably.

I kept getting this error message

enter image description here

Option Explicit
Sub getStockDataTest()
    getGoogleStockHistory 700

End Sub


Sub getGoogleStockHistory(gInt As Long)
'
' Macro1 Macro
'load google stock hisotry

'
    '.add -> create object
    'With ActiveSheet.QueryTables.Add(Connection:= _
        '"URL;https://www.google.com.hk/finance/historical?q=HKG%3A000&ei=V5k-U4CjHtDakQWfQw#" _
        , Destination:=Range("$A$1"))

    With ThisWorkbook.Sheets("Query").QueryTables(1)
       .Connection = "URL;https://www.google.com.hk/finance/historical?q=HKG%3A" & Format(gInt, "0000") & "&num=200" '&num 200 = 200 days of data
        .Name = "WebQuery"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables   'xlEntirePage      will get entire webpage
        .WebTables = "4"
        .WebFormatting = xlWebFormattingNone
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableRedirections = False
    End With
    Range("A1").Select
End Sub

Furthermore, I want to ask how to do Import from web in Mac Excel 2011, because I cannot find such icon. Please help me out here, as I am quite lost here.

Upvotes: 0

Views: 630

Answers (2)

Manuel Allenspach
Manuel Allenspach

Reputation: 12745

This site says (see number 9 in the list) that PreserveFormatting doesn't exist on Mac and RefreshPeriod may be different...

Deleting these 2 statements may solve your problem.

Upvotes: 2

Adach1979
Adach1979

Reputation: 278

The only recommendation I have is to remove the inline comments as it might be part of the issue. Here is a link to a similar question on how to create a web query on a Mac.

From what I can gather from a couple of Google queries, VBA within Excel 2011 Mac is nearly identical to the VBA on PC so there shouldn't be an issue. Several forums I frequent recommend this site to understand the differences that still exist.

Upvotes: 0

Related Questions