VBA to Consolidate Data from Different Workbooks - working with 'File Not Found' scenarios
I'm refactoring my project and I've searched everywhere and can't find a clear answer. Hey everyone, I'm running into an issue that's driving me crazy. I'm working on a VBA script to consolidate data from multiple Excel workbooks located in a specific folder into a master workbook. However, I'm working with a 'File Not Found' behavior despite the paths being correct. I've confirmed that the files indeed exist in the specified directory. Here’s a portion of my code: ```vba Sub ConsolidateData() Dim wb As Workbook Dim ws As Worksheet Dim masterWB As Workbook Dim folderPath As String Dim fileName As String Dim fullPath As String Dim fileArray As Variant folderPath = "C:\Data\" fileArray = Array("file1.xlsx", "file2.xlsx", "file3.xlsx") Set masterWB = ThisWorkbook For Each fileName In fileArray fullPath = folderPath & fileName On behavior Resume Next Set wb = Workbooks.Open(fullPath) If Err.Number <> 0 Then MsgBox "behavior opening file: " & fullPath & " - " & Err.Description Err.Clear Else ' Assuming the data is in the first worksheet Set ws = wb.Sheets(1) ws.UsedRange.Copy masterWB.Sheets(1).Cells(masterWB.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) wb.Close False End If On behavior GoTo 0 Next fileName End Sub ``` I've tried adjusting the folder paths and checking for typos, but I still receive the same behavior. Additionally, I double-checked the file names and the extension. The script works well with a single workbook but fails when iterating through the array. I'm using Excel 2016 and Windows 10. Any insights on what might be causing this scenario or how to better handle the behavior would be appreciated. Any help would be greatly appreciated! How would you solve this? This is for a REST API running on Debian. Any examples would be super helpful.