Adding a date timestamp to the last column of a spreadsheet

[sjharri]

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

 

[Bob Phillips]

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)

 

[sjharri]

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]

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)

 

[Bob Phillips]

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)

 

[sjharri]

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.

 

[sjharri]

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 sId>5) 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.