PDA

View Full Version : Solved: trying to import csv in macro from web.



grichey
05-19-2008, 01:08 PM
Hello,
I am trying to import the following into excel using the below I found on another thread. It's a csv but when it pulls into excel, it all comes in as a 1 cell per row ie, row 1 is all in A1, row 2 is all in B1 etc and isn't split up. How would I modify the below to actually split it up by the commas?
Thanks,
Gavin


Sub gethtmltable()
Dim objWeb As QueryTable
Dim airPortCode As String
'airPortCode = "KCSG"


Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;http://www.wunderground.com/history/airport/KDAL/2008/1/1/CustomHistory.html?dayend=31&monthend=1&yearend=2008&req_city=NA&req_state=NA&req_statename=NA&format=1", _
Destination:=Range("A1"))

With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = "1" ' Identify your HTML Table here
.Refresh BackgroundQuery:=False
.SaveData = True
End With



End Sub

grichey
05-19-2008, 01:43 PM
The end goal by the way is taking a few of these fields and transposing them left to right to get a table with airport code in column A and the days weather variable (ex. wind) in the next 31 columns out to the right. After I solve this, I will be trying to figure out how to change the airport code to a list of 200 to read them all in sequentially.

grichey
05-20-2008, 05:11 AM
anyone??

Charlize
05-20-2008, 05:54 AM
To get you going.Sub split_info()
Dim cell As Range
Dim myrange As Range
Dim mystring As String
Dim mypos As Long
Set myrange = ActiveSheet.Range("A2:A" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row)
For Each cell In myrange
mystring = cell.Value
For mypos = LBound(Split(mystring, ",")) To UBound(Split(mystring, ","))
cell.Offset(0, mypos) = Split(mystring, ",")(mypos)
Next mypos
Next cell
Range("A2", Range("A2").End(xlToRight)).Columns.AutoFit
Range("A:A").Columns.AutoFit
MsgBox "Reformatting info done !", vbInformation
End SubCharlize

grichey
05-20-2008, 06:56 AM
Thanks. I'll give it a try later on today.

grichey
05-21-2008, 07:43 AM
Here is the finished Code which goes out a website and finds weather based on airport code and brings in weather data for each airport in the 'Airport Codes' worksheet listed in column A beginning row 2. Thanks for the help. Thanks for the help!

Sub getweathertable()
'For reading in from web
Dim objWeb As QueryTable
Dim varAirPortCode As String
Dim varMonth As String
Dim varDaysInMonth As String
'For splitting
Dim cell As Range
Dim myrange As Range
Dim mystring As String
Dim mypos As Long

Dim counter As Integer
counter = 2


'Set varAirPortCode starting Position
Sheets("Airport Codes").Select
Range("A" & counter).Select
varAirPortCode = Selection


'Get month and days in month
varMonth = InputBox(prompt:="Enter Month like 1", Title:="Enter Month like 1")
varDaysInMonth = InputBox(prompt:="Enter days in month like 29", Title:="Enter days in month like 29")

'Loop
Do
Sheets("Working").Select
'Needs to rotate through AirPortCode list
'varAirPortCode = "KCSG" Hard code for testing purposes

'Get info from web
Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;http://websitethatsupportscsvdownload.com/history/airport/" & varAirPortCode & "/2008/" & varMonth & "/1/CustomHistory.html?dayend=" & varDaysInMonth & "&monthend=1&yearend=2008&req_city=NA&req_state=NA&req_statename=NA&format=1", _
Destination:=Range("A1"))

With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = "1"
.Refresh BackgroundQuery:=False
.SaveData = True
End With

'Split Data Up into Columns
Set myrange = ActiveSheet.Range("A2:A" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row)
For Each cell In myrange
mystring = cell.Value
For mypos = LBound(Split(mystring, ",")) To UBound(Split(mystring, ","))
cell.Offset(0, mypos) = Split(mystring, ",")(mypos)
Next mypos
Next cell
Range("A2", Range("A2").End(xlToRight)).Columns.AutoFit
Range("A:A").Columns.AutoFit

'Copy Visibility
Range("O1:O33").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Visibility").Select
Range("A" & counter).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Sheets("Working").Select

'Copy Wind
Range("R1:R33").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Wind").Select
Range("A" & counter).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Working").Select

'Copy Precipitation
Range("T1:T33").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Precipitation").Select
Range("A" & counter).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True



'Copy Dates
Sheets("Working").Select
Range("A1:A33").Select
Selection.Copy
Sheets("Visibility").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Wind").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Precipitation").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Sheets("Working").Select
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete

Sheets("Airport Codes").Select
counter = counter + 1
Range("A" & counter).Select
varAirPortCode = Selection

Loop Until IsEmpty(Selection.Offset(1, 0))


'**Needs to loop back to beginning here and switch to next airport code**


MsgBox "Done", vbInformation










End Sub