[AccessD] Use of Application.FileDialog(msoFileDialogSaveAs) inAccess 2007 - Question on how to plug in file name

Stuart,

The code you sent earlier works nicely.
 
The files in the correct folder are shown based on the path that I
supply.

In some cases, however, I would like to also be able to "plug" in a file
name and give the user the chance to either use this file name or enter
a different file name.

Is there a way to plug in the file name also?

When I supply the entire path (with the file name) such as
C:\Test1\SaveAsTest.txt the files in the folder "C:\Test1" are shown but
the "File Name" field is blank on the form.

Thanks for your help.  This is my first attempt to work with
GetSaveFileName and I am still trying to figure things out.

Brad

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Friday, January 21, 2011 1:22 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Use of
Application.FileDialog(msoFileDialogSaveAs) inAccess 2007

Use the GetSaveFileName API.  

Paste the following into a module and then use GetSaveFile()  in your
exporting function.


Code:

Declare Function GetSaveFileName Lib "comdlg32.dll" Alias _
         "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Long
   
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

Function GetSaveFile(Directory As String) As String
         Dim OpenFile As OPENFILENAME
         Dim lReturn As Long
         Dim sFilter As String
         OpenFile.lStructSize = Len(OpenFile)
         OpenFile.hwndOwner = 0
         OpenFile.hInstance = 0
         sFilter = "" & Chr(0)
         OpenFile.lpstrFilter = sFilter
         OpenFile.nFilterIndex = 0
         OpenFile.lpstrFile = String(257, 0)
         OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
         OpenFile.lpstrFileTitle = OpenFile.lpstrFile
         OpenFile.nMaxFileTitle = OpenFile.nMaxFile
         OpenFile.lpstrInitialDir = Directory
         OpenFile.lpstrTitle = "Select File"
         OpenFile.flags = 0
         lReturn = GetOpenFileName(OpenFile)
         GetSaveFile = Left$(OpenFile.lpstrFile,
InStr(OpenFile.lpstrFile, Chr$(0)) - 1)
End Function




On 21 Jan 2011 at 9:55, Brad Marks wrote:

> All,
> 
> I have just started to experiment with
> Application.FileDialog(msoFileDialogSaveAs).
> 
> We have an Access 2007 application that uses
> "DoCmd.TransferSpreadsheet" to generate several Excel files from
> Access Queries.  Currently this application has the name of the Excel
> file "hard coded" in the application.
> 
> I would like to give our end-users the option of changing the name of
> the Excel file (and folder name) when the file is generated. 
> 
> Using  Application.FileDialog(msoFileDialogSaveAs) seems to work
> nicely, but I am a bit concerned that this may not be a good long-term
> approach.
> 
> When doing research about this method, I stumbled upon this statement
> on a Microsoft web page.
> 
> " msoFileDialogSaveAs constants are not supported in Microsoft Office
> Access 2007"
> 
> This, and other comments that I have read have made me wonder if I am
> heading down the wrong path. (no pun intended) 
> 
> Is Application.FileDialog(msoFileDialogSaveAs) a viable approach?
> 
> Is there a better way to give the end-users an easy way to change the
> name (and folder) of the generated Excel file?
> 
> Thanks,
> Brad
> 




-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.