add one connection

T

tlindeman

Active Member
Joined
Jun 29, 2005
Messages
313
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
You could do a for loop and do something like:

Code: 
    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.
 
Upvote 0
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
 
Upvote 0
Does this work:

Code: 
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 -->
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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))

:oops:
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA to add to data model when creating Power Query tables`
Replies
3
Views
577
Raddle
R
S
  • Solved
QueryTables bug. If one query fails, all subsequent queries will fail unless restart Excel
Replies
4
Views
418
Micron
M
K
  • Question Question
Scraping a table from a website, VBA code needs to be updated, running into issues
Replies
1
Views
550
Dan_W
D
A
  • Question Question
Getting table from a link from a cell value
Replies
0
Views
535
adipat
A
A
  • Question Question
Table is not downloading to excel
Replies
0
Views
532
adipat
A

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top