VBA Error in Excel 2016 but works in 2013

Disguy · Feb 28, 2018

Moved an excel spreadsheet which scrapes data off of a website from a PC with excel 2013 to new PC with excel 2016. Error occurs at " .Refresh BackgroundQuery:=False" (code snip below)

I used the help in excel 2016 and it seems to referrence this as .Refresh ([BackgroundQuery]) but no combination of spacing or other adjustment creates a functional or executable script.

It is my assumption that either something changed between versions or i need to enable some feature in excel. Any help would be appreciated.

Code:

 Dim objWeb As QueryTable

 With objWeb
         
        .WebSelectionType = xlSpecifiedTables
        .WebTables = sWebTable
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With

cerfani · Feb 28, 2018

what does the error say?

Disguy · Feb 28, 2018

Full code with the exclusion of website data.

Code:

Sub gethtmltable()


'Insert that into the beginning
    Application.ScreenUpdating = False
    Dim objWeb As QueryTable
    Dim sWebTable As String
    Dim strStartDate As String
    Dim strEndDate As String
    Dim strUrl As String
    
    Sheets("Data").Select
    'Application.Visible = False'


    strStartDate = Format(Date, "yyyy-MM-dd")
    strEndDate = Format(Date, "yyyy-MM-dd")
    
    
    strUrl = "URL;http://data.com"
       

    'This will delete the previous data
    Range(Cells(1, 1), Cells(150, 6)).Delete
    
    
    'You have to count down the tables on the URL listed in your query
    'This example shows how to retrieve the 2nd table from the web page.
    sWebTable = 2
     'Sets the url to run the query and the destination in the excel file
     'You can change both to suit your needs
    Set objWeb = ActiveSheet.QueryTables.Add( _
    Connection:=strUrl, _
    Destination:=Range("$A$1"))
     
    With objWeb
         
        .WebSelectionType = xlSpecifiedTables
        .WebTables = sWebTable
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With
    
    Cells(1, 7) = Cells(LastRow() - 1, 5)
    Set objWeb = Nothing

Disguy · Feb 28, 2018

cerfani said:
what does the error say?

Runtime Error '1004', unable to open.
http://data.com (not actual address)
Cannot download the information you requested.

End, Debug or help are selectable options.

I can move the spreadsheet on to my laptop and it executes without error.

cerfani · Feb 28, 2018

what about just calling .Refresh with no parameter? the msdn doesnt mention any kind of difference between versions although the info might not be complete, not sure how up to date msdn is.

Disguy · Feb 28, 2018

cerfani said:
what about just calling .Refresh with no parameter? the msdn doesnt mention any kind of difference between versions although the info might not be complete, not sure how up to date msdn is.

Without the "BackgroundQuery:=False" it does not stop the refresh and excel throws an error stating so.

Disguy · Feb 28, 2018

In an update to the thread. The issue was not with Excel but actually Window's security on that machine was preventing the messages from going through.

[FONT=&quot]The machine’s OS is server 2012. In Server Manager, under “Local Server” . Be sure to turn off IE Enchanced Security Configuration. It’s on the right, sixth item down. This solved my issue.[/FONT]

VBA Error in Excel 2016 but works in 2013

Disguy

New Member

Excel Facts

cerfani

Well-known Member

Disguy

New Member

Disguy

New Member

cerfani

Well-known Member

Disguy

New Member

Disguy

New Member

Similar threads

Forum statistics

Share this page

VBA Error in Excel 2016 but works in 2013

Disguy

New Member

Excel Facts

cerfani

Well-known Member

Disguy

New Member

Disguy

New Member

cerfani

Well-known Member

Disguy

New Member

Disguy

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock