Web Query from Web Address in Cell

[anderb16]

Hey everybody,

Background
First post, as I've been able to answer many of my questions through the use of google. I'm working on a grade calculator/schedule for my college courses. It's a project that has evolved over two semesters so far, but I'm now at a road block.

Question
The website I'm trying to load data from is a syllabus for a course. For example - https://my.excelsior.edu/web/syllabus-library/art101_8wk. All of the links to the courses begin the same - https://my.excelsior.edu/web/syllabus-library/. I have a different sheet for each course and I'd like to load the data from the syllabus for each of those courses so I can automate the formulas for many of the calculations.

I have created a formula that returns the last part of the web address for the syllabus that I need the data from; however, it can be easily modified to return the entire web address.

How can I get excel to load the data from a web page based on the address provided by a cell?

If I can avoid VBA it may make life easier; however, this task seems too complicated for a formula. I would need specific instructions on how to implement VBA if that is how I need to resolve this.

Additional
Link to a copy of my spreadsheet - https://drive.google.com/file/d/0Bw9Rbc8CDV10RXk1RXlaZXdWQjg/view?usp=sharing

 

[John_w]

How can I get excel to load the data from a web page based on the address provided by a cell?

See Using Parameters with Web Queries - WebQuery.

However it sounds like you don't need a dynamic web query as described in that article (where the web query refreshes when a parameter cell changes). Instead, manually create a web query for each sheet, specifying the full URL for the associated course. Your formulas can then reference the cells which each web query returns data to.

 

[anderb16]

See Using Parameters with Web Queries - WebQuery

John,


Thanks for the response. I tried to manipulate the address to the syllabi to work as a Web Query with Parameters in the past with no luck. I assume it is because the address I am using does not fit with the conventional naming used by excel in the Parameters - no "?=" in the address like all the examples for the tool.


I can include links to each syllabi in every sheet for every course, but this is exactly what I'm trying to avoid. The ultimate goal is to utilize a template sheet for a new course and have it autofill with everything except for the grades.


If the address fit the required structure the answer you provided would have worked perfectly.


Brian

 

[John_w]

You are correct; the web query parameter cell only works if there is a query string in the URL (the ?= part), so won't work for your URLs.

I recorded a macro for a web query which imports the entire page for https://my.excelsior.edu/web/syllabus-library/art101_8wk starting at A2 on the active sheet:

Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://my.excelsior.edu/web/syllabus-library/art101_8wk", Destination:= _
        Range("A2"))
        .Name = "art101_8wk"
        .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

I then edited the code so that the web query takes the art101_8wk part of the URL from cell A1 on the active sheet (test using a different sheet to the one used by Macro1 with the text art101_8wk in cell A1):

Sub Macro1_edited()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://my.excelsior.edu/web/syllabus-library/" & Range("A1").Value, Destination:= _
        Range("A2"))
        .Name = "art101_8wk"
        .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

This is just a simple example of what you can do with web queries and VBA - mostly done with the macro recorder. Hopefully it will give you an idea of how to progress with your task.

The code posted goes in a 'regular' VBA module - see http://www.contextures.com/xlvba01.html#Regular for instructions.

 

[anderb16]

John,

Pure excellence! To those of us who haven't got around to learning the awesomeness that is VBA (or recording macros), you are a Greek god.

Thank you for taking the time to create the macro and provide the link to step-by-step instructions on how to implement it.

In case wasn't clear enough, it works as expected.


Brian


Now if only I can figure out how to mark this thread solved.