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

Stuart,

I owe you a beer!

Thanks for the help, I appreciate it.

Brad

 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Monday, January 24, 2011 4:32 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Use of
Application.FileDialog(msoFileDialogSaveAs)inAccess 2007 - Question on
how to plug in file name

Modify the function to take an optional second parameter.

Function GetSaveFile(Directory As String, Optional FileName As String =
"") As String
...
     OpenFile.lpstrFile = Left$(FileName & String(257, 0), 257)
...
End Function

-- 
Stuart

On 24 Jan 2011 at 15:48, Brad Marks wrote:

> 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.
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 



-- 
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.