SOLVED

Help for VBA code

Copper Contributor

Hi everyone, 

 

I'm going to write this post in English to have the high chance to get an answer but it's not my langage.

 

My problem :

I have an Excel file, this Excel contains link to open more than 300 words' files. The words' files are on my computer and the "link" are the path to open words' files into my windows explorer. 

 

The words' files are on several folder and i want to copy all the words' files (which are in different Folders) in one, general, folder. 

 

I can't do it one-by-one, it's possible to create a VBA to automate that ?

 

I am on Excel 2013 with windows.

 

I hope it's understable.

 

thanks. 

 

3 Replies
best response confirmed by Lcoco20258 (Copper Contributor)
Solution

@Lcoco20258 

It is possible to create a VBA macro to automate the process of copying all the Word files from different folders into one general folder. Below is an outline of the steps you can take to achieve this:

  1. Prompt the user to select the destination folder where all the Word files will be copied.
  2. Loop through all the cells containing the links to the Word files.
  3. Extract the file paths from the links.
  4. Use the FileCopy function to copy the Word files from their original location to the destination folder.

Here is the VBA code to accomplish this task:

vba code (untested):

Sub CopyWordFiles()
    Dim destinationFolder As String
    Dim sourceFilePath As String
    Dim destinationFilePath As String
    Dim cell As Range
    
    ' Prompt user to select the destination folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Destination Folder"
        If .Show = -1 Then
            destinationFolder = .SelectedItems(1) & "\"
        Else
            Exit Sub ' User canceled
        End If
    End With
    
    ' Loop through the cells containing the links to Word files
    For Each cell In ActiveSheet.UsedRange
        If InStr(1, cell.Formula, "file://") > 0 Then
            ' Extract the file path from the link
            sourceFilePath = Replace(cell.Formula, "file:///", "")
            destinationFilePath = destinationFolder & GetFileName(sourceFilePath)
            
            ' Copy the Word file to the destination folder
            FileCopy sourceFilePath, destinationFilePath
        End If
    Next cell

    MsgBox "Word files copied successfully.", vbInformation
End Sub

Function GetFileName(ByVal filePath As String) As String
    ' Function to extract the filename from the full file path
    GetFileName = Mid(filePath, InStrRev(filePath, "\") + 1)
End Function

Here's how to use the code:

  1. Open your Excel file.
  2. Press Alt + F11 to open the VBA Editor.
  3. Click Insert from the top menu and then choose Module.
  4. Paste the above code into the newly created module.
  5. Close the VBA Editor and return to your Excel file.
  6. Press Alt + F8 to open the "Macros" dialog.
  7. Select CopyWordFiles from the list and click Run.

The macro will prompt you to select the destination folder where you want to copy all the Word files. After selecting the destination folder, the macro will loop through the cells containing the links, extract the file paths, and copy the Word files to the specified folder.

Please note that this macro assumes that the links in your Excel file are of the format file:///C:/Path/To/WordFile.docx. If your links have a different format, you may need to adjust the code accordingly.

Make sure to test the macro on a copy of your Excel file before using it on your original file to avoid any potential data loss or unintended consequences. Since no one has replied to this for over a day and more, I entered your question in various AI's. The text and the steps are the result of the compilation of different AI's.

 

My answers are voluntary and without guarantee!

 

 

Hope this will help you.

Hi, thanks for your answer. It was a failur but i did it one by one. It Took my 3hours but it's done now.

Thanks for help !
I am glad that this helped you.
1 best response

Accepted Solutions
best response confirmed by Lcoco20258 (Copper Contributor)
Solution

@Lcoco20258 

It is possible to create a VBA macro to automate the process of copying all the Word files from different folders into one general folder. Below is an outline of the steps you can take to achieve this:

  1. Prompt the user to select the destination folder where all the Word files will be copied.
  2. Loop through all the cells containing the links to the Word files.
  3. Extract the file paths from the links.
  4. Use the FileCopy function to copy the Word files from their original location to the destination folder.

Here is the VBA code to accomplish this task:

vba code (untested):

Sub CopyWordFiles()
    Dim destinationFolder As String
    Dim sourceFilePath As String
    Dim destinationFilePath As String
    Dim cell As Range
    
    ' Prompt user to select the destination folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Destination Folder"
        If .Show = -1 Then
            destinationFolder = .SelectedItems(1) & "\"
        Else
            Exit Sub ' User canceled
        End If
    End With
    
    ' Loop through the cells containing the links to Word files
    For Each cell In ActiveSheet.UsedRange
        If InStr(1, cell.Formula, "file://") > 0 Then
            ' Extract the file path from the link
            sourceFilePath = Replace(cell.Formula, "file:///", "")
            destinationFilePath = destinationFolder & GetFileName(sourceFilePath)
            
            ' Copy the Word file to the destination folder
            FileCopy sourceFilePath, destinationFilePath
        End If
    Next cell

    MsgBox "Word files copied successfully.", vbInformation
End Sub

Function GetFileName(ByVal filePath As String) As String
    ' Function to extract the filename from the full file path
    GetFileName = Mid(filePath, InStrRev(filePath, "\") + 1)
End Function

Here's how to use the code:

  1. Open your Excel file.
  2. Press Alt + F11 to open the VBA Editor.
  3. Click Insert from the top menu and then choose Module.
  4. Paste the above code into the newly created module.
  5. Close the VBA Editor and return to your Excel file.
  6. Press Alt + F8 to open the "Macros" dialog.
  7. Select CopyWordFiles from the list and click Run.

The macro will prompt you to select the destination folder where you want to copy all the Word files. After selecting the destination folder, the macro will loop through the cells containing the links, extract the file paths, and copy the Word files to the specified folder.

Please note that this macro assumes that the links in your Excel file are of the format file:///C:/Path/To/WordFile.docx. If your links have a different format, you may need to adjust the code accordingly.

Make sure to test the macro on a copy of your Excel file before using it on your original file to avoid any potential data loss or unintended consequences. Since no one has replied to this for over a day and more, I entered your question in various AI's. The text and the steps are the result of the compilation of different AI's.

 

My answers are voluntary and without guarantee!

 

 

Hope this will help you.

View solution in original post