Export Table with date in filename

[jempie]

I have the following code in a module:

Private Sub Exprt_Click()
Dim strFileName As String
Dim tOutputPath As String

strFileName = Format(Date, "ddmmyyyy") & ".xls"
tOutputPath = "E:\Jemmo\BMK Classified\SamplesForBMK\"

DoCmd.TransferText acExportDelim, , "This Week-Deduped", tOutputPath & strFileName, 1, , 1

MsgBox "The registrations have been exported successfully."

End Sub

When I run the module, I get a run-time error that the database or object is read-only. Can anyone help?

Thanks,

Jempie

 

[cable]

you've got no checks to see if the filename exists?

also you seem to have too many options on your transfertext line, after the filename there is a HasFieldName and HTMLTableName option.

 

[jempie]

there is no checks as the export will only be run once a day. besides, i'm too much of a beginner to know how to do that!

when you type transfertext, it shows that there are the HasFieldName and HTMLTableName options, but also a codepage option. Hence the " 1, , 1" after the filename.

jempie

 

[cable]

is the table open? that might cause problems.

Below is a simple file exists function:

You would use it before the transfertext sommit like:
If Exists(tOutputPath & strFileName) then
msgbox "some kind of warning"
'you could use Kill tOutputPath & strFileName to delete the file here
end if

Private Function Exists(sFileName As String) As Boolean
'check if a file exists
Dim l As Long

On Error GoTo ErrHand

Exists = False

If InStr(sFileName, ".") <> 0 Then  'check for extension, hence file
    l = FileLen(sFileName)
    If l <> 0 Then
        Exists = True
    End If
Else                                'we're not a file, we're a directory/folder
    If Dir(sFileName, vbDirectory) <> "" Then
        Exists = True
    End If
End If

Exit Function
ErrHand:
    Exists = False                  'any error then default to false
End Function

 

[jempie]

i thought that it would of been because the table is open, but it is not. i'm really baffled why the export does not work!

Thanks for the file exists check, it looks excellent.