Web query help

Hi Friends,

I need to write a web query which is .iqy file where in the date is taken dynamically. I wish to give the date in cell a1. The code should pick up the date from the a1 cell fills that in the date part and open the website automatically.


Rollup.html?siteId=390&maxDate=04/03/2007
&intervalType=Daily&useLiveData=0&runReport=1&
textMode=1&scheduleId=


This site wont work for you since its a intranet site. Any ideas how to make the code in the .iqy file pick up the date from cell a1 in the excel sheet.

Please help

Hi priya,

The .iqy file is just a flat-text file that excel interprets to understand how to perform your webquery, it doesn't actually contain any code. This is good, since it will allow us to create/modify your file very easily with VBA code.

Here's an example .iqy file contents of a simple webquery I just did for this thread:

Quote:

---

Originally Posted by sample.iqy

WEB
1
http://www.vbaexpress.com/forum/showthread.php?t=12175

Selection=AllTables
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False

---

What I would recommend you do is to open up your .iqy file in notepad, change your current max date value to a string that isn't in the file otherwise, like

Rollup.html?siteId=390&maxDate=MYMAXDATE
&intervalType=Daily&useLiveData=0&runReport=1&
textMode=1&scheduleId=


And save that somewhere easy to find, like C:\MaxDateTemplate.iqy

Then you can run some VBA code to open the maxdatetemplate.iqy file, replace MYMAXDATE with a formatted date value from A1, then save it as a different name like C:\RealWebQueryFile.iqy
[vba]Sub priyaIQY()
Dim vFF As Long, tStr As String, vInFile As String, vOutFile As String
vInFile = "C:\MaxDateTemplate.iqy"
vOutFile = "C:\RealWebQueryFile.iqy"
vFF = FreeFile
Open vInFile For Binary As #vFF
tStr = Space$(LOF(vFF))
Get #vFF, , tStr
Close #vFF
tStr = Replace(tStr, "MYMAXDATE", Format(Range("A1").Value, "mm/dd/yyyy"))
Open vOutFile For Output As #vFF
Print #vFF, tStr;
Close #vFF
End Sub[/vba]Then after running it simply run the saved query file. You could put that in the code above too. Though it would be easier for you to record a macro while opening the saved query, it would look similar to this (after substituting the vOutFile variable for the actual filename:[vba]
'this would go after the last "Close #vFF" line above
Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:="FINDER;" & vOutFile, _
Destination:=Range("A1"))
.Name = "sample"
.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
.Refresh BackgroundQuery:=False
End With[/vba]Matt

Check out my page on web queries with a parameter:

www.jkp-ads.com/articles/webquery.asp

Nice, JKP, didn't know you could do that :)

Thanks Matt for the help. It works great. I need another help is it possible that I can call one macro from another.If yes how to do it ?

JKP I have gone through your site when I first started creating my web queries.It is a awesome site with good explanations.

Hi Matt,

Thanks a lot for all your effort for my macro... it works just fine... No words to express my thanks to u......

Priya Srini

Thanks!

Quote:

---

Originally Posted by priya123

I need another help is it possible that I can call one macro from another.If yes how to do it ?

---

It is definitely possible to call one from another, you can either just enter the macro name, use Call before it, or even (though not recommended except under certain circumstances) use application.run.. let me give you an example:[vba]Sub Macro1()
Macro2
Call Macro3
Application.Run "Macro4"
End Sub
Sub Macro2()
MsgBox "This is macro 2"
End Sub
Sub Macro3()
MsgBox "This is macro 3"
End Sub
Sub Macro4()
MsgBox "This is macro 4"
End Sub[/vba]If your other macro has any arguments, you can include them like such:
[vba]Sub Macro1()
Macro2 "hi", "bye"
Call Macro2("hello", "Goodbye")
Application.Run "Macro2", "hey", "seeya"
End Sub
Sub Macro2(ByVal arg1 As String, ByVal arg2 As String)
MsgBox arg1 & vbCrLf & vbCrLf & arg2
End Sub[/vba]Matt

Hi I tried using Matt's web query it works fine when the date in the URL is in the format mm/dd/yyyy but how to give that when the date is in
04%2F10%2F2007 format. Please help... the rest are all fine and am using Matt's file concept for this.

priya,

Why not just use JKP's suggestion above? It would eliminate the need to build the .iqy file every day..

I am a little confused about your followup question.. Are you saying that your cell has data in mm/dd/yyyy format and you want the data to be in mm%2Fdd%2Fyyyy format?
%2F is the same as / (2F in hex is equivalent to 47, ascii character code 47 is "/"), it is the URLEncoded equivalent

