.iqy Files and Dynamic Web Query

Bluffalo · Apr 26, 2010

I'm building a workbook that will allow me to pull data from a web-based reporting tool I have at work, and I've run into a problem.

The links to the 7 different reports I am seeking to integrate into my spreadsheet via web query are static/relative. For example

Static:
http://myworkreportserver.ext/
Relative
user06

I've built a pretty simple Concatenate setup that allows me to pull the static pieces together with the relative information that is inputted via a dropdown list and vlookup function(s).

The links are all over 256 characters, so I've had to find a workaround as Excel isn't fond of long urls. I reasearched this problem online, and compiled a few different ideas to build a functional system. A macro that first opens a .txt file, then copies the updated link from the workbook into the .txt file. Following this, the .txt file is saved as an .iqy file.

The .txt file is closed (I did a dialogue box overwrite as it was tedious clicking yes to overwrite the file each time), then the original workbook opens the newly created/updated .iqy file to run the web query.

I have a system of cell references that format the data pulled into the workbook with the web query, and the 1st of 7 reports works flawlessly!

Now we get to the problem.

The first .iqy file saves in the perfect format, which is this:

WEB[cr]
1[cr]
http://myurlhere

The problem I am running into, is that the other 6 reports seem to save the .iqy file with quotation marks around the url, and this is causing Excel to not accept the webquery.

Any idea what may be causing the other report urls to be saved with quotation marks when the 1st report doesn't?

Bluffalo · Apr 26, 2010

Thought I would include the macro in case that may be part of the problem. Also, the Concatenate formulas.

Code:

Sub sQmac()
'
' sQmac Written by Adam B. Huffman
'
    Workbooks.OpenText Filename:= _
        "\\KVUOMFPS01\ahuffman\Documents\PDMs\queries\sQ.txt", Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
 
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "=[autoPDM.xlsm]portalCONs!R18C1"
    Range("A3").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A4").Select
 
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
        "\\KVUOMFPS01\ahuffman\Documents\PDMs\queries\sQ.iqy", FileFormat:=xlText _
        , CreateBackup:=False
    ActiveWindow.Close
    Application.DisplayAlerts = True
 
    Sheets("sQ").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "FINDER;\\KVUOMFPS01\ahuffman\Documents\PDMs\queries\sQ.iqy", Destination _
        :=Range("A1"))
        .Name = "sQ"
        .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
    Range("A1").Select
    ActiveWorkbook.Connections("sQ").Delete
    End With
 
 
    Sheets("agentPDM").Select
    Range("A1").Select
 
    End
 
End Sub

This is the Concatenate formula that works

Code:

=CONCATENATE(A11,A12,A13,TEXT(A14,"mm/dd/yyyy"),A15,TEXT(A16,"mm/dd/yyyy"),A17)

