Export/Import Dynamic Filenames! PLEASE HELP

[philcon]

Hi All,

I am trying to export a textfile (query) from an access application, but I need the filename to be unique for each time this occurs (following a standard format)

i.e file001
file002 etc

Is there a simple way of doing this.

Regards


Phil.

 

[paron]

Make the current Date/Time part of the file name?

 

[philcon]

Many thanks for the advice, but can you tell me how I would specify that (if the export were within a macro, presumably I cant just put file+now().txt within the export filename field.

Regards


Phil.

ps (maybe I should have said that this needed to be an automtic repetitive process)

 

[paron]

No problem with automatic repetitive process -- but I didn't realize you were using a macro. In VBA, you really would do just about what you said:

Public Sub SendSpreadsheet()
Dim strFileName as String
strFileName = "File" & month(Now())
strFileName = strFileName & Day(Now())
strFileName = strFileName & Year(Now())
strFileName = strFileName & Hour(Now())
strFileName = strFileName & Minute(Now())
strFileName = strFileName & Second(Now())
strFileName = strFileName & ".txt"

DoCmd.OutputTo acOutputQuery, "YourQueryNameHere", _
acFormatTXT, strFileName

End Sub

or something like that. In a macro? Sorry, never use them, I'm afraid they'll phase them out completely in the future. Maybe there's a macro expert reading this who can help.

Ron

 

[Shortie]

You could have a textfile (lastfile.txt) located in the same folder that your trying to write to. Have this textfile contain the last number in the sequence.

Your export code could have the following setup...

Option Compare Database
Option Explicit

Function ExportQueryToFile()
On Error GoTo Error_ExportQueryToFile

'
' Variable Declaration
'
Const intSeqLen As Integer = 4 'The length of the sequential number at the end of each file
Const strDrivePath As String = "d:\temp\" 'The drive and path where output file should go

Dim intPos As Integer 'Use to identify the current position/length of reference
Dim intLastFile As Integer 'The last sequential number used as imported from lastfile.txt
Dim intNextFile As Integer 'The value representing the number to be used for export on this occasion

Dim strNextFile As String 'The string equivelant of intNextFile including pre-padded zero(s)
Dim strSeqFile As String 'The full path and filename of the Sequential Number generator

Dim FileIsOpen As Boolean 'Is the Input file open?

'
' Variable initialisation
'
strDrivePath = "d:\temp\"
strSeqFile = strDrivePath & "lastfile.txt"
FileIsOpen = False

'
' Open input file - import last sequential file number
'
If Dir(strSeqFile) = "" Then Error 9998

Open strSeqFile For Input As #1

FileIsOpen = True
Input #1, intLastFile

Close #1

FileIsOpen = False

New_ExportQueryToFile:

'
' Check integrity of file number sequence returned
'
If intLastFile = ((10 ^ intSeqLen) - 1) Then Error 9999

intNextFile = intLastFile + 1

'
' Open output file - export last sequential file number
'
Open strSeqFile For Output As #1

FileIsOpen = True
Write #1, intNextFile

Close #1

FileIsOpen = False

'
' Pad file number with zeros
'
strNextFile = Trim(Str(intNextFile))

If Len(strNextFile) < intSeqLen Then

For intPos = 1 To (intSeqLen - Len(strNextFile))

strNextFile = &quot;0&quot; & strNextFile

Next

End If

'
' Export query to requested file
'
strNextFile = strDrivePath & &quot;File&quot; & strNextFile & &quot;.txt&quot;

DoCmd.TransferText acExportDelim, , &quot;qryTest&quot;, strNextFile

Exit_ExportQueryToFile:

If FileIsOpen Then Close #1
Exit Function

Error_ExportQueryToFile:

If Err.Number = 9999 Then

MsgBox &quot;The maximum number of export files has been met.@Change the code of this program &quot; _
& &quot;to increase the constant variable known as@intSeqLen.&quot;, vbOKOnly + vbCritical, &quot;Error: 9999&quot;
Resume Exit_ExportQueryToFile

ElseIf Err.Number = 9998 Then

intLastFile = (CInt(InputBox(&quot;The &quot; & strSeqFile & &quot; will be created now. You need to specify what &quot; _
& &quot;the first sequential number will be. It must be less than &quot; & (10 ^ intSeqLen) & &quot;. Start numbering at...&quot;, _
strSeqFile & &quot; not found!&quot;, 1)) - 1)
Resume New_ExportQueryToFile

Else

MsgBox Err.Description, vbOKOnly + vbCritical, &quot;Error: &quot; & Err.Number
Resume Exit_ExportQueryToFile

End If

End Function

 

[philcon]

Thanks shorty, and thank v much for all the documentation.

Now would I be a reeeal pain if I asked how I could get another application to import each of said files(to the same table)i.e. look for text files where the name is of a given format, and one by one import them.

Regards


Phil.

 

[Shortie]

I'll have to get back to you on monday, but basically, on specification of a file number (say 0345 input by user), you could run code to process the file: e.g.


if dir(&quot;d:\temp\file0345.txt&quot <> &quot;&quot; then

open text file
while to eof
read record
docmd.runsql(&quot;APPEND QUERY&quot
loop

close file

endif

If file exists, import record, and append values to table