Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1

    Answered: Saving Files in VBA

    I have some code (below) that has the user open a dialog box and choose a filename. I want to (1) save that file to a folder with a different filename and (2) then open the file. It would be a good start if I could do word and excel files, but ideally this would work for any file type (word, excel, any photo files, csv, txt, etc.).

    Can provide more info if needed. Thanks

    Code:
        Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
        With fDialog
            ' Allow user to make multiple selections in dialog box
            .AllowMultiSelect = False
                   
            ' Set the title of the dialog box.
            .Title = "Please select a file"
            
            ' Clear out the current filters, and add our own.
            .Filters.Clear
            .Filters.Add "All Files", "*.*"
            
            If .Show = True Then
                If .SelectedItems.Count = 1 Then
                     For Each selectedFiles In .SelectedItems
                         docToImport = selectedFiles
                         
                     Next
                Else
                    MsgBox "File Error", , "Error"
                    GoTo Exit_Handler
                End If
            Else
                MsgBox "File import canceled."
                GoTo Exit_Handler
            End If
        End With

  2. Best Answer
    Posted by Sinndho

    "When you write "then open the file", I suppose that you mean open the file with the appropriate program according to its type (i.e.use Excel to open a .xls file, use NotePad to open a .txt file, etc.).

    You need to perform 3 operations : select a file, copy it to another dir and open the copy with the appropriate program.

    1. Select a file.

    Using the Application.FileDialog object to pick up a file is ok, however you probably copied the sample from Access help without understanding how it works. If you set the AllowMultiSelect property to False, there is only one item in the SelectedItems collection and the "For Each selectedFiles In .SelectedItems ... Next" loop is useless.

    2. Copy a file to another folder.

    Several methods are available. I opted to use a FileSystemObject object from the Scripting library which offers a FileCopy method.

    3. Open a file with the appropriate program.

    Windows has a ShellExecute function in the shell32.dll library which does precisely that (it's the function that is invoked when you double-click on a file in Windows Explorer).

    altogether, here's a procedure that will perform the 3 operations. You can copy the code in a standard module:
    Code:
    Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                                 (ByVal hwnd As Long, ByVal lpszOp As String, _
                                  ByVal lpszFile As String, ByVal lpszParams As String, _
                                  ByVal LpszDir As String, ByVal FsShowCmd As Long) As Long
    
    Sub CopyAndOpenFile(ByVal SourcePath As String, ByVal DestinationPath As String)
    
        Const SW_SHOWNORMAL As Long = 1
        
        Dim objFileDialog As Object
        Dim objFileSystemObject As Object
        Dim varSelectedFile As Variant
        Dim strFileName As String
        Dim strCommand As String
        
        Set objFileDialog = Application.FileDialog(msoFileDialogFilePicker)
        With objFileDialog
        
            ' Allow user to select only one file in dialog box.
            .AllowMultiSelect = False
            
            ' Set the intitial folder.
            '
            .InitialFileName = SourcePath
            
            ' Set the title of the dialog box.
            .Title = "Please select a file"
            
            ' Clear out the current filters, and add our own.
            .Filters.Clear
            .Filters.Add "All Files", "*.*"
            
            ' Display the FileDialog box.
            '
            If .Show = True Then    ' One file was selected.
            
                ' Retrieve the name of the selected file (full path).
                varSelectedFile = .SelectedItems(1)
                
                ' Extract the file name (name.ext).
                strFileName = Mid(varSelectedFile, InStrRev(varSelectedFile, "\") + 1)
                
                ' use the FileSystemObject to copy the file to the destination path.
                If Right(DestinationPath, 1) <> "\" Then DestinationPath = DestinationPath & "\"
                Set objFileSystemObject = CreateObject("Scripting.FileSystemObject")
                objFileSystemObject.CopyFile varSelectedFile, DestinationPath & strFileName
                
                ' Open the copied file.
                ShellExecute 0, "open", DestinationPath & strFileName, vbNullString, vbNullString, SW_SHOWNORMAL
                
            Else                    ' User cancelled the selection.
                MsgBox "File import canceled."
                GoTo Exit_Handler
            End If
        End With
    
    Exit_Handler:
    
        ' Clean up.
        Set objFileDialog = Nothing
        Set objFileSystemObject = Nothing
        Exit Sub
    
    End Sub
    "


  3. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When you write "then open the file", I suppose that you mean open the file with the appropriate program according to its type (i.e.use Excel to open a .xls file, use NotePad to open a .txt file, etc.).

    You need to perform 3 operations : select a file, copy it to another dir and open the copy with the appropriate program.

    1. Select a file.

    Using the Application.FileDialog object to pick up a file is ok, however you probably copied the sample from Access help without understanding how it works. If you set the AllowMultiSelect property to False, there is only one item in the SelectedItems collection and the "For Each selectedFiles In .SelectedItems ... Next" loop is useless.

    2. Copy a file to another folder.

    Several methods are available. I opted to use a FileSystemObject object from the Scripting library which offers a FileCopy method.

    3. Open a file with the appropriate program.

    Windows has a ShellExecute function in the shell32.dll library which does precisely that (it's the function that is invoked when you double-click on a file in Windows Explorer).

    altogether, here's a procedure that will perform the 3 operations. You can copy the code in a standard module:
    Code:
    Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                                 (ByVal hwnd As Long, ByVal lpszOp As String, _
                                  ByVal lpszFile As String, ByVal lpszParams As String, _
                                  ByVal LpszDir As String, ByVal FsShowCmd As Long) As Long
    
    Sub CopyAndOpenFile(ByVal SourcePath As String, ByVal DestinationPath As String)
    
        Const SW_SHOWNORMAL As Long = 1
        
        Dim objFileDialog As Object
        Dim objFileSystemObject As Object
        Dim varSelectedFile As Variant
        Dim strFileName As String
        Dim strCommand As String
        
        Set objFileDialog = Application.FileDialog(msoFileDialogFilePicker)
        With objFileDialog
        
            ' Allow user to select only one file in dialog box.
            .AllowMultiSelect = False
            
            ' Set the intitial folder.
            '
            .InitialFileName = SourcePath
            
            ' Set the title of the dialog box.
            .Title = "Please select a file"
            
            ' Clear out the current filters, and add our own.
            .Filters.Clear
            .Filters.Add "All Files", "*.*"
            
            ' Display the FileDialog box.
            '
            If .Show = True Then    ' One file was selected.
            
                ' Retrieve the name of the selected file (full path).
                varSelectedFile = .SelectedItems(1)
                
                ' Extract the file name (name.ext).
                strFileName = Mid(varSelectedFile, InStrRev(varSelectedFile, "\") + 1)
                
                ' use the FileSystemObject to copy the file to the destination path.
                If Right(DestinationPath, 1) <> "\" Then DestinationPath = DestinationPath & "\"
                Set objFileSystemObject = CreateObject("Scripting.FileSystemObject")
                objFileSystemObject.CopyFile varSelectedFile, DestinationPath & strFileName
                
                ' Open the copied file.
                ShellExecute 0, "open", DestinationPath & strFileName, vbNullString, vbNullString, SW_SHOWNORMAL
                
            Else                    ' User cancelled the selection.
                MsgBox "File import canceled."
                GoTo Exit_Handler
            End If
        End With
    
    Exit_Handler:
    
        ' Clean up.
        Set objFileDialog = Nothing
        Set objFileSystemObject = Nothing
        Exit Sub
    
    End Sub
    Have a nice day!

  4. #3
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    Thanks, I found the below for my copy function, which works like a charm and am still deciding if I want to use allen browne's openhyperlink function or shell for the file open. This looks great though, will definitely take a look at this to see if I can integrate any of it. Thanks for taking the time!

    Code:
     Dim fso As Object
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")
    Call fso.CopyFile(source, destination[, overwrite] )

  5. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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