| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a date timestamp to the last column of a spreadsheet
I have a macro contained in a master spreadsheet which is used to
extract data from a spreadsheet selected by a user, the selected spreadsheets are listed in the master spreadsheet containing the macro. The macro opens the selected spreadsheet and formats the data (removes unwanted cells, adds a header row and numbers it 1,2,3.......) and outputs it to a csv file, this is then loaded into a sql server database. I need to add an update date field as the final field in the csv file, therefore this should be appended to the final column of data in the final csv file. I do not want to edit the original spreadsheet selected by the user. The final column of data varies between user spreadsheets but I am identifying what is the final column and inserting a header row up to the final column using the code: If sId 5 Then LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Rows("1:1").Insert shift:=xlDown Range("A1").Select ActiveCell.Value = 1 Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)), Type:=xlFillSeries Can I do something like LastCol+1 needs to be autofill with a date timestamp? Fairly new to this so excuse my ignorance? Thanks Steve |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding a date timestamp to the last column of a spreadsheet
Just add
Cells(1,LastCol+1).Value = Format(Date, "yyyy-mm-dd") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I have a macro contained in a master spreadsheet which is used to extract data from a spreadsheet selected by a user, the selected spreadsheets are listed in the master spreadsheet containing the macro. The macro opens the selected spreadsheet and formats the data (removes unwanted cells, adds a header row and numbers it 1,2,3.......) and outputs it to a csv file, this is then loaded into a sql server database. I need to add an update date field as the final field in the csv file, therefore this should be appended to the final column of data in the final csv file. I do not want to edit the original spreadsheet selected by the user. The final column of data varies between user spreadsheets but I am identifying what is the final column and inserting a header row up to the final column using the code: If sId 5 Then LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Rows("1:1").Insert shift:=xlDown Range("A1").Select ActiveCell.Value = 1 Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)), Type:=xlFillSeries Can I do something like LastCol+1 needs to be autofill with a date timestamp? Fairly new to this so excuse my ignorance? Thanks Steve |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding a date timestamp to the last column of a spreadsheet
Thanks! Need it to look at little different.
This is giving me the date and looks like this: 1 2 08/09/2006 US DOLLAR 32.37903956 UK 6.110059007 EURO 34.89862838 JAPANESE YEN 18.396 ASIAN 3.067956227 EMERG 5.148316832 TOTAL 100 What I would like is for each row to be populated with the date/time and for the top row to be populated with the next sequential number (above this would be 3) as this row is used to read the data into a sql server table by our proprietary loader. I would like it to look like this: 1 2 3 US DOLLAR 32.37903956 08/09/2006 UK 6.110059007 08/09/2006 EURO 34.89862838 08/09/2006 JAPANESE YEN 18.396 08/09/2006 ASIAN 3.067956227 08/09/2006 EMERG 5.148316832 08/09/2006 TOTAL 100 08/09/2006 I can get it to look like below but for some reason it is not adding the number 3 as a column header: 1 2 US DOLLAR 32.37903956 08/09/2006 UK 6.110059007 EURO 34.89862838 JAPANESE YEN 18.396 ASIAN 3.067956227 EMERG 5.148316832 100 Code currently looks like this: If sId 5 Then LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Cells(1, LastCol + 1).Value = Format(Date, "yyyy-mm-dd") Rows("1:1").Insert shift:=xlDown Range("A1").Select ActiveCell.Value = 1 Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)), Type:=xlFillSeries Any ideas? Bob Phillips wrote: Just add Cells(1,LastCol+1).Value = Format(Date, "yyyy-mm-dd") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I have a macro contained in a master spreadsheet which is used to extract data from a spreadsheet selected by a user, the selected spreadsheets are listed in the master spreadsheet containing the macro. The macro opens the selected spreadsheet and formats the data (removes unwanted cells, adds a header row and numbers it 1,2,3.......) and outputs it to a csv file, this is then loaded into a sql server database. I need to add an update date field as the final field in the csv file, therefore this should be appended to the final column of data in the final csv file. I do not want to edit the original spreadsheet selected by the user. The final column of data varies between user spreadsheets but I am identifying what is the final column and inserting a header row up to the final column using the code: If sId 5 Then LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Rows("1:1").Insert shift:=xlDown Range("A1").Select ActiveCell.Value = 1 Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)), Type:=xlFillSeries Can I do something like LastCol+1 needs to be autofill with a date timestamp? Fairly new to this so excuse my ignorance? Thanks Steve |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding a date timestamp to the last column of a spreadsheet
I don't see in the code where you are filling anything other than row 1.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Thanks! Need it to look at little different. This is giving me the date and looks like this: 1 2 08/09/2006 US DOLLAR 32.37903956 UK 6.110059007 EURO 34.89862838 JAPANESE YEN 18.396 ASIAN 3.067956227 EMERG 5.148316832 TOTAL 100 What I would like is for each row to be populated with the date/time and for the top row to be populated with the next sequential number (above this would be 3) as this row is used to read the data into a sql server table by our proprietary loader. I would like it to look like this: 1 2 3 US DOLLAR 32.37903956 08/09/2006 UK 6.110059007 08/09/2006 EURO 34.89862838 08/09/2006 JAPANESE YEN 18.396 08/09/2006 ASIAN 3.067956227 08/09/2006 EMERG 5.148316832 08/09/2006 TOTAL 100 08/09/2006 I can get it to look like below but for some reason it is not adding the number 3 as a column header: 1 2 US DOLLAR 32.37903956 08/09/2006 UK 6.110059007 EURO 34.89862838 JAPANESE YEN 18.396 ASIAN 3.067956227 EMERG 5.148316832 100 Code currently looks like this: If sId 5 Then LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Cells(1, LastCol + 1).Value = Format(Date, "yyyy-mm-dd") Rows("1:1").Insert shift:=xlDown Range("A1").Select ActiveCell.Value = 1 Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)), Type:=xlFillSeries Any ideas? Bob Phillips wrote: Just add Cells(1,LastCol+1).Value = Format(Date, "yyyy-mm-dd") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I have a macro contained in a master spreadsheet which is used to extract data from a spreadsheet selected by a user, the selected spreadsheets are listed in the master spreadsheet containing the macro. The macro opens the selected spreadsheet and formats the data (removes unwanted cells, adds a header row and numbers it 1,2,3.......) and outputs it to a csv file, this is then loaded into a sql server database. I need to add an update date field as the final field in the csv file, therefore this should be appended to the final column of data in the final csv file. I do not want to edit the original spreadsheet selected by the user. The final column of data varies between user spreadsheets but I am identifying what is the final column and inserting a header row up to the final column using the code: If sId 5 Then LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Rows("1:1").Insert shift:=xlDown Range("A1").Select ActiveCell.Value = 1 Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)), Type:=xlFillSeries Can I do something like LastCol+1 needs to be autofill with a date timestamp? Fairly new to this so excuse my ignorance? Thanks Steve |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding a date timestamp to the last column of a spreadsheet
Is this any better?
With ActiveSheet If sId 5 Then LastRow = .UsedRange.Rows.Count - .UsedRange.Row - 1 LastCol = .UsedRange.Columns.Count + .UsedRange.Column - 1 .Rows("1:1").Insert shift:=xlDown With .Range("A1") .Value = 1 .AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol + 1)), Type:=xlFillSeries End With .Cells(2, LastCol + 1).Resize(LastRow).Value = Format(Date, "mm/dd/yyyy") End If End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Thanks! Need it to look at little different. This is giving me the date and looks like this: 1 2 08/09/2006 US DOLLAR 32.37903956 UK 6.110059007 EURO 34.89862838 JAPANESE YEN 18.396 ASIAN 3.067956227 EMERG 5.148316832 TOTAL 100 What I would like is for each row to be populated with the date/time and for the top row to be populated with the next sequential number (above this would be 3) as this row is used to read the data into a sql server table by our proprietary loader. I would like it to look like this: 1 2 3 US DOLLAR 32.37903956 08/09/2006 UK 6.110059007 08/09/2006 EURO 34.89862838 08/09/2006 JAPANESE YEN 18.396 08/09/2006 ASIAN 3.067956227 08/09/2006 EMERG 5.148316832 08/09/2006 TOTAL 100 08/09/2006 I can get it to look like below but for some reason it is not adding the number 3 as a column header: 1 2 US DOLLAR 32.37903956 08/09/2006 UK 6.110059007 EURO 34.89862838 JAPANESE YEN 18.396 ASIAN 3.067956227 EMERG 5.148316832 100 Code currently looks like this: If sId 5 Then LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Cells(1, LastCol + 1).Value = Format(Date, "yyyy-mm-dd") Rows("1:1").Insert shift:=xlDown Range("A1").Select ActiveCell.Value = 1 Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)), Type:=xlFillSeries Any ideas? Bob Phillips wrote: Just add Cells(1,LastCol+1).Value = Format(Date, "yyyy-mm-dd") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I have a macro contained in a master spreadsheet which is used to extract data from a spreadsheet selected by a user, the selected spreadsheets are listed in the master spreadsheet containing the macro. The macro opens the selected spreadsheet and formats the data (removes unwanted cells, adds a header row and numbers it 1,2,3.......) and outputs it to a csv file, this is then loaded into a sql server database. I need to add an update date field as the final field in the csv file, therefore this should be appended to the final column of data in the final csv file. I do not want to edit the original spreadsheet selected by the user. The final column of data varies between user spreadsheets but I am identifying what is the final column and inserting a header row up to the final column using the code: If sId 5 Then LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Rows("1:1").Insert shift:=xlDown Range("A1").Select ActiveCell.Value = 1 Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)), Type:=xlFillSeries Can I do something like LastCol+1 needs to be autofill with a date timestamp? Fairly new to this so excuse my ignorance? Thanks Steve |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding a date timestamp to the last column of a spreadsheet
Bob and others:
I would really like to have the insert update date column as a seperate statement to the insert numbered row (as previously posted) because the update field needs applies to all spreadsheets being processed not just those with a sId of greater than 5. Hope this makes sense. Thanks. Bob Phillips wrote: Is this any better? With ActiveSheet If sId 5 Then LastRow = .UsedRange.Rows.Count - .UsedRange.Row - 1 LastCol = .UsedRange.Columns.Count + .UsedRange.Column - 1 .Rows("1:1").Insert shift:=xlDown With .Range("A1") .Value = 1 .AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol + 1)), Type:=xlFillSeries End With .Cells(2, LastCol + 1).Resize(LastRow).Value = Format(Date, "mm/dd/yyyy") End If End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Thanks! Need it to look at little different. This is giving me the date and looks like this: 1 2 08/09/2006 US DOLLAR 32.37903956 UK 6.110059007 EURO 34.89862838 JAPANESE YEN 18.396 ASIAN 3.067956227 EMERG 5.148316832 TOTAL 100 What I would like is for each row to be populated with the date/time and for the top row to be populated with the next sequential number (above this would be 3) as this row is used to read the data into a sql server table by our proprietary loader. I would like it to look like this: 1 2 3 US DOLLAR 32.37903956 08/09/2006 UK 6.110059007 08/09/2006 EURO 34.89862838 08/09/2006 JAPANESE YEN 18.396 08/09/2006 ASIAN 3.067956227 08/09/2006 EMERG 5.148316832 08/09/2006 TOTAL 100 08/09/2006 I can get it to look like below but for some reason it is not adding the number 3 as a column header: 1 2 US DOLLAR 32.37903956 08/09/2006 UK 6.110059007 EURO 34.89862838 JAPANESE YEN 18.396 ASIAN 3.067956227 EMERG 5.148316832 100 Code currently looks like this: If sId 5 Then LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Cells(1, LastCol + 1).Value = Format(Date, "yyyy-mm-dd") Rows("1:1").Insert shift:=xlDown Range("A1").Select ActiveCell.Value = 1 Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)), Type:=xlFillSeries Any ideas? Bob Phillips wrote: Just add Cells(1,LastCol+1).Value = Format(Date, "yyyy-mm-dd") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I have a macro contained in a master spreadsheet which is used to extract data from a spreadsheet selected by a user, the selected spreadsheets are listed in the master spreadsheet containing the macro. The macro opens the selected spreadsheet and formats the data (removes unwanted cells, adds a header row and numbers it 1,2,3.......) and outputs it to a csv file, this is then loaded into a sql server database. I need to add an update date field as the final field in the csv file, therefore this should be appended to the final column of data in the final csv file. I do not want to edit the original spreadsheet selected by the user. The final column of data varies between user spreadsheets but I am identifying what is the final column and inserting a header row up to the final column using the code: If sId 5 Then LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Rows("1:1").Insert shift:=xlDown Range("A1").Select ActiveCell.Value = 1 Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)), Type:=xlFillSeries Can I do something like LastCol+1 needs to be autofill with a date timestamp? Fairly new to this so excuse my ignorance? Thanks Steve |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding a date timestamp to the last column of a spreadsheet
I want something like the following however getting the error 'invalid
or unqualified reference for .Cells? As I want the update column to apply to all spreadsheets (not just where sId5) I have included it as a seperate statement. Determine the last column and autofill row one If sId 5 Then LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Rows("1:1").Insert shift:=xlDown Range("A1").Select ActiveCell.Value = 1 Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)), Type:=xlFillSeries ' Add update field to all spreadsheets LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Cells(2, LastCol + 1).Resize(LastRow).Value = Now Sorry guys, only started learning this yesterday. Bob Phillips wrote: Is this any better? With ActiveSheet If sId 5 Then LastRow = .UsedRange.Rows.Count - .UsedRange.Row - 1 LastCol = .UsedRange.Columns.Count + .UsedRange.Column - 1 .Rows("1:1").Insert shift:=xlDown With .Range("A1") .Value = 1 .AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol + 1)), Type:=xlFillSeries End With .Cells(2, LastCol + 1).Resize(LastRow).Value = Format(Date, "mm/dd/yyyy") End If End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Thanks! Need it to look at little different. This is giving me the date and looks like this: 1 2 08/09/2006 US DOLLAR 32.37903956 UK 6.110059007 EURO 34.89862838 JAPANESE YEN 18.396 ASIAN 3.067956227 EMERG 5.148316832 TOTAL 100 What I would like is for each row to be populated with the date/time and for the top row to be populated with the next sequential number (above this would be 3) as this row is used to read the data into a sql server table by our proprietary loader. I would like it to look like this: 1 2 3 US DOLLAR 32.37903956 08/09/2006 UK 6.110059007 08/09/2006 EURO 34.89862838 08/09/2006 JAPANESE YEN 18.396 08/09/2006 ASIAN 3.067956227 08/09/2006 EMERG 5.148316832 08/09/2006 TOTAL 100 08/09/2006 I can get it to look like below but for some reason it is not adding the number 3 as a column header: 1 2 US DOLLAR 32.37903956 08/09/2006 UK 6.110059007 EURO 34.89862838 JAPANESE YEN 18.396 ASIAN 3.067956227 EMERG 5.148316832 100 Code currently looks like this: If sId 5 Then LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Cells(1, LastCol + 1).Value = Format(Date, "yyyy-mm-dd") Rows("1:1").Insert shift:=xlDown Range("A1").Select ActiveCell.Value = 1 Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)), Type:=xlFillSeries Any ideas? Bob Phillips wrote: Just add Cells(1,LastCol+1).Value = Format(Date, "yyyy-mm-dd") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I have a macro contained in a master spreadsheet which is used to extract data from a spreadsheet selected by a user, the selected spreadsheets are listed in the master spreadsheet containing the macro. The macro opens the selected spreadsheet and formats the data (removes unwanted cells, adds a header row and numbers it 1,2,3.......) and outputs it to a csv file, this is then loaded into a sql server database. I need to add an update date field as the final field in the csv file, therefore this should be appended to the final column of data in the final csv file. I do not want to edit the original spreadsheet selected by the user. The final column of data varies between user spreadsheets but I am identifying what is the final column and inserting a header row up to the final column using the code: If sId 5 Then LastCol = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 Rows("1:1").Insert shift:=xlDown Range("A1").Select ActiveCell.Value = 1 Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, LastCol)), Type:=xlFillSeries Can I do something like LastCol+1 needs to be autofill with a date timestamp? Fairly new to this so excuse my ignorance? Thanks Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formating a timestamp as a date and time. | Excel Worksheet Functions | |||
| Converting UTC timestamp value to date and time | Excel Discussion (Misc queries) | |||
| insert date timestamp to end of spreadsheet | Excel Programming | |||
| Remove Timestamp from date | Excel Programming | |||
| Convert Date Time in Spreadsheet Column to Date only | Excel Programming | |||
Linear Mode
