Create filename based todays date

L

Lars Hostrup

I'am using "transfer" text to create a text file based on a query. In stead
of having af "fixed" filename for the output file, I would like to build a
small routine that gives the file a name based on todays date i.e.
11072007.txt.

I need help to solve this issue and hope someone can help me!

Lars

-----------------------------------


Private Sub Label26_DblClick(Cancel As Integer)

Dim oApp As Object
Dim ExcelWasNotRunning As Boolean
Dim Msg, Style, Title, Response, MyString
Dim SourceFile, Destinationfile

'export af betalinger til CSV-fil

DoCmd.TransferText acExportDelim, "factoringfile", "Betalinger",
"K:\All\Koncern\betalingsfiler\Betalinger.txt"
DoCmd.TransferText acExportDelim, , "Betalinger",
"K:\All\Koncern\betalingsfiler\Betalinger.csv"

'kopier fil
SourceFile = "K:\All\Koncern\Betalingsfiler\betalinger.txt"
Destinationfile =
"K:\All\Koncern\Betalingsfiler\book\betalingersendt.txt"
FileCopy SourceFile, Destinationfile


'Kør excel for at vise filen
'Msg = "Do you want to Continue?"
'Style = vbYesNo + vbDefaultButton2
'Title = "MsgBox"
'Response = MsgBox(Msg, Style, Title)

'If Responce = vbYes Then

On Error Resume Next

Set oApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear

Set oApp =
GetObject("K:\All\Koncern\betalingsfiler\Betalinger.csv")

oApp.Application.Visible = True
oApp.Parent.Windows(1).Visible = True


'Only XL 97 supports UserControl Property

oApp.UserControl = True
'Else

Exit_runExcel_Click:
Exit Sub

MsgBox ("file created"), vbInformation
'End If
'transfer_error:
' MsgBox "error in fil transfer", vbOKOnly
End Sub
 
B

Beginner

Hi!

Here is one example that gives you the current date, hopefully this will
help you:

Dim strstring As String
Dim d As Date
d = Now

strstring = day(d) & Month(d) & Year(d)

MsgBox strstring
'you can make it look like you want to by formatting
MsgBox Format(d, "ddmmyyyy")


-Beginner-
 
J

John Spencer

Destinationfile =
"K:\All\Koncern\Betalingsfiler\book\betalingersendt" &
Format(Date(),"ddmmyyyy") &.txt"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
K

Klatuu

John Spenser's answer shows how it is done; however, I would make one
suggestion. For sorting the filenames in the folder to make them easier to
find, I would suggest "yyyymmdd" as the order.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top