Import tables from internet failed (Mac)

Question

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.

comment out the .refresh line, it should work without an error then. And when you are in the vba-editor go to tools > references and make sure all the libraries that you need are activated. I have been running in a similar problem :) — Kathara, Jan 28 '16 at 10:07

score 2 · Answer 1 · answered Apr 29 '14 at 06:42

2

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.

answered Apr 29 '14 at 06:42

Manuel Allenspach

12,467
14
54
76

2

Both answers from Adach1979 and Manu reduce to the same great advice: _Simplify in order to trouble shoot._ The error message isn't helpful, true, but misery is a choice :-) ... Simplify till you get something that works, then build back up till you have what you need. – Smandoli Apr 29 '14 at 13:29

score 0 · Answer 2 · edited May 23 '17 at 12:16

0

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.

edited May 23 '17 at 12:16

Community

1
1

answered Apr 23 '14 at 12:13

Adach1979

278
2
8

The problem is that I am using a for loop to load internet stock data. It would difficult for me to create many txt files. – mynameisJEFF Apr 23 '14 at 12:43
1

I agree that one might as well remove the inline comments -- when trouble shooting, try anything and everything. THAT SAID ... I don't imagine that is the problem. – Smandoli Apr 29 '14 at 13:26

Import tables from internet failed (Mac)

2 Answers2