Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving Rowes based on Date value with a macro | Excel Worksheet Functions | |||
Macro help: moving specific cells based upon content | Excel Worksheet Functions | |||
Moving selection right one column at a time: Is there a shortcut? | New Users to Excel | |||
Selection Based Pivot table in a macro | Excel Programming | |||
Macro to change list box input range based on selection made in another cell | Excel Programming |