testRev2.xlsm
I've written a module (below and also in the attached excel file) to read a series of *.csv files (which I access online) and insert into a spreadsheet. I'm kind of stuck on some basic VBA syntax, specifically, how to concatenate the string "C" with the integer I, to form a string which can then define 'qrow' which is the cell location to write each line of data to. Any insights would be greatly appreciated. Thanks!
Sub GetData()
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim qrow As String
Dim i As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
i = 128
Set DataSheet = ActiveSheet
Range("C7").CurrentRegion.ClearContents
qurl = Cells(i, 14)
For i = 128 To 7 Step -1
qrow = "C" + string(i)
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("qrow"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range("qrow").CurrentRegion.TextToColumns Destination:=Range("qrow"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
Next i
End Sub
basically, I'm looping from row 128 to row 7 in the excel file and trying to write the downloaded data to cell location C128, C127... C7.
Bookmarks