Robert McElrath
New Member
- Joined
- Jul 26, 2008
- Messages
- 14
I have a dynamic web query .iqy file that accesses an exchange rate website, and after entering answers to 4 questions, an exchange rate history table is returned. It works fine.<o></o>
<o> </o>
[FONT="]WEB<o></o>[/FONT]
[FONT="]1<o></o>[/FONT]
[FONT="]http://www.oanda.com/convert/fxhistory?lang=en&date_fmt=us&exch=USD&expr=EUR&margin_fixed=0&&SUBMIT=Get+Table&format=CSV&redirected=1&date1=["Start","Please enter a starting date."]&date=["End","Please enter an ending date."]&exch2=["From","Please enter the original currency."]&expr2=["To","Please enter the currency to convert to."]<o></o>[/FONT]
[FONT="]<o> </o>[/FONT]
[FONT="]Selection=7<o></o>[/FONT]
[FONT="]Formatting=None<o></o>[/FONT]
[FONT="]PreFormattedTextToColumns=True<o></o>[/FONT]
[FONT="]ConsecutiveDelimitersAsOne=True<o></o>[/FONT]
[FONT="]SingleBlockTextImport=False<o></o>[/FONT]
[FONT="]DisableDateRecognition=False<o></o>[/FONT]
[FONT="]DisableRedirections=False<o></o>[/FONT]
<o> </o>
However, I would rather store the values of the 4 parameters (date1, date, exch2, expr2) in cells B1, B2, B3, and B4 and have a macro run the web query. I tried with the following, but the ActiveSheet.QueryTables.Add statement has syntax errors that I do not know how to fix. Any suggestions? Many thanks.<o></o>
<o> </o>
[FONT="]Sub GetFXHistory()<o></o>[/FONT]
[FONT="] With ActiveSheet.QueryTables.Add(Connection:= _<o></o>[/FONT]
[FONT="] "URL;http://www.oanda.com/convert/fxhistory?lang=en&date_fmt=us&exch=USD&expr=EUR&margin_fixed=0&&SUBMIT=Get+Table&format=CSV&redirected=1"&date1=&range.("B1").value &date=&range("B2").value &exch2=&range("B3").value &expr2=&range("B4").value, Destination:=Range(“A1”))<o></o>[/FONT]
[FONT="] .Name = "fxhistory_1"<o></o>[/FONT]
[FONT="] .FieldNames = True<o></o>[/FONT]
[FONT="] .RowNumbers = False<o></o>[/FONT]
[FONT="] .FillAdjacentFormulas = False<o></o>[/FONT]
[FONT="] .PreserveFormatting = True<o></o>[/FONT]
[FONT="] .RefreshOnFileOpen = False<o></o>[/FONT]
[FONT="] .BackgroundQuery = True<o></o>[/FONT]
[FONT="] .RefreshStyle = xlInsertDeleteCells<o></o>[/FONT]
[FONT="] .SavePassword = False<o></o>[/FONT]
[FONT="] .SaveData = True<o></o>[/FONT]
[FONT="] .AdjustColumnWidth = True<o></o>[/FONT]
[FONT="] .RefreshPeriod = 0<o></o>[/FONT]
[FONT="] .WebSelectionType = xlSpecifiedTables<o></o>[/FONT]
[FONT="] .WebFormatting = xlWebFormattingNone<o></o>[/FONT]
[FONT="] .WebTables = "6"<o></o>[/FONT]
[FONT="] .WebPreFormattedTextToColumns = True<o></o>[/FONT]
[FONT="] .WebConsecutiveDelimitersAsOne = True<o></o>[/FONT]
[FONT="] .WebSingleBlockTextImport = False<o></o>[/FONT]
[FONT="] .WebDisableDateRecognition = False<o></o>[/FONT]
[FONT="] .WebDisableRedirections = False<o></o>[/FONT]
[FONT="] .Refresh BackgroundQuery:=False<o></o>[/FONT]
[FONT="] End With<o></o>[/FONT]
[FONT="]End Sub<o></o>[/FONT]
<o> </o>
[FONT="]WEB<o></o>[/FONT]
[FONT="]1<o></o>[/FONT]
[FONT="]http://www.oanda.com/convert/fxhistory?lang=en&date_fmt=us&exch=USD&expr=EUR&margin_fixed=0&&SUBMIT=Get+Table&format=CSV&redirected=1&date1=["Start","Please enter a starting date."]&date=["End","Please enter an ending date."]&exch2=["From","Please enter the original currency."]&expr2=["To","Please enter the currency to convert to."]<o></o>[/FONT]
[FONT="]<o> </o>[/FONT]
[FONT="]Selection=7<o></o>[/FONT]
[FONT="]Formatting=None<o></o>[/FONT]
[FONT="]PreFormattedTextToColumns=True<o></o>[/FONT]
[FONT="]ConsecutiveDelimitersAsOne=True<o></o>[/FONT]
[FONT="]SingleBlockTextImport=False<o></o>[/FONT]
[FONT="]DisableDateRecognition=False<o></o>[/FONT]
[FONT="]DisableRedirections=False<o></o>[/FONT]
<o> </o>
However, I would rather store the values of the 4 parameters (date1, date, exch2, expr2) in cells B1, B2, B3, and B4 and have a macro run the web query. I tried with the following, but the ActiveSheet.QueryTables.Add statement has syntax errors that I do not know how to fix. Any suggestions? Many thanks.<o></o>
<o> </o>
[FONT="]Sub GetFXHistory()<o></o>[/FONT]
[FONT="] With ActiveSheet.QueryTables.Add(Connection:= _<o></o>[/FONT]
[FONT="] "URL;http://www.oanda.com/convert/fxhistory?lang=en&date_fmt=us&exch=USD&expr=EUR&margin_fixed=0&&SUBMIT=Get+Table&format=CSV&redirected=1"&date1=&range.("B1").value &date=&range("B2").value &exch2=&range("B3").value &expr2=&range("B4").value, Destination:=Range(“A1”))<o></o>[/FONT]
[FONT="] .Name = "fxhistory_1"<o></o>[/FONT]
[FONT="] .FieldNames = True<o></o>[/FONT]
[FONT="] .RowNumbers = False<o></o>[/FONT]
[FONT="] .FillAdjacentFormulas = False<o></o>[/FONT]
[FONT="] .PreserveFormatting = True<o></o>[/FONT]
[FONT="] .RefreshOnFileOpen = False<o></o>[/FONT]
[FONT="] .BackgroundQuery = True<o></o>[/FONT]
[FONT="] .RefreshStyle = xlInsertDeleteCells<o></o>[/FONT]
[FONT="] .SavePassword = False<o></o>[/FONT]
[FONT="] .SaveData = True<o></o>[/FONT]
[FONT="] .AdjustColumnWidth = True<o></o>[/FONT]
[FONT="] .RefreshPeriod = 0<o></o>[/FONT]
[FONT="] .WebSelectionType = xlSpecifiedTables<o></o>[/FONT]
[FONT="] .WebFormatting = xlWebFormattingNone<o></o>[/FONT]
[FONT="] .WebTables = "6"<o></o>[/FONT]
[FONT="] .WebPreFormattedTextToColumns = True<o></o>[/FONT]
[FONT="] .WebConsecutiveDelimitersAsOne = True<o></o>[/FONT]
[FONT="] .WebSingleBlockTextImport = False<o></o>[/FONT]
[FONT="] .WebDisableDateRecognition = False<o></o>[/FONT]
[FONT="] .WebDisableRedirections = False<o></o>[/FONT]
[FONT="] .Refresh BackgroundQuery:=False<o></o>[/FONT]
[FONT="] End With<o></o>[/FONT]
[FONT="]End Sub<o></o>[/FONT]