+ Reply to Thread
Results 1 to 3 of 3

file-picker thinks it is a folder-picker if the folder picker runs first?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Portsmouth, VA now, Falmouth, VA 4 yrs, Palm Bay, FL for 2 yrs, was Colorado Springs, CO for ten years; Cedark Park, TX; Zeeland, MI; Wilmette, IL; Princeton Junction, NJ; NY, NY
    MS-Off Ver
    365
    Posts
    617

    file-picker thinks it is a folder-picker if the folder picker runs first?

    Hi again and Happy New Years' Eve!!!

    So one of the first things I want to do in this little project is allow the user the opportunity to save the workbook before my code shreds through it. Some time ago I found some code on the web that worked great in another project, so I brought it over. That code is the second code block below, which is called from the first code block.

    The first code block just has a very little bit extracted for I was "lucky" and that is where all of my current headache resides. As you see in the first code block I set a variable to my file-picker, then offer (and presumably run) the back-up that allows the user to choose a folder in which to place the backup by the folder-picker, and it backs up the workbook.

    After that runs we are back to the first code block and the file-picker runs. BUT: IF the folder picker has run, my dang file-picker thinks it is a folder picker! If I do not run the back-up procedure, the file picker runs perfectly. What important fact am I missing?


        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        Set wb0 = ActiveWorkbook
    
        If MsgBox("Would you like to create a back-up file before proceeding?", vbYesNo) = vbYes Then
            Application.StatusBar = "...Please Wait - Performing Backup"
            Call SaveWB
        End If
    
    
        'Select workbook from which to import sheets with a fail-safe to abort the sub if no file is selected
    
        'Select the file to import from
        If fd.Show = -1 Then
            selFile = fd.SelectedItems(1)




    Private Sub SaveWB()
    
        Dim diaFolder As FileDialog
        Dim strFolderPath As String
        Dim savedate As Date
        Dim savetime As Date
        Dim formattime As String
        Dim formatdate As String
    
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
        ' Open the file dialog
        Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
        diaFolder.AllowMultiSelect = False
        diaFolder.Show
        strFolderPath = diaFolder.SelectedItems(1)
    
        'Saves the current file to a backup folder
        savedate = Date    ' Current system date
        savetime = Time    ' Current system time
        formattime = Format(savetime, "hhMMss")
        formatdate = Format(savedate, "YYMMDD")
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveCopyAs Filename:=strFolderPath & "\" & formatdate & formattime & " " & ActiveWorkbook.Name
        ActiveWorkbook.Save
        Application.DisplayAlerts = True
        MsgBox "Backup Run - Stored in: " & strFolderPath
    
    End Sub

    Thanks for your thoughts and Happy New Year!!!

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,659

    Re: file-picker thinks it is a folder-picker if the folder picker runs first?

    I can duplicate the problem. I don't know why it works that way, but the fix is to define fd as a filepicker after you call the folderpicker.

        Set wb0 = ActiveWorkbook
        
        If MsgBox("Would you like to create a back-up file before proceeding?", vbYesNo) = vbYes Then
            Application.StatusBar = "...Please Wait - Performing Backup"
            Call SaveWB
        End If
        
        'Select workbook from which to import sheets with a fail-safe to abort the sub if no file is selected
        
        'Select the file to import from
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        If fd.Show = -1 Then
            selFile = fd.SelectedItems(1)
    Or just...
        Set wb0 = ActiveWorkbook
        
        If MsgBox("Would you like to create a back-up file before proceeding?", vbYesNo) = vbYes Then
            Application.StatusBar = "...Please Wait - Performing Backup"
            Call SaveWB
        End If
        
        'Select workbook from which to import sheets with a fail-safe to abort the sub if no file is selected
        
        'Select the file to import from
        With Application.FileDialog(msoFileDialogFilePicker)
            If .Show = -1 Then
                selFile = .SelectedItems(1)
                
            End If
        End With
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Portsmouth, VA now, Falmouth, VA 4 yrs, Palm Bay, FL for 2 yrs, was Colorado Springs, CO for ten years; Cedark Park, TX; Zeeland, MI; Wilmette, IL; Princeton Junction, NJ; NY, NY
    MS-Off Ver
    365
    Posts
    617

    Re: file-picker thinks it is a folder-picker if the folder picker runs first?

    Thank-you, and Happy New Year!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] save As worksheet via Folder Picker
    By clementraj1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-01-2016, 04:49 PM
  2. [SOLVED] 64 bit API query-SendMessageString (Upgrading Bovey/Bullen Folder Picker)
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-14-2015, 08:07 PM
  3. Folder Picker - picking files in chronological order
    By jcroque89 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-28-2015, 05:12 PM
  4. File Picker
    By rajwar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-06-2014, 11:46 AM
  5. Folder picker breaks for Mac
    By skania in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 05-14-2013, 11:59 AM
  6. File dialog list picker
    By julhs in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-01-2013, 12:42 PM
  7. File Picker problem
    By noodle48 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-01-2011, 11:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1