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!
