I have the following code to open a csv file for writing. To make a unique
name for the file I make use of the Now() function. E.g. I get the file name :

C:\HF\Excel\Work\Naam_20090320_130144.csv

However, when run on another machine with different date time setting it
could result in :

C:\HF\Excel\Work\Naam_-200Ma_1:0:4:3.csv

Which result in an error.

How can I make sure that I will always get the first result?


Code :

Dim ThisDirectory As String
ThisDirectory = ActiveWorkbook.Path
Dim TimeStamp As String
TimeStamp = Mid(Now(), 7, 4) & Mid(Now(), 4, 2) & Mid(Now(), 1, 2) &
"_" & Mid(Now(), 12, 2) & Mid(Now(), 15, 2) & Mid(Now(), 18, 2)
Dim csvFileName As String
csvFileName = ThisDirectory & "\Naam_" & TimeStamp & ".csv"
Dim ReadyText As String

Set csvObject = CreateObject("Scripting.FileSystemObject")
Set csvFile = csvObject.CreateTextFile(csvFileName, True)

TimeStamp = Format(Now(),"YYYYMMDD_hhmmss")

If this post helps click Yes
--------------
Jacob Skaria


"Henk" wrote:

> I have the following code to open a csv file for writing. To make a unique
> name for the file I make use of the Now() function. E.g. I get the file name :
>
> C:\HF\Excel\Work\Naam_20090320_130144.csv
>
> However, when run on another machine with different date time setting it
> could result in :
>
> C:\HF\Excel\Work\Naam_-200Ma_1:0:4:3.csv
>
> Which result in an error.
>
> How can I make sure that I will always get the first result?
>
>
> Code :
>
> Dim ThisDirectory As String
> ThisDirectory = ActiveWorkbook.Path
> Dim TimeStamp As String
> TimeStamp = Mid(Now(), 7, 4) & Mid(Now(), 4, 2) & Mid(Now(), 1, 2) &
> "_" & Mid(Now(), 12, 2) & Mid(Now(), 15, 2) & Mid(Now(), 18, 2)
> Dim csvFileName As String
> csvFileName = ThisDirectory & "\Naam_" & TimeStamp & ".csv"
> Dim ReadyText As String
>
> Set csvObject = CreateObject("Scripting.FileSystemObject")
> Set csvFile = csvObject.CreateTextFile(csvFileName, True)
>
>

If you just want the timestamp, use

csvFileName = ActiveWorkbook.Path & "\" & _
"Naam_" & _
Format(Now,"ddmmyyyyhhmm") & ".cvs"

It is theoretically possible, though quite unlikely, that two users
might get the same value for the time stamp. If you really want a
universally unique number, use a GUID. For example,

csvFileName= ActiveWorkbook.Path & "\" & _
"Naam_" & CreateGUID() & ".csv"

The code for CreateGUID is at
http://www.cpearson.com/Excel/CreateGUID.aspx .
The GUID will be unique across all users, all computers, and all
networks. Though unique, there is no meaningful information in a GUID.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 20 Mar 2009 05:07:01 -0700, Henk
<(E-Mail Removed)> wrote:

>I have the following code to open a csv file for writing. To make a unique
>name for the file I make use of the Now() function. E.g. I get the file name :
>
>C:\HF\Excel\Work\Naam_20090320_130144.csv
>
>However, when run on another machine with different date time setting it
>could result in :
>
>C:\HF\Excel\Work\Naam_-200Ma_1:0:4:3.csv
>
>Which result in an error.
>
>How can I make sure that I will always get the first result?
>
>
>Code :
>
> Dim ThisDirectory As String
> ThisDirectory = ActiveWorkbook.Path
> Dim TimeStamp As String
> TimeStamp = Mid(Now(), 7, 4) & Mid(Now(), 4, 2) & Mid(Now(), 1, 2) &
>"_" & Mid(Now(), 12, 2) & Mid(Now(), 15, 2) & Mid(Now(), 18, 2)
> Dim csvFileName As String
> csvFileName = ThisDirectory & "\Naam_" & TimeStamp & ".csv"
> Dim ReadyText As String
>
> Set csvObject = CreateObject("Scripting.FileSystemObject")
> Set csvFile = csvObject.CreateTextFile(csvFileName, True)
>

tHenks !

"Jacob Skaria" wrote:

> TimeStamp = Format(Now(),"YYYYMMDD_hhmmss")
>
> If this post helps click Yes
> --------------
> Jacob Skaria
>
>
> "Henk" wrote:
>
> > I have the following code to open a csv file for writing. To make a unique
> > name for the file I make use of the Now() function. E.g. I get the file name :
> >
> > C:\HF\Excel\Work\Naam_20090320_130144.csv
> >
> > However, when run on another machine with different date time setting it
> > could result in :
> >
> > C:\HF\Excel\Work\Naam_-200Ma_1:0:4:3.csv
> >
> > Which result in an error.
> >
> > How can I make sure that I will always get the first result?
> >
> >
> > Code :
> >
> > Dim ThisDirectory As String
> > ThisDirectory = ActiveWorkbook.Path
> > Dim TimeStamp As String
> > TimeStamp = Mid(Now(), 7, 4) & Mid(Now(), 4, 2) & Mid(Now(), 1, 2) &
> > "_" & Mid(Now(), 12, 2) & Mid(Now(), 15, 2) & Mid(Now(), 18, 2)
> > Dim csvFileName As String
> > csvFileName = ThisDirectory & "\Naam_" & TimeStamp & ".csv"
> > Dim ReadyText As String
> >
> > Set csvObject = CreateObject("Scripting.FileSystemObject")
> > Set csvFile = csvObject.CreateTextFile(csvFileName, True)
> >
> >

You have two suggestions to use Format. For my curiosity could you confirm
they work correctly, in particular does the "yyyy" help provide the correct
year in your non English version. Just wondering if perhaps it should be
"jjjj", assuming jaar = year for you.

Regards,
Peter T

"Henk" <(E-Mail Removed)> wrote in message
news:99774843-4259-4DB4-AFCA-(E-Mail Removed)...
>I have the following code to open a csv file for writing. To make a unique
> name for the file I make use of the Now() function. E.g. I get the file
> name :
>
> C:\HF\Excel\Work\Naam_20090320_130144.csv
>
> However, when run on another machine with different date time setting it
> could result in :
>
> C:\HF\Excel\Work\Naam_-200Ma_1:0:4:3.csv
>
> Which result in an error.
>
> How can I make sure that I will always get the first result?
>
>
> Code :
>
> Dim ThisDirectory As String
> ThisDirectory = ActiveWorkbook.Path
> Dim TimeStamp As String
> TimeStamp = Mid(Now(), 7, 4) & Mid(Now(), 4, 2) & Mid(Now(), 1, 2)
> &
> "_" & Mid(Now(), 12, 2) & Mid(Now(), 15, 2) & Mid(Now(), 18, 2)
> Dim csvFileName As String
> csvFileName = ThisDirectory & "\Naam_" & TimeStamp & ".csv"
> Dim ReadyText As String
>
> Set csvObject = CreateObject("Scripting.FileSystemObject")
> Set csvFile = csvObject.CreateTextFile(csvFileName, True)
>
>