473,320 Members | 2,073 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Default FileName with a Save As (OPENFILENAME) dialog box

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
2 10939
vbaDev
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
32,556 Expert Mod 16PB
Sorry that no-one was able to help with your problem. Thanks anyway for posting the solution :)
Feb 28 '07 #3

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

Similar topics

3
by: S.W. Rasmussen | last post by:
With the risk of being accused of multi-posting I would like to draw the attention to a serious visual basic/windows issue discussed in the microsoft.public.vb.bugs newsgroup. As pointed out below...
1
by: ASH | last post by:
Hello all, I'm using the "API: Call Windows File Open/Save Dialog Box" to source the name of a file to open. The Dialog Box when called opens in the top left hand corner of the screen. I have...
1
by: Wayne | last post by:
I'm using the following code from the Access Web to open a folder. The folder opens in "List" View. Is there an addition that I can make to the code to force the folder to open in "Details" view?...
1
by: mnlarsen | last post by:
How do I set the default location to "My Computer" when using the GetOpenFileName call? I have tried the following but CSIDL_DRIVES doesn't appear to work. Any ideas? WCHAR szFile =...
0
by: abraham | last post by:
Hi In an .aspx codebehind file I retrieve a file from a database and write it to the response buffer, so the browser pops up a 'save file' dialog and the user can save the file to disk. The...
0
by: Olav Tollefsen | last post by:
I have an .aspx file with the following code in Form_Load: image = System.Drawing.Image.FromFile(imageFilename); Response.ContentType = "image/jpeg"; image.Save(Response.OutputStream,...
2
by: Leo | last post by:
I am using OPENFILENAME and GetSaveFileName to let user save a document out. However, the default name won't show up in the dialog and the selected filter can't be obtained (always got the default...
13
by: Hemant Sipahimalani | last post by:
The following piece of code is being used to export HTML to excel. HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"...
2
by: Dulwich | last post by:
As a Newbie to VBA, I've just had a successful conclusion to a protracted struggle with attempts to open a browse window that allows one to find a file and have that file's path saved in a bound...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.