CODEWITHSUNDEEP
vbaexcel
Gokotai
Gokotai

Reputation: 153

Excel VBA macro to look up intranet link into querytable

I'm trying to write a macro to extract information from an intranet page onto an excel worksheet. I'm trying to automate this because the URL can change depending on the date and "shift" chosen on the webpage.

I'm getting several errors upon compiling my code. I've tried a few different approaches but none of them seem to work.

Any thoughts?

    Sub InfoExtract()

    Dim Symbol As String
    Dim URL As String
    Dim ws1 As Worksheet

    Set ws1 = Sheets("Sheet3")
    ws1.Select

    Symbol = "%20"

    With ActiveSheet.QueryTables.Add(Connection:=Range("link1").Value & Range("day").Value & Symbol & Range("month").Value & Symbol & Range("Year").Value & Symbol & "the_shift=" & Range("shift").Value & "_1", Destination:=Range("$B$4"))
        .Name = "shift_report.asp?the_date=" & Range("day").Value & Symbol & Range("month").Value & Symbol & Range("Year").Value & Symbol & "the_shift=" & Range("shift").Value
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Upvotes: 0

Views: 313

Answers (1)

Ralph
Ralph

Reputation: 9434

I'd say the link is incorrect and therefore you're getting the error. The rest seems fine and can be easily verified using the macro recorder anyway. Therefore, I'd suggest that you build and verify the link before you use it.

strURL = Range("link1").Value & Range("day").Value & Symbol & Range("month").Value & Symbol & Range("Year").Value & Symbol & "the_shift=" & Range("shift").Value & "_1" ' This is copied from your code and cannot be verified
Debug.Print strURL 'copy this into the IE and see if it works
With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range("$B$4"))
        .Name = "TestName"
'... the rest of your code as is ...

Upvotes: 1

Related Questions