Whichever way you're looking to translate, use the Replace function[vba]Replace("04%2F10%2F2007", "%2F", "/")
'or
Replace("04/10/2007", "/", "%2F")[/vba]Matt

Sorry just getting to know and I guess i find parameters thing a difficult one. Also the replace thing above I dont know how to give it since it is not working for me.

Could you please let me know how to give and where so that it reads with 2F.

I tried running the file with the / it is not hitting the correct URL.

That is OK, figuring these things out isn't always a very quick thing to do.

[vba]'Instead of using:
tStr = Replace(tStr, "MYMAXDATE", Format(Range("A1").Value, "mm/dd/yyyy"))

'You could either use this (recommended):
tStr = Replace(tStr, "MYMAXDATE", Format(Range("A1").Value, "mm""%2F""dd""%2F""yyyy"))
'or
tStr = Replace(tStr, "MYMAXDATE", Replace(Format(Range("A1").Value, "mm/dd/yyyy"), "/", "%2F"))[/vba]Matt

Thanks a lot Matt.....your help for a newbie like me is so much appreciated... thanks for the good work. It works great.

Priya

Hi Matt,

Is that possible to just make the webquery pick up few columns from the website. If yes where to add what ... please let me know ... thanks

The possibilities depend on your Excel version. For Xp and up, in the define query screen, you see small black arrows on a yellow background. Click the one at the top-left corner of the table you want to retrieve.

I did that. But when running the .iqy file it says the query did not return any data. I do not know what is wrong in that.

Why would you want to use that iqy file?

I'm just here to listen, I still have office 2000 and have no such feature. I avoid querying the web directly from excel anyways, so I dont know that I'd be able to help necessarily. I know that in the past when I did use web queries I just entered individual table numbers until I found the one I wanted, wouldn't work for specific columns though (in my XL version)

By the way, Priya, as an FYI you don't need to send me a private message for the same thing you add to this thread, I still get the notifications when you post here.

Hi JKP,

I still did not know how to pass parameters to the query that is why I was trying to do with iqy files. If i can do that using parameters it should be great.

I just need the date to be passed as a parameter in my queries and that the date should be picked up from the value in cell a1.

If any thoughts please let me know

Priya

Hi JKP,

I still did not know how to pass parameters to the query that is why I was trying to do with iqy files. If i can do that using parameters it should be great.

I just need the date to be passed as a parameter in my queries and that the date should be picked up from the value in cell a1.

If any thoughts please let me know

Priya

Hi Matt,

Quote:

---

Originally Posted by priya123

Hi Friends,

I need to write a web query which is .iqy file where in the date is taken dynamically. I wish to give the date in cell a1. The code should pick up the date from the a1 cell fills that in the date part and open the website automatically.


Rollup.html?siteId=390&maxDate=04/03/2007
&intervalType=Daily&useLiveData=0&runReport=1&
textMode=1&scheduleId=


This site wont work for you since its a intranet site. Any ideas how to make the code in the .iqy file pick up the date from cell a1 in the excel sheet.

Please help

---

My new code

Quote:

---

Sub macro_er()

Dim vFF As Long, tStr As String, vInFile As String, vOutFile As String, tstr1 As String
vInFile = "C:\Documents and Settings\priyas\My Documents\Query\123.iqy"
vOutFile = "C:\Documents and Settings\priyas\My Documents\Query\1231.iqy"
vFF = FreeFile
Open vInFile For Binary As #vFF
tStr = Space$(LOF(vFF))
tstr1 = Space$(LOF(vFF))
Get #vFF, , tStr
' Get #vFF, , tstr1
Close #vFF
tStr = replace(tStr, "MYMINDATE", Format(Range("A3").Value, "mm/dd/yyyy"))
'tStr = replace(tStr, "MYMIDDATE", Format(Range("A3").Value, "mm/dd/yyyy"))
' tStr = replace(tStr, "MYMAXDATE", Format(Range("b3").Value, "mm/dd/yyyy"))
Open vOutFile For Output As #vFF
Print #vFF, tStr;
Close #vFF
End Sub

---

I am getting an error saying invalid property assignment or wrong number of arguments. Why is this so any insight on this ?

Hi Priya,

I'm gonna need more information.. what line are you receiving that on?

Nothing I see appears wrong. The only thing that it could be at quick glance is the fact that your "Replace" function is showing with a small "r" .. could you possibly have made a function or sub named "replace" that is being called instead of the built-in function? If not, please also provide the line that is erroring out.

I also again want to ask why you haven't just used JKP's suggestion above? It would eliminate the need for this.