Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
build a list with timestamps
Hi,
On one worksheet (Sheet1) I have a named range ("SumDaily") which is pulling data from an outside source that updates automatically. I would like to capture this data once every minute, paste as values (transposed) on Sheet3 with a time stamp. I want to append it each time to the bottom of the list so that at the end of a 6.5 hour period I have 390 rows of data with respective timestamps (Row 1 has headers, Column A would be time stamp). Below is a recorded version of how I would do it for one line, but I don't know how to append it to the bottom of a list, nor do I know how to make it run automatically every 60 seconds (relative beginner with VBA). I'm using Excel 2007, Windows XP. Sub Macro1() 'Ideally I don't want it to activate the workbook since I want to be working in other applications or other workbooks while it runs Windows("MyWorkbook.xlsx").Activate Sheets("Sheet1").Select Application.Goto Reference:="SumDaily" Selection.Copy Sheets("Sheet3").Select Range("B2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Range("A2").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False End Sub A bonus would be to make it run only during market hours (9:30 AM - 4:00 PM), but this isn't necessary since I can just open and close the workbook at those times. Many thanks! Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
build a list with timestamps
Sub GetData()
'Ideally I don't want it to activate the workbook since I want to be 'working in other applications or other workbooks while it runs MyTime = Time If MyTime = TimeValue("6:30AM") And _ MyTime <= TimeValue("4:00PM") Then With ThisWorkbook Range("SumDaily").Copy 'Application.CutCopyMode = False With .Sheets("Sheet3") LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 .Range("B" & NewRow).PasteSpecial _ Paste:=xlPasteValues, _ Transpose:=True .Range("A" & NewRow) = MyTime End With End With End If Application.OnTime Now + TimeValue("00:01:00"), "GetData" End Sub " wrote: Hi, On one worksheet (Sheet1) I have a named range ("SumDaily") which is pulling data from an outside source that updates automatically. I would like to capture this data once every minute, paste as values (transposed) on Sheet3 with a time stamp. I want to append it each time to the bottom of the list so that at the end of a 6.5 hour period I have 390 rows of data with respective timestamps (Row 1 has headers, Column A would be time stamp). Below is a recorded version of how I would do it for one line, but I don't know how to append it to the bottom of a list, nor do I know how to make it run automatically every 60 seconds (relative beginner with VBA). I'm using Excel 2007, Windows XP. Sub Macro1() 'Ideally I don't want it to activate the workbook since I want to be working in other applications or other workbooks while it runs Windows("MyWorkbook.xlsx").Activate Sheets("Sheet1").Select Application.Goto Reference:="SumDaily" Selection.Copy Sheets("Sheet3").Select Range("B2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Range("A2").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False End Sub A bonus would be to make it run only during market hours (9:30 AM - 4:00 PM), but this isn't necessary since I can just open and close the workbook at those times. Many thanks! Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
build a list with timestamps
Works great!!! Thanks.
One question...the time stamp includes a date (1/0/1900), depending on how I format it. Is there a way to include today's date with the time? Thank you Steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
build a list with timestamps
I made 3 changes
1) Added Date to MyTime when saving data to worksheet .Range("A" & NewRow) = MyTime + Date Date is the midnight time and Mytime is only the hours, minutes and seconds. 2) Changed Start time to 9:30. For testing I was using 6:30 and forgot to change the time before posting 3) I changed the end time to <4:01PM so the 4:00PM time gets posted. Otherwise the last posting would be 3:59PM. Note: You can add a workbook open event to automactically start the macro. Put this code in the Thisworkbook VBA sheet. Private Sub Workbook_Open() Call GetData End Sub Sub GetData() 'Ideally I don't want it to activate the workbook since I want to be 'working in other applications or other workbooks while it runs If MyTime = TimeValue("9:30AM") And _ MyTime < TimeValue("4:01PM") Then With ThisWorkbook Range("SumDaily").Copy 'Application.CutCopyMode = False With .Sheets("Sheet3") LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 .Range("B" & NewRow).PasteSpecial _ Paste:=xlPasteValues, _ Transpose:=True .Range("A" & NewRow) = MyTime + Date End With End With End If Application.OnTime Now + TimeValue("00:01:00"), "GetData" End Sub " wrote: Works great!!! Thanks. One question...the time stamp includes a date (1/0/1900), depending on how I format it. Is there a way to include today's date with the time? Thank you Steve |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
build a list with timestamps
Thanks, Joel. Appreciate the changes. One question...if I'm working
in another workbook while this is running in the background, it gives me an error. I assume it's because of the "ThisWorkbook" code. Any thoughts on how to get around this? Thanks Steve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
build a list with timestamps
I got an e-mail this afternoon indicating there was a new posting, yet when I
looked there is no message. "Joel" wrote: I made 3 changes 1) Added Date to MyTime when saving data to worksheet .Range("A" & NewRow) = MyTime + Date Date is the midnight time and Mytime is only the hours, minutes and seconds. 2) Changed Start time to 9:30. For testing I was using 6:30 and forgot to change the time before posting 3) I changed the end time to <4:01PM so the 4:00PM time gets posted. Otherwise the last posting would be 3:59PM. Note: You can add a workbook open event to automactically start the macro. Put this code in the Thisworkbook VBA sheet. Private Sub Workbook_Open() Call GetData End Sub Sub GetData() 'Ideally I don't want it to activate the workbook since I want to be 'working in other applications or other workbooks while it runs If MyTime = TimeValue("9:30AM") And _ MyTime < TimeValue("4:01PM") Then With ThisWorkbook Range("SumDaily").Copy 'Application.CutCopyMode = False With .Sheets("Sheet3") LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 .Range("B" & NewRow).PasteSpecial _ Paste:=xlPasteValues, _ Transpose:=True .Range("A" & NewRow) = MyTime + Date End With End With End If Application.OnTime Now + TimeValue("00:01:00"), "GetData" End Sub " wrote: Works great!!! Thanks. One question...the time stamp includes a date (1/0/1900), depending on how I format it. Is there a way to include today's date with the time? Thank you Steve |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
build a list with timestamps
On Dec 23, 3:26*pm, wrote:
Thanks, Joel. *Appreciate the changes. *One question...if I'm working in another workbook while this is running in the background, it gives me an error. *I assume it's because of the "ThisWorkbook" code. *Any thoughts on how to get around this? Thanks Steve See above. I get an error if I'm working in another workbook. Thanks Steve |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
build a list with timestamps
This website has been down since the evening of the 23rd. Just came back up
this morning. You can always set the workbook by using a variable like from with thisworkbook to Set Bk = activeworkbook with BK or specifically calling out the workbook name Set Bk = workbooks("book1.xls") with BK " wrote: On Dec 23, 3:26 pm, wrote: Thanks, Joel. Appreciate the changes. One question...if I'm working in another workbook while this is running in the background, it gives me an error. I assume it's because of the "ThisWorkbook" code. Any thoughts on how to get around this? Thanks Steve See above. I get an error if I'm working in another workbook. Thanks Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I build a custom list in Excel | Excel Discussion (Misc queries) | |||
help to build a list of holidays for 6 years | Excel Worksheet Functions | |||
Trying to build list based on cell value | Excel Discussion (Misc queries) | |||
how do I build a drop-down list in Excel? | Excel Worksheet Functions | |||
Worksheet Change to build product list | Excel Programming |