add one connection

[tlindeman]

I have a query that adds 56 connections to a website and imports that data into excel. Is there a way to open one connection and edit the connection each time. For example I have a ticker in A1, A232...A1151. I would like the query to look at A1 pull in the data, and then edit the connection and look at A232, pull in the data. Here is a sample of my code. Any help is appreciated.

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.google.com/finance?q=" & Range("A1").Value & "&fstype=ii", Destination:=Range( _
"$B$1"))
.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
ActiveWorkbook.Connections("connection").Delete
End With

 

[schielrn]

You could do a for loop and do something like:

    for i = 1 to 1151 step 231
         'code goes here
    next i
    For Each cn In ThisWorkbook.Connections
        cn.Delete
    Next cn

Then change:

Range("A1").Value

to:

Range("A" & i).Value

This will also remove all the connections when it is finished.

Hope that helps.

 

[tlindeman]

I will give it a shot, thank you

 

[tlindeman]

Ok, I am getting a globals failed error. Here is my code, please point out what I have wrong.

Sheets("Income Statement").Select
Call clear_income_statement
For i = 1 To 1151 Step 231
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.google.com/finance?q=" & Range("A" & i).Value & "&fstype=ii", Destination:=Range(Range("B" & i).Value))
.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
End With
Next i
For Each cn In ThisWorkbook.Connections
cn.Delete
Next cn

 

[schielrn]

Does this work:

Sheets("Income Statement").Select
Call clear_income_statement
For i = 1 To 1151 Step 231
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.google.com/finance?q=" & Range("A" & i).Value & "&fstype=ii", Destination:=Range("B" & i)
.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
End With
Next i
For Each cn In ThisWorkbook.Connections
cn.Delete
Next cn

<!-- / message -->

 

[schielrn]

Note that this is only going to run about 4 times since it will run at 1 then 232 then 463, etc... up only until 1151 since that is what you have specified.

 

[tlindeman]

I am getting a syntax error and it is highlighting the following code:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.google.com/finance?q=" & Range("A" & i).Value & "&fstype=ii", Destination:=Range("B" & i)

 

[schielrn]

I forgot a close parentheses. Put a ) at the very end:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.google.com/finance?q=" & Range("A" & i).Value & "&fstype=ii", Destination:=Range("B" & i))

 

[tlindeman]

Works perfect, thank you very much for your help