[Natalie]

Hello,

I'm trying to work smarter. I want to learn how to create/use a macro to do
the following. I need to click on an IE link, cut and paste into Excel, and
return to the previous IE page, click the next link, cut and paste into Excel
slightly below the previous record, repeat for about 488 records. Any
suggestions?

The first page is:
http://cdec.water.ca.gov/cgi-progs/s...&display=staid

The second page is: (changing - that I cut and paste from)
http://cdec.water.ca.gov/cgi-progs/s...station_id=ABR

Rinse, repeat 487 times more. Any suggestions?

Thank you.

Natalie Vane
916-631-4548

[joel]

Here is what you do.

Go to Data menu - Import External Data - New Web Query
Copy and Paste your URL in the address box at top of query and press Go
Click the yellow Arrow pointing to the data table.
Then press Import on the boom right corner of the window.
Select the Destination cell where you want the data to go.


You can Record a Macro while performing these operations which will record a
macro that you can play back. You can edit the macro as required to make
changes.

"Natalie" wrote:

Hello,

I'm trying to work smarter. I want to learn how to create/use a macro to do
the following. I need to click on an IE link, cut and paste into Excel, and
return to the previous IE page, click the next link, cut and paste into Excel
slightly below the previous record, repeat for about 488 records. Any
suggestions?

The first page is:
http://cdec.water.ca.gov/cgi-progs/s...&display=staid

The second page is: (changing - that I cut and paste from)
http://cdec.water.ca.gov/cgi-progs/s...station_id=ABR

Rinse, repeat 487 times more. Any suggestions?

Thank you.

Natalie Vane
916-631-4548

[joel]

I did the macro for you. If you put the first Page on Sheet1 of the workbook
with the header on Row1 and the first station on row 2 the code below will
get each Station data and put it in Sheet2 with a single space between each
station.

The code takes a while to run. I only did about 80 stations in 5 minutes.
I did what I suggested in my lst posting. I recorded a macro for the first
station. then made some monor changes to make it work in a loop and repeat
for each station listd in Page 1.



Sub GetStations()

URL = "URL;http://cdec.water.ca.gov/cgi-progs/staMeta?station_id="

With Sheets("Sheet1")
RowCount = 2
Do While .Range("A" & RowCount) < ""

Station_ID = .Range("A" & RowCount)

With Sheets("Sheet2")
If .Range("A1") = "" Then
NewRow = 1
Else
LastRow = .Range("A" & Rows.Count) _
.End(xlUp).Row
NewRow = LastRow + 2
End If

With .QueryTables.Add(Connection:= _
URL & Station_ID, _
Destination:=Range("A" & NewRow))

.Name = "staMeta?station_id=" & Station_ID
.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 = "1"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End With


RowCount = RowCount + 1
Loop
End With


End Sub


"Joel" wrote:

Here is what you do.

Go to Data menu - Import External Data - New Web Query
Copy and Paste your URL in the address box at top of query and press Go
Click the yellow Arrow pointing to the data table.
Then press Import on the boom right corner of the window.
Select the Destination cell where you want the data to go.


You can Record a Macro while performing these operations which will record a
macro that you can play back. You can edit the macro as required to make
changes.

"Natalie" wrote:

Hello,

I'm trying to work smarter. I want to learn how to create/use a macro to do
the following. I need to click on an IE link, cut and paste into Excel, and
return to the previous IE page, click the next link, cut and paste into Excel
slightly below the previous record, repeat for about 488 records. Any
suggestions?

The first page is:
http://cdec.water.ca.gov/cgi-progs/s...&display=staid

The second page is: (changing - that I cut and paste from)
http://cdec.water.ca.gov/cgi-progs/s...station_id=ABR

Rinse, repeat 487 times more. Any suggestions?

Thank you.

Natalie Vane
916-631-4548

[joel]

Her is the final part of the job. The code below will take the data
downloaded on sheet 2 and put the data in a table on Sheet3. This code runs
very quickly.



Sub MakeTable()

'Create Headers
With Sheets("Sheet3")
.Range("A1") = "Station ID"
.Range("B1") = "Elevation"
.Range("C1") = "River Basin"
.Range("D1") = "County"
.Range("E1") = "Hydrologic Area"
.Range("F1") = "Nearby City"
.Range("G1") = "Latitude"
.Range("H1") = "Longitude"
.Range("I1") = "Operator"
.Range("J1") = "Data Collection"

End With
With Sheets("Sheet2")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Sh3RowCount = 2
For Sh2RowCount = 1 To LastRow Step 6
Sh3Col = 1
For StationRow = Sh2RowCount To (Sh2RowCount + 4)
For StationCol = 2 To 4 Step 2
Sheets("Sheet3").Cells(Sh3RowCount, Sh3Col) = .Cells(StationRow,
StationCol)
Sh3Col = Sh3Col + 1
Next StationCol
Next StationRow
Sh3RowCount = Sh3RowCount + 1
Next Sh2RowCount
End With

End Sub


"Joel" wrote:

I did the macro for you. If you put the first Page on Sheet1 of the workbook
with the header on Row1 and the first station on row 2 the code below will
get each Station data and put it in Sheet2 with a single space between each
station.

The code takes a while to run. I only did about 80 stations in 5 minutes.
I did what I suggested in my lst posting. I recorded a macro for the first
station. then made some monor changes to make it work in a loop and repeat
for each station listd in Page 1.



Sub GetStations()

URL = "URL;http://cdec.water.ca.gov/cgi-progs/staMeta?station_id="

With Sheets("Sheet1")
RowCount = 2
Do While .Range("A" & RowCount) < ""

Station_ID = .Range("A" & RowCount)

With Sheets("Sheet2")
If .Range("A1") = "" Then
NewRow = 1
Else
LastRow = .Range("A" & Rows.Count) _
.End(xlUp).Row
NewRow = LastRow + 2
End If

With .QueryTables.Add(Connection:= _
URL & Station_ID, _
Destination:=Range("A" & NewRow))

.Name = "staMeta?station_id=" & Station_ID
.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 = "1"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End With


RowCount = RowCount + 1
Loop
End With


End Sub


"Joel" wrote:

Here is what you do.

Go to Data menu - Import External Data - New Web Query
Copy and Paste your URL in the address box at top of query and press Go
Click the yellow Arrow pointing to the data table.
Then press Import on the boom right corner of the window.
Select the Destination cell where you want the data to go.


You can Record a Macro while performing these operations which will record a
macro that you can play back. You can edit the macro as required to make
changes.

"Natalie" wrote:

Hello,

I'm trying to work smarter. I want to learn how to create/use a macro to do
the following. I need to click on an IE link, cut and paste into Excel, and
return to the previous IE page, click the next link, cut and paste into Excel
slightly below the previous record, repeat for about 488 records. Any
suggestions?

The first page is:
http://cdec.water.ca.gov/cgi-progs/s...&display=staid

The second page is: (changing - that I cut and paste from)
http://cdec.water.ca.gov/cgi-progs/s...station_id=ABR

Rinse, repeat 487 times more. Any suggestions?

Thank you.

Natalie Vane
916-631-4548