| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding todays date to new records in Excel
HI
I hope someone can help. I run Macro in Excel to add new records from a text file to the bottom of an excel sheet on a daily basis. Therefore the number of records in the sheet grows daily. There is data in each column up to column K. I would like to be able to add todays (being the day the data is added to the sheet) date to Column L when each new record is added while running the Macro. Is this possible. Thanks for any help Eddie |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding todays date to new records in Excel
Hi Mike
If Rnum is the row number you copy to This will add the date Cells(Rnum,"L").Value = Date -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "mikerobe" wrote in message ... HI I hope someone can help. I run Macro in Excel to add new records from a text file to the bottom of an excel sheet on a daily basis. Therefore the number of records in the sheet grows daily. There is data in each column up to column K. I would like to be able to add todays (being the day the data is added to the sheet) date to Column L when each new record is added while running the Macro. Is this possible. Thanks for any help Eddie |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding todays date to new records in Excel
Do you really want the date duplicated on every new row, or just once at the
start of a new day's data? It would be helpful if you included your present macro so we could modify it to work properly. Mike F "mikerobe" wrote in message ... HI I hope someone can help. I run Macro in Excel to add new records from a text file to the bottom of an excel sheet on a daily basis. Therefore the number of records in the sheet grows daily. There is data in each column up to column K. I would like to be able to add todays (being the day the data is added to the sheet) date to Column L when each new record is added while running the Macro. Is this possible. Thanks for any help Eddie |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding todays date to new records in Excel
Thanks Mike and Ron
Here is the Macro which I should have included first time Sub klm() Workbooks.OpenText Filename:="M:\Statdata\klm.txt", Origin:=xlMSDOS, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _ Array(10, 1)), TrailingMinusNumbers:=True, Local:=True '<- this decides date interpretation Selection.Sort Key1:=Range("H1"), Order1:=xlDescending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("1:1").Select Selection.Insert Shift:=xlDown Cells.Select Selection.AutoFilter Selection.AutoFilter Field:=11, Criteria1:="=STAU" Sheets.Add Sheets("klm").Select Rows("1:6000").Select Selection.Copy Sheets("Sheet1").Select Range("A1").Select ActiveSheet.Paste Rows("2:6000").Select Selection.Copy Workbooks.Open Filename:= _ "G:\J\klm.xls", Origin:=xlWindows Range("A65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste Cells.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("A:A").Select Set Rng = ActiveSheet R = 1 N = 1 With Rng LastRow = .Range("A" & Rows.Count).End(xlUp).Row Do While N <= LastRow If R Mod 500 = 0 Then Application.StatusBar = "Processing Row: " & Format(R, "#,##0") End If V = .Range("A" & R).Value If V = vbNullString Then If Application.WorksheetFunction. _ CountIf(.Columns(1), vbNullString) 1 Then .Rows(R).Delete End If Else Next_V = .Range("A" & (R + 1)).Value If V = Next_V Then Thisdate = .Range("H" & R).Value NextDate = .Range("H" & (R + 1)).Value If Thisdate < NextDate Then .Rows(R + 1).Delete Else .Rows(R).Delete End If Else R = R + 1 End If End If N = N + 1 Loop End With Cells.Select Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveWorkbook.Save End Sub Thanks for offering your help Eddie On Jun 13, 10:34*pm, "Mike Fogleman" wrote: Do you really want the date duplicated on every new row, or just once at the start of a new day's data? It would be helpful if you included your present macro so we could modify it to work properly. Mike F"mikerobe" wrote in message ... HI I hope someone can help. I run Macro in Excel to add new records from a text file to the bottom of an excel sheet on a daily basis. Therefore the number of records in the sheet grows daily. There is data in each column up to column K. I would like to be able to add todays (being the day the data is added to the sheet) date to Column L when each new record is added while running the Macro. Is this possible. Thanks for any help Eddie- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding todays date to new records in Excel
I would think the best time to put the dates is right after you copy the
text to sheet1 in the text file, and before you copy that to your workbook. Here is a snippet from your code with the lines added to put the Date in column L as far down as there is data in column K. Rows("1:1").Select Selection.Insert Shift:=xlDown Cells.Select Selection.AutoFilter Selection.AutoFilter Field:=11, Criteria1:="=STAU" Sheets.Add Sheets("klm").Select Rows("1:6000").Select Selection.Copy Sheets("Sheet1").Select Range("A1").Select ActiveSheet.Paste Range("L1").Value = Date Range("L1", Range("K1").End(xlDown)).Offset(0, 1).FillDown Mike F "mikerobe" wrote in message ... Thanks Mike and Ron Here is the Macro which I should have included first time Sub klm() Workbooks.OpenText Filename:="M:\Statdata\klm.txt", Origin:=xlMSDOS, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _ Array(10, 1)), TrailingMinusNumbers:=True, Local:=True '<- this decides date interpretation Selection.Sort Key1:=Range("H1"), Order1:=xlDescending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("1:1").Select Selection.Insert Shift:=xlDown Cells.Select Selection.AutoFilter Selection.AutoFilter Field:=11, Criteria1:="=STAU" Sheets.Add Sheets("klm").Select Rows("1:6000").Select Selection.Copy Sheets("Sheet1").Select Range("A1").Select ActiveSheet.Paste Rows("2:6000").Select Selection.Copy Workbooks.Open Filename:= _ "G:\J\klm.xls", Origin:=xlWindows Range("A65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste Cells.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("A:A").Select Set Rng = ActiveSheet R = 1 N = 1 With Rng LastRow = .Range("A" & Rows.Count).End(xlUp).Row Do While N <= LastRow If R Mod 500 = 0 Then Application.StatusBar = "Processing Row: " & Format(R, "#,##0") End If V = .Range("A" & R).Value If V = vbNullString Then If Application.WorksheetFunction. _ CountIf(.Columns(1), vbNullString) 1 Then .Rows(R).Delete End If Else Next_V = .Range("A" & (R + 1)).Value If V = Next_V Then Thisdate = .Range("H" & R).Value NextDate = .Range("H" & (R + 1)).Value If Thisdate < NextDate Then .Rows(R + 1).Delete Else .Rows(R).Delete End If Else R = R + 1 End If End If N = N + 1 Loop End With Cells.Select Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveWorkbook.Save End Sub Thanks for offering your help Eddie On Jun 13, 10:34 pm, "Mike Fogleman" wrote: Do you really want the date duplicated on every new row, or just once at the start of a new day's data? It would be helpful if you included your present macro so we could modify it to work properly. Mike F"mikerobe" wrote in message ... HI I hope someone can help. I run Macro in Excel to add new records from a text file to the bottom of an excel sheet on a daily basis. Therefore the number of records in the sheet grows daily. There is data in each column up to column K. I would like to be able to add todays (being the day the data is added to the sheet) date to Column L when each new record is added while running the Macro. Is this possible. Thanks for any help Eddie- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding todays date to new records in Excel
On Jun 14, 2:30 pm, "Mike Fogleman" wrote:
I would think the best time to put the dates is right after you copy the text to sheet1 in the text file, and before you copy that to your workbook. Here is a snippet from your code with the lines added to put the Date in column L as far down as there is data in column K. Rows("1:1").Select Selection.Insert Shift:=xlDown Cells.Select Selection.AutoFilter Selection.AutoFilter Field:=11, Criteria1:="=STAU" Sheets.Add Sheets("klm").Select Rows("1:6000").Select Selection.Copy Sheets("Sheet1").Select Range("A1").Select ActiveSheet.Paste Range("L1").Value = Date Range("L1", Range("K1").End(xlDown)).Offset(0, 1).FillDown Mike F"mikerobe" wrote in message ... Thanks Mike and Ron Here is the Macro which I should have included first time Sub klm() Workbooks.OpenText Filename:="M:\Statdata\klm.txt", Origin:=xlMSDOS, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _ Array(10, 1)), TrailingMinusNumbers:=True, Local:=True '<- this decides date interpretation Selection.Sort Key1:=Range("H1"), Order1:=xlDescending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("1:1").Select Selection.Insert Shift:=xlDown Cells.Select Selection.AutoFilter Selection.AutoFilter Field:=11, Criteria1:="=STAU" Sheets.Add Sheets("klm").Select Rows("1:6000").Select Selection.Copy Sheets("Sheet1").Select Range("A1").Select ActiveSheet.Paste Rows("2:6000").Select Selection.Copy Workbooks.Open Filename:= _ "G:\J\klm.xls", Origin:=xlWindows Range("A65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste Cells.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("A:A").Select Set Rng = ActiveSheet R = 1 N = 1 With Rng LastRow = .Range("A" & Rows.Count).End(xlUp).Row Do While N <= LastRow If R Mod 500 = 0 Then Application.StatusBar = "Processing Row: " & Format(R, "#,##0") End If V = .Range("A" & R).Value If V = vbNullString Then If Application.WorksheetFunction. _ CountIf(.Columns(1), vbNullString) 1 Then .Rows(R).Delete End If Else Next_V = .Range("A" & (R + 1)).Value If V = Next_V Then Thisdate = .Range("H" & R).Value NextDate = .Range("H" & (R + 1)).Value If Thisdate < NextDate Then .Rows(R + 1).Delete Else .Rows(R).Delete End If Else R = R + 1 End If End If N = N + 1 Loop End With Cells.Select Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveWorkbook.Save End Sub Thanks for offering your help Eddie On Jun 13, 10:34 pm, "Mike Fogleman" wrote: Do you really want the date duplicated on every new row, or just once at the start of a new day's data? It would be helpful if you included your present macro so we could modify it to work properly. Mike F"mikerobe" wrote in message ... HI I hope someone can help. I run Macro in Excel to add new records from a text file to the bottom of an excel sheet on a daily basis. Therefore the number of records in the sheet grows daily. There is data in each column up to column K. I would like to be able to add todays (being the day the data is added to the sheet) date to Column L when each new record is added while running the Macro. Is this possible. Thanks for any help Eddie- Hide quoted text - - Show quoted text - Thanks Mike I will give that a go on Monday and tell you how i get on Eddie |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding todays date to new records in Excel
On Jun 14, 2:30*pm, "Mike Fogleman" wrote:
I would think the best time to put the dates is right after you copy the text to sheet1 in the text file, and before you copy that to your workbook.. Here is a snippet from your code with the lines added to put the Date in column L as far down as there is data in column K. *Rows("1:1").Select * * Selection.Insert Shift:=xlDown * * Cells.Select * * Selection.AutoFilter * * Selection.AutoFilter Field:=11, Criteria1:="=STAU" * * Sheets.Add * * Sheets("klm").Select * * Rows("1:6000").Select * * Selection.Copy * * Sheets("Sheet1").Select * * Range("A1").Select * * ActiveSheet.Paste Range("L1").Value = Date Range("L1", Range("K1").End(xlDown)).Offset(0, 1).FillDown Mike F"mikerobe" wrote in message ... Thanks Mike and Ron Here is the Macro which I should have included first time Sub klm() * * Workbooks.OpenText Filename:="M:\Statdata\klm.txt", Origin:=xlMSDOS, _ * * * * StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ * * * * ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _ * * * * , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ * * * * Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _ * * * * Array(10, 1)), TrailingMinusNumbers:=True, Local:=True * '<- this decides date interpretation * * Selection.Sort Key1:=Range("H1"), Order1:=xlDescending, Header:=xlNo, _ * * * * OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ * * * * DataOption1:=xlSortNormal * * Rows("1:1").Select * * Selection.Insert Shift:=xlDown * * Cells.Select * * Selection.AutoFilter * * Selection.AutoFilter Field:=11, Criteria1:="=STAU" * * Sheets.Add * * Sheets("klm").Select * * Rows("1:6000").Select * * Selection.Copy * * Sheets("Sheet1").Select * * Range("A1").Select * * ActiveSheet.Paste Rows("2:6000").Select * * Selection.Copy * * Workbooks.Open Filename:= _ * * * * "G:\J\klm.xls", Origin:=xlWindows Range("A65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste * * Cells.Select * * Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ * * * * OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ * * * * DataOption1:=xlSortNormal * * Columns("A:A").Select * *Set Rng = ActiveSheet R = 1 N = 1 With Rng * *LastRow = .Range("A" & Rows.Count).End(xlUp).Row * *Do While N <= LastRow * * * If R Mod 500 = 0 Then * * * * *Application.StatusBar = "Processing Row: " & Format(R, "#,##0") * * * End If * * * V = .Range("A" & R).Value * * * If V = vbNullString Then * * * * *If Application.WorksheetFunction. _ * * * * * * CountIf(.Columns(1), vbNullString) 1 Then * * * * * * .Rows(R).Delete * * * * *End If * * * Else * * * * *Next_V = .Range("A" & (R + 1)).Value * * * * *If V = Next_V Then * * * * * * Thisdate = .Range("H" & R).Value * * * * * * NextDate = .Range("H" & (R + 1)).Value * * * * * * If Thisdate < NextDate Then * * * * * * * *.Rows(R + 1).Delete * * * * * * Else * * * * * * * *.Rows(R).Delete * * * * * * End If * * * * *Else * * * * * * R = R + 1 * * * * *End If * * * End If * * * N = N + 1 * *Loop End With Cells.Select * * Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ * * * * OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ * * * * DataOption1:=xlSortNormal ActiveWorkbook.Save End Sub Thanks for offering your help Eddie On Jun 13, 10:34 pm, "Mike Fogleman" wrote: Do you really want the date duplicated on every new row, or just once at the start of a new day's data? It would be helpful if you included your present macro so we could modify it to work properly. Mike F"mikerobe" wrote in message ... HI I hope someone can help. I run Macro in Excel to add new records from a text file to the bottom of an excel sheet on a daily basis. Therefore the number of records in the sheet grows daily. There is data in each column up to column K. I would like to be able to add todays (being the day the data is added to the sheet) date to Column L when each new record is added while running the Macro. Is this possible. Thanks for any help Eddie- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Hi Mike This isn't quite working as i hoped. the date is only added to the last record that was added to the worksheet. Ultimately why i need to add the date is to use this date to extract data from multiple worksheets in the same folder to one worksheet in a separate workbook. The original text file is data from a Healthcare patient search. The dates queried go back 2 weeks in case the extract new record macro is not run daily as it normally should be. Hope this is clear Thanks Eddie |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding todays date to new records in Excel
On Jun 15, 6:38*pm, mikerobe wrote:
On Jun 14, 2:30*pm, "Mike Fogleman" wrote: I would think the best time to put the dates is right after you copy the text to sheet1 in the text file, and before you copy that to your workbook. Here is a snippet from your code with the lines added to put the Date in column L as far down as there is data in column K. *Rows("1:1").Select * * Selection.Insert Shift:=xlDown * * Cells.Select * * Selection.AutoFilter * * Selection.AutoFilter Field:=11, Criteria1:="=STAU" * * Sheets.Add * * Sheets("klm").Select * * Rows("1:6000").Select * * Selection.Copy * * Sheets("Sheet1").Select * * Range("A1").Select * * ActiveSheet.Paste Range("L1").Value = Date Range("L1", Range("K1").End(xlDown)).Offset(0, 1).FillDown Mike F"mikerobe" wrote in message ... Thanks Mike and Ron Here is the Macro which I should have included first time Sub klm() * * Workbooks.OpenText Filename:="M:\Statdata\klm.txt", Origin:=xlMSDOS, _ * * * * StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ * * * * ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _ * * * * , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ * * * * Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _ * * * * Array(10, 1)), TrailingMinusNumbers:=True, Local:=True * '<- this decides date interpretation * * Selection.Sort Key1:=Range("H1"), Order1:=xlDescending, Header:=xlNo, _ * * * * OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ * * * * DataOption1:=xlSortNormal * * Rows("1:1").Select * * Selection.Insert Shift:=xlDown * * Cells.Select * * Selection.AutoFilter * * Selection.AutoFilter Field:=11, Criteria1:="=STAU" * * Sheets.Add * * Sheets("klm").Select * * Rows("1:6000").Select * * Selection.Copy * * Sheets("Sheet1").Select * * Range("A1").Select * * ActiveSheet.Paste Rows("2:6000").Select * * Selection.Copy * * Workbooks.Open Filename:= _ * * * * "G:\J\klm.xls", Origin:=xlWindows Range("A65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste * * Cells.Select * * Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ * * * * OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ * * * * DataOption1:=xlSortNormal * * Columns("A:A").Select * *Set Rng = ActiveSheet R = 1 N = 1 With Rng * *LastRow = .Range("A" & Rows.Count).End(xlUp).Row * *Do While N <= LastRow * * * If R Mod 500 = 0 Then * * * * *Application.StatusBar = "Processing Row: " & Format(R, "#,##0") * * * End If * * * V = .Range("A" & R).Value * * * If V = vbNullString Then * * * * *If Application.WorksheetFunction. _ * * * * * * CountIf(.Columns(1), vbNullString) 1 Then * * * * * * .Rows(R).Delete * * * * *End If * * * Else * * * * *Next_V = .Range("A" & (R + 1)).Value * * * * *If V = Next_V Then * * * * * * Thisdate = .Range("H" & R).Value * * * * * * NextDate = .Range("H" & (R + 1)).Value * * * * * * If Thisdate < NextDate Then * * * * * * * *.Rows(R + 1).Delete * * * * * * Else * * * * * * * *.Rows(R).Delete * * * * * * End If * * * * *Else * * * * * * R = R + 1 * * * * *End If * * * End If * * * N = N + 1 * *Loop End With Cells.Select * * Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ * * * * OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ * * * * DataOption1:=xlSortNormal ActiveWorkbook.Save End Sub Thanks for offering your help Eddie On Jun 13, 10:34 pm, "Mike Fogleman" wrote: Do you really want the date duplicated on every new row, or just once at the start of a new day's data? It would be helpful if you included your present macro so we could modify it to work properly. Mike F"mikerobe" wrote in message .... HI I hope someone can help. I run Macro in Excel to add new records from a text file to the bottom of an excel sheet on a daily basis. Therefore the number of records in the sheet grows daily. There is data in each column up to column K. I would like to be able to add todays (being the day the data is added to the sheet) date to Column L when each new record is added while running the Macro. Is this possible. Thanks for any help Eddie- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Hi Mike This isn't quite working as i hoped. the date is only added to the last record that was added to the worksheet. Ultimately why i need to add the date is to use this date to extract data from multiple worksheets in the same folder to one worksheet in a separate workbook. The original text file is data from a Healthcare patient search. The dates queried go back 2 weeks in case the extract new record macro is not run daily as it normally should be. Hope this is clear Thanks Eddie- Hide quoted text - - Show quoted text - Hi Mike I have thought of another possible way around this. I can extract todays date with the text file. It will be positioned on Column H moving the sample date to Column I. This works fine but when the query is run on the next day the dates (in the todays date column) get overwritten with the next days date. Is there a way of locking these dates so they are not overwritten. Thanks Eddie |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding todays date to new records in Excel
Not really. First of all the two lines I added should put the date in column
L as far down as there is data in column K. This is done in the helper sheet you added to the text file. Did this not happen? Step through the code line by line and tell me what happened when you got past my lines. Mike F "mikerobe" wrote in message ... On Jun 14, 2:30 pm, "Mike Fogleman" wrote: I would think the best time to put the dates is right after you copy the text to sheet1 in the text file, and before you copy that to your workbook. Here is a snippet from your code with the lines added to put the Date in column L as far down as there is data in column K. Rows("1:1").Select Selection.Insert Shift:=xlDown Cells.Select Selection.AutoFilter Selection.AutoFilter Field:=11, Criteria1:="=STAU" Sheets.Add Sheets("klm").Select Rows("1:6000").Select Selection.Copy Sheets("Sheet1").Select Range("A1").Select ActiveSheet.Paste Range("L1").Value = Date Range("L1", Range("K1").End(xlDown)).Offset(0, 1).FillDown Mike F"mikerobe" wrote in message ... Thanks Mike and Ron Here is the Macro which I should have included first time Sub klm() Workbooks.OpenText Filename:="M:\Statdata\klm.txt", Origin:=xlMSDOS, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _ Array(10, 1)), TrailingMinusNumbers:=True, Local:=True '<- this decides date interpretation Selection.Sort Key1:=Range("H1"), Order1:=xlDescending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("1:1").Select Selection.Insert Shift:=xlDown Cells.Select Selection.AutoFilter Selection.AutoFilter Field:=11, Criteria1:="=STAU" Sheets.Add Sheets("klm").Select Rows("1:6000").Select Selection.Copy Sheets("Sheet1").Select Range("A1").Select ActiveSheet.Paste Rows("2:6000").Select Selection.Copy Workbooks.Open Filename:= _ "G:\J\klm.xls", Origin:=xlWindows Range("A65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste Cells.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("A:A").Select Set Rng = ActiveSheet R = 1 N = 1 With Rng LastRow = .Range("A" & Rows.Count).End(xlUp).Row Do While N <= LastRow If R Mod 500 = 0 Then Application.StatusBar = "Processing Row: " & Format(R, "#,##0") End If V = .Range("A" & R).Value If V = vbNullString Then If Application.WorksheetFunction. _ CountIf(.Columns(1), vbNullString) 1 Then .Rows(R).Delete End If Else Next_V = .Range("A" & (R + 1)).Value If V = Next_V Then Thisdate = .Range("H" & R).Value NextDate = .Range("H" & (R + 1)).Value If Thisdate < NextDate Then .Rows(R + 1).Delete Else .Rows(R).Delete End If Else R = R + 1 End If End If N = N + 1 Loop End With Cells.Select Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveWorkbook.Save End Sub Thanks for offering your help Eddie On Jun 13, 10:34 pm, "Mike Fogleman" wrote: Do you really want the date duplicated on every new row, or just once at the start of a new day's data? It would be helpful if you included your present macro so we could modify it to work properly. Mike F"mikerobe" wrote in message ... HI I hope someone can help. I run Macro in Excel to add new records from a text file to the bottom of an excel sheet on a daily basis. Therefore the number of records in the sheet grows daily. There is data in each column up to column K. I would like to be able to add todays (being the day the data is added to the sheet) date to Column L when each new record is added while running the Macro. Is this possible. Thanks for any help Eddie- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Hi Mike This isn't quite working as i hoped. the date is only added to the last record that was added to the worksheet. Ultimately why i need to add the date is to use this date to extract data from multiple worksheets in the same folder to one worksheet in a separate workbook. The original text file is data from a Healthcare patient search. The dates queried go back 2 weeks in case the extract new record macro is not run daily as it normally should be. Hope this is clear Thanks Eddie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| adding/omitting/all the values prior to todays date | Excel Worksheet Functions | |||
| Why todays date shows up as #### in excel doc. | Excel Discussion (Misc queries) | |||
| In Excel, Get todays date in a cell | Excel Discussion (Misc queries) | |||
| adding a month onto todays date | Excel Programming | |||
Hybrid Mode