This is the url that it yields (with sensitive information x'd out)
<TABLE style="WIDTH: 253pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=337><COLGROUP><COL style="WIDTH: 253pt; mso-width-source: userset; mso-width-alt: 12324" width=337><TBODY><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 253pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=21 width=337>http://corportal/main/reports/default.aspx?Reports=8&Report=Scorecards&lbl_DrillLevel=Supervisor&rdo_DrillLevel=3&rptViewType=3&LST_DOW=-1&DateGrouping=6&Version_ID=2&emp_id=-xxxxxxxxxxxx&sdate=04/11/2010&edate=04/17/2010&EMP_LEVEL2=5&EMP_LEVEL=4&SITE_LIST=17&Grp_Descr=SALES</TD></TR></TBODY></TABLE>

This is one of the concatenate formulas that DOESN'T work

Code:

=CONCATENATE(A20,TEXT(A21,"mm/dd/yyyy"),A22,TEXT(A23,"mm/dd/yyyy"),A24,A25,A26,A27,A28)

This is the url that it yields (with sensitive information x'd out)
<TABLE style="WIDTH: 253pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=337><COLGROUP><COL style="WIDTH: 253pt; mso-width-source: userset; mso-width-alt: 12324" width=337><TBODY><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 253pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=21 width=337>http://corportal/main/reports/default.aspx?Reports=6&Report=Productivity&Level=_DAILY&BillingSystem=CSG,ICOMS,BLENDED&Label=Daily&DateGrouping=6&lst_DOW=0,1,2,3,4,5,6&sdate=04/11/2010&edate=04/17/2010&rdo_DrillLevel=3&rptViewType=1&SUP_SK=-xxxxxxxxxxxx&TitleDisplay=xxxxxxxxxxxx&UseAgentLevel=YES&SITE_LIST=-1&GROUP_LIST=-1</TD></TR></TBODY></TABLE>

Bluffalo · Apr 29, 2010

I still haven't figured out why the quotation marks are showing up in my iqy file. Any ideas?

John_w · Apr 29, 2010

Bluffalo said:
This is one of the concatenate formulas that DOESN'T work

Code:

=CONCATENATE(A20,TEXT(A21,"mm/dd/yyyy"),A22,TEXT(A23,"mm/dd/yyyy"),A24,A25,A26,A27,A28)

This is the url that it yields (with sensitive information x'd out)
<TABLE style="WIDTH: 253pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=337><COLGROUP><COL style="WIDTH: 253pt; mso-width-source: userset; mso-width-alt: 12324" width=337><TBODY><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 253pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=21 width=337>http://corportal/main/reports/default.aspx?Reports=6&Report=Productivity&Level=_DAILY&BillingSystem=CSG,ICOMS,BLENDED&Label=Daily&DateGrouping=6&lst_DOW=0,1,2,3,4,5,6&sdate=04/11/2010&edate=04/17/2010&rdo_DrillLevel=3&rptViewType=1&SUP_SK=-xxxxxxxxxxxx&TitleDisplay=xxxxxxxxxxxx&UseAgentLevel=YES&SITE_LIST=-1&GROUP_LIST=-1</TD></TR></TBODY></TABLE>

Does that URL contain invalid characters? It looks okay, but the xxxxxxxxxxxx's might contain invalid characters, in which case you will need to 'escape' them.

Also, instead of Connection:="FINDER;\\KVUOMFPS01\ahuffman\Documents\PDMs\queries\sQ.iqy" and using .iqy files have you tried using the URL directly, ie. Connection:="URL;" & YourLongURL ?

Bluffalo · Apr 29, 2010

You might have just figured it out!

I can't put the URL directly in VBA because it is dynamic, hence this whole project.

I didn't even think about invalid characters, and that might be the key. What characters are invalid in this scenario? Would + or | be invalid, because the link has both of those.

John_w · Apr 29, 2010

Bluffalo said:
What characters are invalid in this scenario? Would + or | be invalid, because the link has both of those.

Yes, both those are invalid so you must encode them in the URL. Use the Escape function at http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/index.htm

Can't you do the string concatenation dynamically in VBA and dispense with the .iqy files altogether?

Bluffalo · Apr 29, 2010

I would love to be able to, that would speed up my macro quite a bit.

Luckily, I found out that the invalid characters were actually not absolutely necessary to run the web report. I modified my links and I can successfully run all of the reports now.

NEW PROBLEM THOUGH

I cannot seem to get Excel to let me have more than one active web query. When I try to run the second query, it ends up returning the results from the first query.

Any ideas about this?

John_w · Apr 30, 2010

Bluffalo said:
I cannot seem to get Excel to let me have more than one active web query. When I try to run the second query, it ends up returning the results from the first query.

I'm not sure. Looking at your code, you have .Refresh BackgroundQuery:=False which should mean it waits for the web query to return the data before continuing.

Just guessing, but maybe the problem is ActiveWorkbook.Connections("sQ").Delete. You are deleting the connection, but not the web query. Try adding, refreshing and deleting the web query separately, something like this:

Code:

    Dim qt As QueryTable

    Set qt = ActiveSheet.QueryTables.Add(Connection:= _
        "FINDER;\\KVUOMFPS01\ahuffman\Documents\PDMs\queries\sQ.iqy", Destination _
        :=Range("A1"))
    With qt
        .Name = "sQ"
        .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
    End With
    
    qt.Refresh
    
    qt.Delete

Bluffalo · Apr 30, 2010

I tried to apply that code, but it doesn't seem to be working.

The second web query is supposed to pull a different .iqy file and enter that data onto a different worksheet. The really odd thing, is that the whole macro will run top to bottom and each query will post, and the details of the query in the connection shows that it is pointing to the correct link, but the data that shows in the web query is all from the first query.

Example.

I've got Scorecard as my first query, and sQ.iqy is the corresponding query file. When the data is pulled, the Scorecard is entered on the sheet.

Productivity is my next query, and pQ.iqy is the corresponding query file. When the macro runs, the Scorecard data shows up where the Productivity is supposed to. I removed the command to delete the query connection, just to check what it was pulling, and under the definition of the query, the connection file was correct, but the connection string is that of the Scorecard file.

I'm in way over my head, but I'm only 1 step away from making this thing work. Any thoughts on why this might be happening?

John_w · May 1, 2010

Ooops! Hope you've spotted my mistake.

qt.Refresh
should be
qt.Refresh BackgroundQuery:=False

I suggest putting debug statements at various points in your code. Also ensure that where you specify ActiveSheet in your code that it really is the intended sheet.

.iqy Files and Dynamic Web Query

Bluffalo

New Member

Excel Facts

Bluffalo

New Member

Bluffalo

New Member

John_w

MrExcel MVP

Bluffalo

New Member

John_w

MrExcel MVP

Bluffalo

New Member

John_w

MrExcel MVP

Bluffalo

New Member

John_w

MrExcel MVP

Similar threads

Forum statistics

Share this page

.iqy Files and Dynamic Web Query

New Member

Excel Facts

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock