Macro Auto-Fill Field.

[Guest]

Hi,

I'm working on a price quotation template and I need some help. There is a
cell call it F4 that has the quotation reference number in it. What I want
to do is create a marco that runs when the template is opened that
automatically inserts concatenated data that is the 'system date' & "-" &
(here's the kicker) the count of the number of files in a given folder plus 1
in a ### format. For instance, take today and there are 10 quotes in the
folder, the cell would display "38387-011".

Is this possible, and if so how?

Sincerely,

Josh Hallenbeck

 

[Guest]

http://support.microsoft.com/default.aspx?scid=kb;en-us;165639

here is a link to how to count files in a directory

 

[PaulD]

: Hi,
:
: I'm working on a price quotation template and I need some help. There is
a
: cell call it F4 that has the quotation reference number in it. What I
want
: to do is create a marco that runs when the template is opened that

use the open event for ThisWorkbook
Private Sub Workbook_Open()
'your code here
End Sub

: automatically inserts concatenated data that is the 'system date' & "-" &

for the 'system date' in excel style
strSerial = (Date - DateSerial(1900, 1, 0)) + 1

: (here's the kicker) the count of the number of files in a given folder
plus 1

sorry I don't have time to code for you but look into FileSystemObject
getfolder, etc. to find the number of files in a specific directory

: in a ### format.

take your count of files, then use an if to determine how to make your
string
i.e. if <10 then "00" & CStr(count + 1)
else if < 100 then "0" & ...

:For instance, take today and there are 10 quotes in the
: folder, the cell would display "38387-011".

now combine strSerial & "-" & strCount


: Is this possible, and if so how?
:
: Sincerely,
:
: Josh Hallenbeck