Default FileName with a Save As (OPENFILENAME) dialog box

[vbaDev]

Hi. I am using Access 2000 and in my code I'm exporting a table into an Excel file (creating it), then the code needs to export another query into the same file (a new worksheet). So I needed both a "Save As" dialog and the ability to grab the filepath so that the second export appends to it.

Anyway, I found Microsofts method and it works, except that I can't figure out how to populate the File Name box in the Dialog with a default name (say, the Access table name being exported). Users usually go with that in my case.

any ideas? Here's the code below, if it helps:
(this is all in a Class Module per MS instructions):

 
Option Compare Database
Option Explicit
'Code needed to use the Windows "Open File" and "Save As" Dialog Boxes
'to capture file path for exporting spreadsheets, etc.
 
'Declare needed functions
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
  (pOpenfilename As OPENFILENAME) As Long
 
Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" _
  (pOpenfilename As OPENFILENAME) As Long
 
'Declare OPENFILENAME custom Type
Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    Flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type
 
Private mstrFileName As String
Private mblnStatus As Boolean
 
 
Public Property Let GetName(strName As String)
    mstrFileName = strName
End Property
 
Public Property Get GetName() As String
    GetName = mstrFileName
End Property
 
Public Property Let GetStatus(blnStatus As Boolean)
    mblnStatus = blnStatus
End Property
 
Public Property Get GetStatus() As Boolean
    GetStatus = mblnStatus
End Property
 
'Function needed to call the "Open File" dialog
Public Function OpenFileDialog(lngFormHwnd As Long, _
                               lngAppInstance As Long, _
                               strInitDir As String, _
                               strFileFilter As String) As Long
Dim OpenFile As OPENFILENAME
Dim X As Long
 
With OpenFile
    .lStructSize = Len(OpenFile)
    .hwndOwner = lngFormHwnd
    .hInstance = lngAppInstance
    .lpstrFilter = strFileFilter
    .nFilterIndex = 1
    .lpstrFile = String(257, 0)
    .nMaxFile = Len(OpenFile.lpstrFile) - 1
    .lpstrFileTitle = OpenFile.lpstrFile
    .nMaxFileTitle = OpenFile.nMaxFile
    .lpstrInitialDir = strInitDir.lpstrTitle = "Open File"
    .Flags = 0
End With
 
X = GetOpenFileName(OpenFile)
If X = 0 Then
    mstrFileName = "none"
    mblnStatus = False
Else
    mstrFileName = Trim(OpenFile.lpstrFile)
    mblnStatus = True
End If
End Function
 
'Function needed to call the "Save As" dialog
Public Function SaveFileDialog(lngFormHwnd As Long, _
                               lngAppInstance As Long, _
                               strInitDir As String, _
                               strFileFilter As String) As Long
Dim SaveFile As OPENFILENAME
Dim X As Long
 
If IsMissing(strFileName) Then strFileName = ""
 
With SaveFile
    .lStructSize = Len(SaveFile)
    .hwndOwner = lngFormHwnd
    .hInstance = lngAppInstance
    .lpstrFilter = strFileFilter
    .nFilterIndex = 1
    .lpstrFile = String(257, 0)
    .nMaxFile = Len(SaveFile.lpstrFile) - 1
    .lpstrFileTitle = SaveFile.lpstrFile
    .nMaxFileTitle = SaveFile.nMaxFile
    .lpstrInitialDir = strInitDir
    .lpstrTitle = "Export To"
    .Flags = 0
    .lpstrDefExt = ".xls"   'sets default file extension to Excel, in case user does not type it
End With
 
X = GetSaveFileName(SaveFile)
If X = 0 Then
    mstrFileName = "none"
    mblnStatus = False
Else
    mstrFileName = Trim(SaveFile.lpstrFile)
    mblnStatus = True
End If
End Function

--- End of Module Code ---

And the actual code snippet in my form module, which calls the Dialog:

'CALL SAVE AS DIALOG BOX to specify Exporting Location:
'************** **********

 Dim cDlg As New CommonDialogAPI     'instantiate CommonDialog
Dim lngFormHwnd As Long
Dim lngAppInstance As Long
Dim strInitDir As String
Dim strFileFilter As String
Dim lngResult As Long
 
lngFormHwnd = Me.Hwnd
lngAppInstance = Application.hWndAccessApp
strInitDir = "C:\"
 
strFileFilter = "Excel Files (*.xls)" & _
    Chr(0) & "*.xls" & Chr(0) & _
    "Text Files (*.csv, *.txt)" & _
    Chr(0) & "*.csv; *.txt" & Chr(0)
 
lngResult = cDlg.SaveFileDialog(lngFormHwnd, _
    lngAppInstance, strInitDir, strFileFilter)
 
If cDlg.GetStatus = True Then
    MsgBox "You selected file: " & cDlg.GetName
    strSavePath = cDlg.GetName  'assign selected path to variable to be passed to TransferSpreadsheet Method
Else
    MsgBox "No file selected."
End If

'************** ***
'END SAVE AS CODE

I copied this code, of course, only having an overall understanding of all the parts, but it works as far as getting a full path, BUT... please help me with that pesky default FileName. Thanks!

[vbaDev]

Here's what I got on another forum, it works:

Replace

 
.lpstrFile = String(257, 0)

with

 
.lpstrFile = "testfile.txt" & string(257 - len("testfile.txt", 0)

This causes 'testfile.txt' to be displayed in the 'file name' textbox, but this file is not selected in the list, if it exists.

Post back if you find a cooler solution.

And don't forget to include the following:

 
.Flags = OFN_OVERWRITEPROMPT    'warns the user before overwriting an existing file

(instead of .Flags = 0)

if you don't want existing files overwritten automatically.

[NeoPa]

Sorry that no-one was able to help with your problem. Thanks anyway for posting the solution :)