Default Filename with a SaveAs (OPENFILENAME) Dialog

[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!


P.S. I tried assigning a string with a default filename to the .lpstrFile property (.lpstrFile = strDefaultFilename) instead of the standard:

.lpstrFile = String(257, 0)

But it doesn't work.

 

[MarkK]

Played with this for a while. Best I could find is that you can push the filename in by assigning it here...

'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.
Cheers,

 

[vbaDev]

This works, thanks! I wish they would explain this "string(257)" thing somewhere in references! I still don't understand what this thing does and why it's necessary. Can you please explain?

Other than that, I'm all set, I only need a default filename, which usually creates a new file anyway and the user can choose to change the filename if they want. Cool!

Thanks!

 

[MarkK]

In the String(257, 0) they're just padding out the 256 character string variable lpstrFile.
Cheers,

 

[vbaDev]

In the String(257, 0) they're just padding out the 256 character string variable lpstrFile.
Cheers,

I'm sorry, can you please explain what that means (if you don't mind). I am not a pro-programmer, so I don't know some of the more system-specific stuff (I've mostly always dealt with Access and VBA). I've never before encountered this "padding". But I like to try and understand stuff I learn and use.

Thanks!

 

[MarkK]

I don't know.
Not every language automatically initializes variables and my assumption is this is the case here. Maybe someone else here can offer more info.

 

[vbaDev]

Thanks. Your post was most helpful, though, at least my system is working now. That's all that counts.

I really wonder where in MS reference I would find that (I searched). It bums me out that a feature could be so unexplained. I'm sure that people with Computer Science background know about this, but it should still be in the references.

Thanks again!