Solved

I have a Excel web query and want it to accept a variable, but my subroutine will not work.

Posted on 2010-09-07
22
337 Views
Last Modified: 2012-05-10
Why does this coding give me an error  when I try to set QT to the connectstring?  What can I do to make it work?
Sub MyQuery()
    Dim WSD As Worksheet
    Dim WSW As Worksheet
    Dim QT As QueryTable
    Dim FinalRow As Long
    Dim i As Integer
    Dim ConnectString As Variant
    Dim FinalResultRow As Long
    Dim RowCount As Long
    Dim stock As String
   
        Set WSW = Worksheets("Portfolio")
    Set WSD = Worksheets("Workspace")
   
    'Clear workspace
    ' On the Workspace worksheet, clear all existing query tables
   
    'TRAP ERROR IF QUERYTABLE DOESN'T EXIST (Make a subroutine to clear and call it here!!)
    On Error Resume Next
        WSD.Range("A10:G80").Select
        Selection.QueryTable.Delete
        Selection.ClearContents
   
    stock = "MO"
    'Build the ConnectString
    'stock = WSW.Range("A3")
   
    ConnectString = "WSD.QueryTables.Add(Connection:=" _
    & """URL;http://finance.yahoo.com/q/hp?s=" & stock & "+Historical+Prices""" _
    & ", Destination:=WSD.Range(""$A$10""))"
   
    Set QT = ConnectString
    'MsgBox ConnectString
 
    ' On the Workspace worksheet, clear all existing query tables
   
    'TRAP ERROR IF QUERYTABLE DOESN'T EXIST
        WSD.Range("A10:G80").Select
        Selection.QueryTable.Delete
        Selection.ClearContents
   
   ' (THE FOLLOWING TEST STRING WORKS)
   ' Set QT = WSD.QueryTables.Add(Connection:= _
   '     "URL;http://finance.yahoo.com/q/hp?s=MO+Historical+Prices", Destination:= _
  '      WSD.Range("$A$10"))
         
    With QT      
        .Name = "portfolio"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """yfncsubtit"",16"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
   
End Sub
0
Comment
Question by:rosse_f
  • 11
  • 10
22 Comments
 
LVL 13

Accepted Solution

by:
MWGainesJR earned 500 total points
ID: 33619674
You're trying to set QT to a string type but you have it defined as a Query table.
0
 

Author Comment

by:rosse_f
ID: 33619729
Do you have a suggestion on what to do?
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33619834
try this
Sub MyQuery()

    Dim WSD As Worksheet

    Dim WSW As Worksheet

    Dim QT As QueryTable

    Dim FinalRow As Long

    Dim i As Integer

    Dim ConnectString As Variant

    Dim FinalResultRow As Long

    Dim RowCount As Long

    Dim stock As String

    

        Set WSW = Worksheets("Portfolio")

    Set WSD = Worksheets("Workspace")

    

    'Clear workspace

    ' On the Workspace worksheet, clear all existing query tables

    

    'TRAP ERROR IF QUERYTABLE DOESN'T EXIST (Make a subroutine to clear and call it here!!)

    On Error Resume Next

        WSD.Range("A10:G80").QueryTable.Delete

        WSD.Range("A10:G80").ClearContents

    

    stock = "MO"

    'Build the ConnectString

    'stock = WSW.Range("A3")

    

    ConnectString = "WSD.QueryTables.Add(Connection:=" _

    & """URL;http://finance.yahoo.com/q/hp?s=" & stock & "+Historical+Prices""" _

    & ", Destination:=WSD.Range(""$A$10""))"

    

    Set QT.commandtext = ConnectString

    'MsgBox ConnectString

  

    ' On the Workspace worksheet, clear all existing query tables

    

    'TRAP ERROR IF QUERYTABLE DOESN'T EXIST

        WSD.Range("A10:G80").QueryTable.Delete

        WSD.Range("A10:G80").ClearContents

    

   ' (THE FOLLOWING TEST STRING WORKS)

   ' Set QT = WSD.QueryTables.Add(Connection:= _

   '     "URL;http://finance.yahoo.com/q/hp?s=MO+Historical+Prices", Destination:= _

  '      WSD.Range("$A$10"))

          

    With QT       

        .Name = "portfolio"

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = False

        .RefreshPeriod = 0

        .WebSelectionType = xlSpecifiedTables

        .WebFormatting = xlWebFormattingNone

        .WebTables = """yfncsubtit"",16"

        .WebPreFormattedTextToColumns = True

        .WebConsecutiveDelimitersAsOne = True

        .WebSingleBlockTextImport = False

        .WebDisableDateRecognition = False

        .WebDisableRedirections = False

        .Refresh BackgroundQuery:=False

    End With

    

End Sub

Open in new window

0
 

Author Comment

by:rosse_f
ID: 33620156
Please show me how to correct the errors, I don't know what to do.
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33620272
I corrected the error with assigning the value to QT...If you're getting errors from there, then it's probably best we backtrack and figure out what it is you're trying to do...Probably going about it the wrong way.
Explain the best you can what it is you're needing.
0
 

Author Comment

by:rosse_f
ID: 33620298
Did not see you suggestion:
 Set QT.commandtext = ConnectString

Debug takes me to the connection string:
ConnectString = "WSD.QueryTables.Add(Connection:=" _
    & """URL;http://finance.yahoo.com/q/hp?s=" & stock & "+Historical+Prices""" _
    & ", Destination:=WSD.Range(""$A$10""))"

