Moving Selection and Time based run macro
 

Hi All,

I am in the midst of writing a macro, which extracts data I requir
form the Internet and puts it into a spreadsheet. I can get the macr
to work; currently there are two problems:

1. The data on the Internet appears to change position when opened i
excel. I have come up with a way round this by doing a find "MM" an
the data I need is in the area 22cells deep by 19cells wide. In th
macro below I have the selection working up to the point where th
active cell is the first cell in the table of data required and i
unable to select the table. Although I know the size of the table I d
not know that it will always be the same cell reference except that i
will be below the "MM" reference, is there a way to select this dat
giving reference to the area form the active cell?

Windows("Wave Buoy Data2.xls").Activate
Sheets("DO NOT CHANGE").Activate
Range("A2").Select
Selection.Copy
Application.CutCopyMode = False
Workbooks.Open Filename:= _
"http://www.ndbc.noaa.gov/station_page.php?station=64046%3E"
Range("C7").Select
Cells.Find(What:="MM", After:=ActiveCell, LookIn:=xlFormulas, LookAt:
_
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext
MatchCase:=False) _
.Activate
Selection.End(xlDown).Select


2. Below is the full macro, which I am trying to run every 22 hours. I
does not appear to be working? Not sure if the sort in causing th
problem or whether it is the way I have the file set up to run?

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long
Public TimerSeconds As Single

Sub StartTimer()
TimerSeconds = 1 ' how often to "pop" the timer in HOURS.
TimerID = SetTimer(0&, 0&, TimerSeconds * 3600000, AddressOf TimerProc

End Sub

Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
'
' The procedure is called by Windows. Put your
' timer-related code here.
'
Windows("Wave Buoy Data.xls").Activate
Sheets("DO NOT CHANGE").Activate
Range("A2").Select
Selection.Copy
Application.CutCopyMode = False
Workbooks.Open Filename:= _
"http://www.ndbc.noaa.gov/station_page.php?station=64046%3E"
Range("C42:U63").Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Windows("Wave Buoy Data.xls").Activate
Sheets("DO NOT CHANGE").Activate
Range("A12").Select
ActiveSheet.Paste
Range("A12:T33").Select
Selection.Copy
Sheets("K7 Data").Select
Dim i As Integer
i = 1
Do Until Range("B" & i).Value = ""
i = i + 1
Loop
Range("B" & i).Activate
ActiveSheet.Paste
Range("A3:A24").Select
Selection.Copy
Do Until Range("A" & i).Value = ""
i = i + 1
Loop
Range("A" & i).Activate
ActiveSheet.Paste
Range("A25:T65500").Select
Selection.Sort Key1:=Range("A25"), Order1:=xlAscending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.SaveWorkspace ("WAve Buoy Data2")
End Sub

Hope there is an answer and thanks for taking time to look and help.
Iarl

--
Iarl
-----------------------------------------------------------------------
Iarla's Profile: http://www.excelforum.com/member.php...fo&userid=1656
View this thread: http://www.excelforum.com/showthread.php?threadid=31871