Access 2003 FileDialog Save As VBA Code

[jbassett]

I have been playing around with using the FileDialog SaveAs object in
Access 2003 VBA. After searching I see that the standard is to use the
code at:

http://www.mvps.org/access/api/api0001.htm

However, I modified some code in a thread that Amy Blankenship tossed
out as untested and came up with the following.


*** Code Start ***

Private Sub ExportCB_Click()
Dim dlgSaveAs As FileDialog
Dim strRawFileInfo As String
Dim strMailerExportPath As String
Dim strMailerExportFile As String

' Create Save AS Dialog Box
Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)

'Use a With...End With block to reference the FileDialog object.
With dlgSaveAs
.Title = "Save Export File"
'Use the Show method to display the File Picker dialog box and
return the user's action.
'The user pressed the action button.
If .Show = -1 Then
strRawFileInfo = .SelectedItems(1)
'The user pressed Cancel.
Else
strRawFileInfo = ""
MsgBox "Action Canceled"
End If
End With

' Extract Seperate Path and File Names from SaveAs Dialog Box
If strRawFileInfo <> "" Then
strMailerExportFile = Right(strRawFileInfo, Len(strRawFileInfo)
- InStrRev(strRawFileInfo, "\"))
strMailerExportPath = Left(strRawFileInfo, Len(strRawFileInfo)
- Len(strMailerExportFile))
End If
' Check file name for correct extension and if not present add .txt
If Right(strMailerExportFile, 4) <> ".txt" Then
' Check for wrong extension and strip it
If Mid((Right(strMailerExportFile, 4)), 1, 1) = "." Then
strMailerExportFile = Left(strMailerExportFile,
Len(strMailerExportFile) - 4)
Else
End If
' Add Correct Extension
strMailerExportFile = strMailerExportFile & ".txt"
Else
End If

' Export tab delimited txt file to folder with file name that use
selected
DoCmd.TransferText acExportDelim, , "QBrokerMailer",
strMailerExportPath & strMailerExportFile

' Clear strings used
strRawFileInfo = ""
strMailerExportPath = ""
strMailerExportFile = ""

End Sub

*** End Code ***

Being relatively new to Access VBA I wanted to get some feedback on the
above code. Can / should it be streamlined (Not sure if I need to
break the path and the file name out into two separate strings)? Would
it work if I moved it to a module rather then repeating the code on
every form? It seems to work just fine for my use (Exporting a query
and allowing the individual to select where and name the file via the
file dialog box).

Comments appreciated.

Thanks,
Jeff Bassett
Commercial Properties, Inc.

j bassett (at) cpi az (dot) com

 

[John Nurick]

The main reason for not using Application.FileDialog is that it is
susceptible to problems with different versions of the Windows common
dialogs library. If you've got it working on your system and portability
isn't a concern, well and good; but if you want your application to work
on other people's computers, with different versions or even service
packs of Office and/or Windows, it's safer and simpler in the long run
to use the Access Web code.

Other comments inline.

I have been playing around with using the FileDialog SaveAs object in
Access 2003 VBA. After searching I see that the standard is to use the
code at:

http://www.mvps.org/access/api/api0001.htm

However, I modified some code in a thread that Amy Blankenship tossed
out as untested and came up with the following.


*** Code Start ***

Private Sub ExportCB_Click()
Dim dlgSaveAs As FileDialog
Dim strRawFileInfo As String
Dim strMailerExportPath As String
Dim strMailerExportFile As String

' Create Save AS Dialog Box
Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)

'Use a With...End With block to reference the FileDialog object.
With dlgSaveAs
.Title = "Save Export File"
'Use the Show method to display the File Picker dialog box and
return the user's action.

Can "File Picker" dialog be correct? You're getting a path and filename
for export, and I thought the file picker only let you select existing
files. But I almost never use the FileDialog object so maybe I'm wrong.

'The user pressed the action button.
If .Show = -1 Then
strRawFileInfo = .SelectedItems(1)
'The user pressed Cancel.
Else
strRawFileInfo = ""
MsgBox "Action Canceled"
End If
End With

' Extract Seperate Path and File Names from SaveAs Dialog Box
If strRawFileInfo <> "" Then

VBA lets you wrap long statements onto multiple lines for easier reading
by ending each line with <space><underscore>

strMailerExportFile = Right(strRawFileInfo, _
Len(strRawFileInfo) - InStrRev(strRawFileInfo, "\"))
strMailerExportPath = Left(strRawFileInfo, _
Len(strRawFileInfo) - Len(strMailerExportFile))

End If
' Check file name for correct extension and if not present add .txt
If Right(strMailerExportFile, 4) <> ".txt" Then
' Check for wrong extension and strip it

The next line assumes that file extensions are always three characters
long. AFAIK that has never been a safe assumption. Instead, use
InStrRev() to find the last . in the path.

If Mid((Right(strMailerExportFile, 4)), 1, 1) = "." Then
strMailerExportFile = Left(strMailerExportFile,
Len(strMailerExportFile) - 4)
Else
End If
' Add Correct Extension
strMailerExportFile = strMailerExportFile & ".txt"
Else
End If

' Export tab delimited txt file to folder with file name that use
selected
DoCmd.TransferText acExportDelim, , "QBrokerMailer",
strMailerExportPath & strMailerExportFile

' Clear strings used

Not necessary. Sometimes it's necessary or advisable to close objects
and set object variables to Nothing in order to release memory, but not
with ordinary variables.

strRawFileInfo = ""
strMailerExportPath = ""
strMailerExportFile = ""

End Sub

*** End Code ***

Being relatively new to Access VBA I wanted to get some feedback on the
above code. Can / should it be streamlined (Not sure if I need to
break the path and the file name out into two separate strings)?

You don't need to, but it doesn't do any harm.

Would
it work if I moved it to a module rather then repeating the code on
every form? It seems to work just fine for my use (Exporting a query
and allowing the individual to select where and name the file via the
file dialog box).

The general idea would be to modify the procedure to take one or more
arguments that would reflect the form it was called from. As it stands,
it appears to export the query "QBrokerMailer", but presumably you don't
want every form to have a button to export that. So you might change the
procedure declaration to something like

Sub ExportQueryToCSV(QueryName As String)

and then replace the literal "QBrokerMailer" in the DoCmd.TransferText
statement with the argument QueryName.