INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

GetSaveAsName with predefined directory location Access/Excel Automation

GetSaveAsName with predefined directory location Access/Excel Automation

(OP)
I am using access to create an excel file and perform a save as. The only part that doesn't seem to be working is defining the starting directory. It does put in the file name correctly. I have reviewed some of the threads and tried the suggestions such as below and they didn't seem to alter the starting directory. The starting directory continues to show as whatever the last one I pointed to via the mouse was. Is it because I'm trying to save it from Access rather than within Excel or because I'm trying to save to a network drive instead of hard disk, or do I need to use UNC instead of drive letter? Note: drive letter is defined by IT for all users so there is no issue of people having a different drive letter assignment.

CODE -->

FName = wb.Application.GetSaveAsFilename(InitialFileName:=stDefaultName, FileFilter:= _
                " Excel Macro Free Workbook (*.xlsx), *.xlsx", _
                FilterIndex:=2, Title:="Save to a new workbook") 
I tried defining the default name as follows:
stDefaultName = "F:\Project Reports\Output\Upload1_" & Format(Forms!frmmain.txtEndDate, "yyyymmdd") & ".xlsx"

The directory does not display as "F", stays at "C"

I also tried:

CODE -->

ChDrive Forms!frmmain.txtLocation & " Reports\Output\"
        ChDir Forms!frmmain.txtLocation & " Reports\Output\" 
When I step through code and do a dir statement to the immediate window, it shows the directory has been changed, but maybe that is only changing the directory for Access and has no impact on Excel?

RE: GetSaveAsName with predefined directory location Access/Excel Automation

Hi,

GetSaveAsFilename is an Excel method, not an Access method. In order to use GetSaveAsFilename, you must first instantiate an Excel Application object.

However, there is no need to use GetSaveAsFilename in this instance, since you already have the entire path and filename. In order to save a workbook, you must 1) have an Excel Aplication Object and 2) use the Excel SaveAs method...

CODE

Dim xl as Object
Set xl = GetObject("Excel.Application")
xl.Workbooks.SaveAs "F:\Project Reports\Output\Upload1_" & Format(Forms!frmmain.txtEndDate, "yyyymmdd") & ".xlsx" 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: GetSaveAsName with predefined directory location Access/Excel Automation

(OP)
Hi Skip, that was my starting point, what I was trying to do was expand the capability by using the GetSaveAsFilename so when the save as file dialog box appeared, everything would be filled out ready to go to the proper location, while at the same time allowing the user to change the location should there be a need to save the file somewhere other than the default location/name. Perhaps I'll need to create a prompt with inputbox instead?

RE: GetSaveAsName with predefined directory location Access/Excel Automation

https://msdn.microsoft.com/en-us/library/office/ff...

Quote:


Remarks

  • This string passed in the FileFilter argument consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters, text and addin: "Text Files (*.txt), *.txt, Add-In Files (*.xla), *.xla".

  • To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, "Visual Basic Files (*.bas; *.txt),*.bas;*.txt".

  • This method returns the selected file name or the name entered by the user. The returned name may include a path specification. Returns False if the user cancels the dialog box.

  • This method may change the current drive or folder.

  • ...emphasis mine.

    Skip,

    glassesJust traded in my OLD subtlety...
    for a NUance!tongue

    RE: GetSaveAsName with predefined directory location Access/Excel Automation

    (OP)
    I reread that, still unclear how I get the

    Quote:

    current drive or folder
    set before the dialog opens so that user sees the predefined directory. I assume that the emphasis part refers to the user being able to change the directory after the fact. Appreciate you looking into it. Guess I'll have to tell them that if they want to save in the default location, they have to manually select it as the price to pay for having the familiar point and click interface to have the choice of saving elsewhere and/or renaming the file.

    RE: GetSaveAsName with predefined directory location Access/Excel Automation

    (OP)
    Found this which seems to meet my need. Brings up the file dialog, shows the preferred starting directory and displays the default filename. User can then click on Save button to accept default or is free to change the location, change the name or both. Note: even though user is clicking on Save button, this code is not actually saving the file, only returning the file path and file name so that it can be used in the save code I already have. Only part that sometimes gave an error for me was the line
    .FilterIndex = 2 
    If it happens again, I'll try to get the error message etc.

    This goes in the event that would be for doing the save:

    CODE -->

    FName = GetSaveFilename(FilePath & FileName & ".xlsx") 

    This goes in a standard code module

    CODE -->

    Public Function GetSaveFilename(FileLoc As String) As String
    'https://stackoverflow.com/questions/7770030/preset-the-save-as-type-field-while-using-application-filedialogmsofiledialog
    '20170612
        Dim Dialog As FileDialog
        
        Set Dialog = Application.FileDialog(msoFileDialogSaveAs)
        
        With Dialog
            .InitialFileName = FileLoc 'CurrentProject.Path & "\*.ext"
            .FilterIndex = 2
            .title = "Save As"
            If .Show <> 0 Then
                GetSaveFilename = .SelectedItems(1)
            End If
        End With
    End Function 

    RE: GetSaveAsName with predefined directory location Access/Excel Automation

    You do not manage (add/delete) Filters of FileDialog object, so FileDialog with setting FilterIndex=2 is out of your control.

    combo

    RE: GetSaveAsName with predefined directory location Access/Excel Automation

    (OP)
    So best to comment or remove that line? I wonder why OP included it?

    RE: GetSaveAsName with predefined directory location Access/Excel Automation

    Almost certainly a legacy of earlier code

    Red Flag This Post

    Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

    Red Flag Submitted

    Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
    The Tek-Tips staff will check this out and take appropriate action.

    Reply To This Thread

    Posting in the Tek-Tips forums is a member-only feature.

    Click Here to join Tek-Tips and talk with other members!

    Resources

    Close Box

    Join Tek-Tips® Today!

    Join your peers on the Internet's largest technical computer professional community.
    It's easy to join and it's free.

    Here's Why Members Love Tek-Tips Forums:

    Register now while it's still free!

    Already a member? Close this window and log in.

    Join Us             Close