Schandor
asked on
Excel Web Query Macro
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=["DestZ
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.
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=["DestZ
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.
Hello Schandor,
why not build the string url in a cell E1 like
="http://www.watkins.com/autosub/autorate.asp?org="&A1&"&dest="&B1&"&weigh
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 :)
https://www.experts-exchange.com/questions/20294436/Excel-function-Template-to-create-yearly-average-of-monthly-stock-averages-for-a-Series-of-downlaoded-stocks-Which-service.html
HAGD:O)Bruintje
why not build the string url in a cell E1 like
="http://www.watkins.com/autosub/autorate.asp?org="&A1&"&dest="&B1&"&weigh
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 :)
https://www.experts-exchange.com/questions/20294436/Excel-function-Template-to-create-yearly-average-of-monthly-stock-averages-for-a-Series-of-downlaoded-stocks-Which-service.html
HAGD:O)Bruintje
ASKER
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?
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?
in that link above you can download a workbook called yahloader.xls everything is in there
bit short on time now but post if something is not clear
ASKER
Hi all,
OK, I gave that a try and wrote this:
Sub Shipping_Rates()
'
' Shipping_Rates Macro
'
Dim strURL
'
strURL = Worksheets("Sheet1").Range
Range("A10").Select
With ActiveSheet.QueryTables.Ad
"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.
OK, I gave that a try and wrote this:
Sub Shipping_Rates()
'
' Shipping_Rates Macro
'
Dim strURL
'
strURL = Worksheets("Sheet1").Range
Range("A10").Select
With ActiveSheet.QueryTables.Ad
"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.
ASKER
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.
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?
ASKER
Here is a sample URL:
"http://www.watkins.com/autosub/autorate.asp?org=55368&dest=55344&weight1=2500&class1=125
"http://www.watkins.com/autosub/autorate.asp?org=55368&dest=55344&weight1=2500&class1=125
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
did you set the sheet up like this
'Spreadsheet layout
' A B C D
'1 ORG dest weight1 class1
'2 55368 55344 2500 125
ASKER
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.
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.
ASKER
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
Sorry I cannot help
ASKER
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
Have a nice day
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").Selec