Trying to use the code below. If I select two files for example, and one of them is longer than 255 characters (including path), Excel will crash. It ignores my error handler and just crashes. I cannot figure out a way to get past this. Any ideas?
VBA to Select Multiple Files when one is too long
- vbmike73
- Thread is marked as Resolved.
-
-
-
Hello,
Instead of creating an Array ...
Have you tried to use a standard loop ...?
HTH
-
I don't think I could use a standard loop in this case because I'm using the GetOpenFilename and it has to collect multiple files form the selection. Correct me if I'm wrong, but I don't see how.
-
Hello,
Below is an example to adapt to your needs :
Code
Display MoreSub LoopAllExcelFilesInFolder() 'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them 'SOURCE: www.TheSpreadsheetGuru.com Dim wb As Workbook Dim myPath As String Dim myFile As String Dim myExtension As String Dim FldrPicker As FileDialog 'Optimize Macro Speed Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual 'Retrieve Target Folder Path From User Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker) With FldrPicker .Title = "Select A Target Folder" .AllowMultiSelect = False If .Show <> -1 Then GoTo NextCode myPath = .SelectedItems(1) & "\" End With 'In Case of Cancel NextCode: myPath = myPath If myPath = "" Then GoTo ResetSettings 'Target File Extension (must include wildcard "*") myExtension = "*.xls*" 'Target Path with Ending Extention myFile = Dir(myPath & myExtension) 'Loop through each Excel file in folder Do While myFile <> "" 'Set variable equal to opened workbook Set wb = Workbooks.Open(Filename:=myPath & myFile) 'Ensure Workbook has opened before moving on to next line of code DoEvents 'Change First Worksheet's Background Fill Blue wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174) 'Save and Close Workbook wb.Close SaveChanges:=True 'Ensure Workbook has closed before moving on to next line of code DoEvents 'Get next file name myFile = Dir Loop 'Message Box when tasks are completed MsgBox "Task Complete!" ResetSettings: 'Reset Macro Optimization Settings Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
Hope this will help
-
Thanks, but this looks like it will only loop through all files in a folder. I need the user to select a single file or multiple files, thus the need for GetOpenFilename. And that has to be a variant. Maybe I'm still missing something, but I don't see how this code applies to my situation.
-
-
In your initial post, you are referring to the limit of 255 characters (including path) ...
Could you build your Array with the Files' Names ... without the path ...
or test each element of your Array to get rid of the exceptions ...
-
Ok, but I'm still not getting past the fact that I need to use GetOpenFilename to have the user select the files they want. There is no other way that I know of to ask a user to select files on their system. Given that, this action alone is what fails when one of the filenames is too long. I can't "build an array" of filenames if I don't know what files they want to choose, and the only way to get that information is to use GetOpenFilename.
-
What about creating a UserForm which would list of all the ' candidate ' Files ... allowing your User to Select the ones he needs ...
-
Maybe this could be a starting point for you
-
Should you want to dive into FSO to handle folders and subfolders ... below is a reference site :
https://wellsr.com/vba/2018/ex…ith-vba-filesystemobject/
Hope this will help
-
-
appreciate the help
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!