Jul 24 2023 07:56 AM
Jul 24 2023 07:56 AM
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.
Jul 25 2023 04:36 AMSolution
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:
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:
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.
Jul 25 2023 05:40 AM