By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,719 Members | 1,811 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,719 IT Pros & Developers. It's quick & easy.

Default FileName with a Save As (OPENFILENAME) dialog box

P: 2
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):
Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3. Option Explicit
  4. 'Code needed to use the Windows "Open File" and "Save As" Dialog Boxes
  5. 'to capture file path for exporting spreadsheets, etc.
  6.  
  7. 'Declare needed functions
  8. Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
  9.   (pOpenfilename As OPENFILENAME) As Long
  10.  
  11. Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" _
  12.   (pOpenfilename As OPENFILENAME) As Long
  13.  
  14. 'Declare OPENFILENAME custom Type
  15. Private Type OPENFILENAME
  16.     lStructSize As Long
  17.     hwndOwner As Long
  18.     hInstance As Long
  19.     lpstrFilter As String
  20.     lpstrCustomFilter As String
  21.     nMaxCustFilter As Long
  22.     nFilterIndex As Long
  23.     lpstrFile As String
  24.     nMaxFile As Long
  25.     lpstrFileTitle As String
  26.     nMaxFileTitle As Long
  27.     lpstrInitialDir As String
  28.     lpstrTitle As String
  29.     Flags As Long
  30.     nFileOffset As Integer
  31.     nFileExtension As Integer
  32.     lpstrDefExt As String
  33.     lCustData As Long
  34.     lpfnHook As Long
  35.     lpTemplateName As String
  36. End Type
  37.  
  38. Private mstrFileName As String
  39. Private mblnStatus As Boolean
  40.  
  41.  
  42. Public Property Let GetName(strName As String)
  43.     mstrFileName = strName
  44. End Property
  45.  
  46. Public Property Get GetName() As String
  47.     GetName = mstrFileName
  48. End Property
  49.  
  50. Public Property Let GetStatus(blnStatus As Boolean)
  51.     mblnStatus = blnStatus
  52. End Property
  53.  
  54. Public Property Get GetStatus() As Boolean
  55.     GetStatus = mblnStatus
  56. End Property
  57.  
  58. 'Function needed to call the "Open File" dialog
  59. Public Function OpenFileDialog(lngFormHwnd As Long, _
  60.                                lngAppInstance As Long, _
  61.                                strInitDir As String, _
  62.                                strFileFilter As String) As Long
  63. Dim OpenFile As OPENFILENAME
  64. Dim X As Long
  65.  
  66. With OpenFile
  67.     .lStructSize = Len(OpenFile)
  68.     .hwndOwner = lngFormHwnd
  69.     .hInstance = lngAppInstance
  70.     .lpstrFilter = strFileFilter
  71.     .nFilterIndex = 1
  72.     .lpstrFile = String(257, 0)
  73.     .nMaxFile = Len(OpenFile.lpstrFile) - 1
  74.     .lpstrFileTitle = OpenFile.lpstrFile
  75.     .nMaxFileTitle = OpenFile.nMaxFile
  76.     .lpstrInitialDir = strInitDir.lpstrTitle = "Open File"
  77.     .Flags = 0
  78. End With
  79.  
  80. X = GetOpenFileName(OpenFile)
  81. If X = 0 Then
  82.     mstrFileName = "none"
  83.     mblnStatus = False
  84. Else
  85.     mstrFileName = Trim(OpenFile.lpstrFile)
  86.     mblnStatus = True
  87. End If
  88. End Function
  89.  
  90. 'Function needed to call the "Save As" dialog
  91. Public Function SaveFileDialog(lngFormHwnd As Long, _
  92.                                lngAppInstance As Long, _
  93.                                strInitDir As String, _
  94.                                strFileFilter As String) As Long
  95. Dim SaveFile As OPENFILENAME
  96. Dim X As Long
  97.  
  98. If IsMissing(strFileName) Then strFileName = ""
  99.  
  100. With SaveFile
  101.     .lStructSize = Len(SaveFile)
  102.     .hwndOwner = lngFormHwnd
  103.     .hInstance = lngAppInstance
  104.     .lpstrFilter = strFileFilter
  105.     .nFilterIndex = 1
  106.     .lpstrFile = String(257, 0)
  107.     .nMaxFile = Len(SaveFile.lpstrFile) - 1
  108.     .lpstrFileTitle = SaveFile.lpstrFile
  109.     .nMaxFileTitle = SaveFile.nMaxFile
  110.     .lpstrInitialDir = strInitDir
  111.     .lpstrTitle = "Export To"
  112.     .Flags = 0
  113.     .lpstrDefExt = ".xls"   'sets default file extension to Excel, in case user does not type it
  114. End With
  115.  
  116. X = GetSaveFileName(SaveFile)
  117. If X = 0 Then
  118.     mstrFileName = "none"
  119.     mblnStatus = False
  120. Else
  121.     mstrFileName = Trim(SaveFile.lpstrFile)
  122.     mblnStatus = True
  123. End If
  124. 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:
'************************
Expand|Select|Wrap|Line Numbers
  1.  Dim cDlg As New CommonDialogAPI     'instantiate CommonDialog
  2. Dim lngFormHwnd As Long
  3. Dim lngAppInstance As Long
  4. Dim strInitDir As String
  5. Dim strFileFilter As String
  6. Dim lngResult As Long
  7.  
  8. lngFormHwnd = Me.Hwnd
  9. lngAppInstance = Application.hWndAccessApp
  10. strInitDir = "C:\"
  11.  
  12. strFileFilter = "Excel Files (*.xls)" & _
  13.     Chr(0) & "*.xls" & Chr(0) & _
  14.     "Text Files (*.csv, *.txt)" & _
  15.     Chr(0) & "*.csv; *.txt" & Chr(0)
  16.  
  17. lngResult = cDlg.SaveFileDialog(lngFormHwnd, _
  18.     lngAppInstance, strInitDir, strFileFilter)
  19.  
  20. If cDlg.GetStatus = True Then
  21.     MsgBox "You selected file: " & cDlg.GetName
  22.     strSavePath = cDlg.GetName  'assign selected path to variable to be passed to TransferSpreadsheet Method
  23. Else
  24.     MsgBox "No file selected."
  25. 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!
Feb 26 '07 #1
Share this Question
Share on Google+
2 Replies


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

Replace
Expand|Select|Wrap|Line Numbers
  1.  
  2. .lpstrFile = String(257, 0)
with
Expand|Select|Wrap|Line Numbers
  1.  
  2. .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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. .Flags = OFN_OVERWRITEPROMPT    'warns the user before overwriting an existing file
(instead of .Flags = 0)

if you don't want existing files overwritten automatically.
Feb 27 '07 #2

NeoPa
Expert Mod 15k+
P: 31,606
Sorry that no-one was able to help with your problem. Thanks anyway for posting the solution :)
Feb 28 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.