asked on

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

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

ASKER CERTIFIED SOLUTION

MWGainesJR

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

rosse_f

ASKER

Do you have a suggestion on what to do?

MWGainesJR

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

rosse_f

ASKER

Please show me how to correct the errors, I don't know what to do.

MWGainesJR

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.

rosse_f

ASKER

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

MWGainesJR

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.

rosse_f

ASKER

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.

rosse_f

ASKER

Yes, I have recorded a macro, but this gives me the stock symbol hard coded into the subroutine.

MWGainesJR

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......

MWGainesJR

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....

rosse_f

ASKER

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.

rosse_f

ASKER

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

rosse_f

ASKER

I want to award the 500 points to MWGainesJR for helping me with this solution.

MWGainesJR

The author is requesting the question be reopened so that points can be assigned.

rosse_f

ASKER

Please award the 500 points to MWGainesJR

MWGainesJR