Refresh backgroundquery false ошибка

• Remove From My Forums

• Question

• This might be a common problem and I found another topic that relates to mine but it isn’t quiet the same and I can’t find a solution to my problem by using it’s solution.

  I have a VBA script for getting data from a HTML table to a Excel file. At the HTML page the VBA script first logges in with a name and password written in the Excel file and given with the GET method.

  The HTML checks if the user is already logged in (session variables) and if so it gets the data from the table to Excel. If not the user is logged in and then the data is send.

  When I run the script it returns a 1004 error. This error is given at «.Refresh BackgroundQuery:=False».

  From using Google I understand that a 1004 error is given when the browser returns an errorcode that VBA doesn’t know.

  When I open the page (In both cases (with the username and password in the URL and already logged in without this)) in any browser (Safari, Firefox, Internet Explorer) the page doesn’t return any error nor report any problems with the page.

  My code:

  Sub GetData() 'Private Sub Workbook_Open() Private Sub Worksheet_Activate()<br/>
    Dim usr As String    'Username<br/>
    Dim pwd As String    'Password<br/>
    Dim url As String    'URL with username and password --> GET<br/>
    Dim baseUrl As String  'URL of page<br/>
    Dim connUrl As String  'URL for connection to HTML<br/>
    <br/>
    usr = ActiveSheet.Range("A1").Value<br/>
    pwd = ActiveSheet.Range("B1").Value<br/>
    <br/>
    baseUrl = "http://xxx/xx/xx/xx/xx/xx/xx.aspx"<br/>
    url = baseUrl & "?usr=" & usr & "&pwd=" & pwd<br/>
    connUrl = "URL;" & baseUrl<br/>
  <br/>
    'open Internet Explorer<br/>
    Set ie = CreateObject("InternetExplorer.Application")<br/>
  <br/>
    'go to .Navigate and show this<br/>
    With ie<br/>
      .Visible = True<br/>
      .Navigate url<br/>
  <br/>
      'do this until the page is fully loaded<br/>
      Do While ie.busy And Not ie.readystate = 4<br/>
        DoEvents<br/>
      Loop<br/>
      <br/>
      'create a Web Query in Sheet1 with connURL beginning from cell A5<br/>
      With ActiveSheet.QueryTables.Add(Connection:=connUrl, Destination:=Range("A4"))<br/>
        .Name = "stockListQuery"<br/>
        .RowNumbers = False<br/>
        .FillAdjacentFormulas = False<br/>
        .PreserveFormatting = True<br/>
        .RefreshOnFileOpen = False<br/>
        .BackgroundQuery = False<br/>
        .RefreshStyle = xlOverwriteCells<br/>
        .SavePassword = False<br/>
        .SaveData = True<br/>
        .AdjustColumnWidth = True<br/>
        .RefreshPeriod = 0<br/>
        .WebSelectionType = xlAllTables<br/>
        .WebFormatting = xlWebFormattingNone<br/>
        .WebPreFormattedTextToColumns = True<br/>
        .WebConsecutiveDelimitersAsOne = True<br/>
        .WebSingleBlockTextImport = False<br/>
        .WebDisableDateRecognition = False<br/>
        .WebDisableRedirections = False<br/>
        <br/>
        .Refresh BackgroundQuery:=False<br/>
        <br/>
      <br/>
      End With<br/>
      Do While ie.busy And Not ie.readystate = 4<br/>
        DoEvents<br/>
      Loop<br/>
      <br/>
    End With<br/>
    <br/>
    'close Internet Explorer<br/>
    ie.Quit<br/>
    <br/>
    Set ie = Nothing<br/>
  End Sub<br/>
  
  

Sub Titans()
 'Select Titans Worksheet
 Sheets("Titans").Select
 'Clear Contents
    Cells.Clear
    'Import data from Pro Football Reference
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.pro-football-reference.com/teams/oti/2022.htm#team_stats", Destination:= _
        Range("A1"))
        .PostText = "local"
        .Name = False
        .FieldNames = False
        .RefreshStyle = xlInsertDeleteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .HasAutoFormat = True
        .RefreshOnFileOpen = 1
        .BackgroundQuery = False
        .TablesOnlyFromHTML = True
        .SaveData = True
        .Refresh BackgroundQuery:=False  ---> This is the line of code that stops it from running
    End With
End Sub

1