Excel Web Query Macro

Schandor
Schandor used Ask the Experts™
on
Hi all,
I am extremely new to VB so please bear with me. I am trying to write a macro for Excel that will query a website and then pull in some data.  I have been able to get it to work via prompting the user for input.  However, I would rather the user simply fill in some cells/fields in the worksheet and use that data for the query.   Does anyone know how to specify a particular cell in VB?  In particular doing a wbe query?

Here is what I have in the webquery that work with prompting:

http://www.watkins.com/autosub/autorate.asp?org=["OriginZip"]&dest=["DestZip"]&weight1=["Weight1"]&class1=["Class1"]

The macro prompts me for data between the "[" and "]" symbols.  If you have any questions let me know and I would be happy to clarify.

Thank you for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
are you using VB or VBA (the microsoft office scripting language?

If it is VBA than to specify a particulat cell use

Sheets("insert sheet name here in quotes").Cells(1, 1).Select

or

Sheets("insert sheet name here in quotes").Range("E1").Select



Top Expert 2006

Commented:
Hello Schandor,

why not build the string url in a cell E1 like

="http://www.watkins.com/autosub/autorate.asp?org="&A1&"&dest="&B1&"&weight1="&C1&"&class1="&D1

A1, B1, C1, D1 are the cells the user fills for the formula in E1

then you can use this as the url in your webquery you can build in code

if you need more let me know, got no code at hand only this PAQ which contains everything you need :)

http://www.experts-exchange.com/Applications/MS_Office/Q_20294436.html

HAGD:O)Bruintje

Author

Commented:
Hi all,
Sorry for the delay in getting back, work fires abound.  Richard, I must be using VBA as I am on a Mac and using Office X's editor. Sorry for the confusion there.

bruintje, I like your idea, but I am unsure of how to take a URL sitting in a cell and use it in a Web query that changes dynamically.  I may be blind but I am having a heck of a time finding documentaion on using Web query.  Your thoughts?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2006

Commented:
in that link above you can download a workbook called yahloader.xls everything is in there
Top Expert 2006

Commented:
bit short on time now but post if something is not clear

Author

Commented:
Hi all,
OK, I gave that a try and wrote this:  

Sub Shipping_Rates()
'
' Shipping_Rates Macro
'
Dim strURL
'
    strURL = Worksheets("Sheet1").Range("A1")
    Range("A10").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & strURL _
        .PostText = "test"
        .Name = False
        .FieldNames = False
        .RefreshStyle = xlInsertDeleteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .HasAutoFormat = True
        .RefreshOnFileOpen = 1
        .BackgroundQuery = False
        .TablesOnlyFromHTML = True
        .SaveData = True
        .Refresh BackgroundQuery:=False
        .UseListObject = False
    End With
End Sub

Now I am getting compile errors.  The URL in the worksheet is correct but I'm still having trouble referencing it.  The URL is in the worksheet "Sheet1" in cell A1.  I was hoping to stick the site's contents in cell A10.  Thoughts?  

By the way, thank you for all your help.
Why don't you record a new macro, and use the New Web Query to generate the code for you ( can be found here - Data->Import External Data->New Web Query).  You can then see how the code should look and than you can tailor it to suit your needs.

Author

Commented:
Hi Richard,
Actually, that's what I tried (the recording, that is).  My tailoring isn't quite up to snuff is the problem.

Thank you.
What URL are you trying to Access?, What sort of data are you trying to retrieve?
Ok is this what you want to do?

Method 1 Using SpreadSheet cells -

Private Sub First_Method()
'
' RecordedQuery Macro
' Macro recorded 22/11/2002 by Richard Eustace

'Spreadsheet layout
'       A       B       C       D
'1      ORG     dest    weight1 class1
'2      55368   55344   2500    125



Dim strURL          As String

Dim strOrg          As String


Sheets("Sheet1").Range("A6:F24").Clear

strURL = "URL;http://www.watkins.com/autosub/autorate.asp?org=" & Sheets("Sheet1").Cells(2, 1).Value & _
        "&dest=" & Sheets("Sheet1").Cells(2, 2).Value & "&weight1=" & Sheets("Sheet1").Cells(2, 3).Value & _
        "&class1=" & Sheets("Sheet1").Cells(2, 4).Value

    With ActiveSheet.QueryTables.Add(Connection:=strURL, _
        Destination:=Range("A6"))
        .Name = "autorate.asp?org=" & strOrg & "&dest=" & strDest & "&weight1=" & strweight1 & "&class1=" & strclass1
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Method two Using InputBoxes -

Private Sub Second_Method()
'
' RecordedQuery Macro
' Macro recorded 22/11/2002 by Richard Eustace
'

Dim strURL          As String

Dim strOrg          As String
Dim strDest         As String
Dim strweight1      As String
Dim strclass1       As String

Sheets("Sheet1").Range("A6:F24").Clear


strOrg = InputBox("Please enter Org")
strDest = InputBox("Please enter Dest")
strweight1 = InputBox("Please enter Weight1")
strclass1 = InputBox("Please enter Class1")

strURL = "URL;http://www.watkins.com/autosub/autorate.asp?org=" & strOrg & _
        "&dest=" & strDest & "&weight1=" & strweight1 & _
        "&class1=" & strclass1

    With ActiveSheet.QueryTables.Add(Connection:=strURL, _
        Destination:=Range("A6"))
        .Name = "autorate.asp?org=" & strOrg & "&dest=" & strDest & "&weight1=" & strweight1 & "&class1=" & strclass1
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Author

Commented:
Hi Richard,
OK, your first method seems to be what i want to do, but I get a syntax error when I compile it (via a copy and paste).  Sorry to be such a pest.
What is the error?

did you set the sheet up like this

'Spreadsheet layout
'       A       B       C       D
'1      ORG     dest    weight1 class1
'2      55368   55344   2500    125

Author

Commented:
Yes it is set up that way.  As far as the error, all it tells me is:

Comile Error:  Syntax Error

It then has the two DIM statements highlited in red as well as the URL statement.  Due to my lack of knowledge and your patience, I'm increasing the points on this one.  Thanks for the help.
What version of Excel are you running. I don't think it should mater much But just checking.  I am running the version which comes with Office XP. I cannot think why you are getting an error. I copied the cection of code out of this toppic and pasted it into excel and it works fine.

Author

Commented:
Microsoft Excel X for Mac, Service Pack 1 is installed
I don't know about excel for Macs.  I therefore assume the syntax is slightly different.

Sorry I cannot help

Author

Commented:
Still haven't gotten this to work completely but it has since become a back-burner issue.  Hence, I will award your points, apologize for taking so long to get back, and thank you for your help

Have a nice day

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial