Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateTime format
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateTime format
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateTime format
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 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateTime format
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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
DateTime format
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" wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Datetime CSV format | Excel Discussion (Misc queries) | |||
DateTime datatype in VBA | Excel Programming | |||
Custom DateTime Format | Excel Programming | |||
datetime format issues when linking access to excel | Excel Discussion (Misc queries) | |||
DateTime Format | Excel Programming |