The roll over message tells me:  ConnectString=Nothing
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33620323
Have you tried recording a macro of importing the website and then editing the recorded connection with the variables?
Seems like it would be much easier than you or I debugging the code.
0
 

Author Comment

by:rosse_f
ID: 33620396
Sorry, I seem to jumping the gun on you.
Following is what I am trying to do.
I have a stock symbol on a worksheet "portfolio.
I want to select the stock symbol and retrieve the historical data on the worksheet "Workspace"
The historical data is used in a calculation and is then deleted.
A new stock symbol is then selected and new historical data is loaded into the worksheet "workspace"

For some reason I am having trouble getting the stock symbol inserted into the query.
0
 

Author Comment

by:rosse_f
ID: 33620430
Yes, I have recorded a macro, but this gives me the stock symbol hard coded into the subroutine.
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33620463
record a macro of you querying the website using one of the symbols.....afterword......use the recorded code to insert the stock vairbale into the query........much easier this way......
Just add your vaible declaraions, input box and what not, or you variable sets and replace the stock symbol in the query with:
" & stock & "
The recording will give you the query table syntax......
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33620479
record it using one of your symbols and then post the code here....I'll edit in the symbol variable and other stuff using what you have above....
0
 

Author Comment

by:rosse_f
ID: 33622285
This is the recorded macro:
Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://finance.yahoo.com/q/hp?s=MO+Historical+Prices", Destination:= _
        Range("$A$10"))
        .Name = "hp?s=MO+Historical+Prices_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """yfncsubtit"",16"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

I need to have the subroutine read and paste the stock symbol into the code.  The use of an input box will not serve our needs.  
0
 

Author Comment

by:rosse_f
ID: 33622358
It was a good tip you gave me.  I recorded the macro and inserted  " & stock & "  into the connection string and it worked.  
I cannot thank you enough
0
 

Author Comment

by:rosse_f
ID: 33622451
I want to award the 500 points to MWGainesJR for helping me with this solution.
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33626401
The author is requesting the question be reopened so that points can be assigned.
0
 

Author Comment

by:rosse_f
ID: 33627192
Please award the 500 points to MWGainesJR
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33627220
Rosse, you'll need to do that.  You should be able to now.
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33627379
you still didn't allocate the points.......you need to accept one of my comments as the solution.  You don't need to request a close.......
0
 

Author Closing Comment

by:rosse_f
ID: 33627510
Complete solution
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33627531
You accepted yourself as the solution.   ?????
0
 

Author Comment

by:rosse_f
ID: 33891245
accept solution, I have tried to close question at least a dozen times
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now