Hi Guys, I'm back
Need some assistance here. With the said code below. How can we "SAVEAS" the file/s instead of just "CLOSE"ing it.
Just want to close the file properly so that none of those unclose workbook file still hanging around.
Dim i As Long, objFolder As Object, wsFiles As Worksheet
Dim colFiles As Collection, arrFiles, WB, MyPath As String
MyPath = "H:\0.0.1 LEAD REFERRAL\0.1MyFiles\"
'Filename = "UB Tracking"
Set colFiles = GetMatchingFiles(MyPath, "UB Tracking.xlsx")
Debug.Print "Found " & colFiles.Count & " matching files"
ReDim arrFiles(1 To colFiles.Count, 1 To 3) 'size output array
Application.ScreenUpdating = False
For i = 1 To colFiles.Count
Set WB = Workbooks.Open(colFiles(i), ReadOnly:=True)
arrFiles(i, 1) = WB.Path
arrFiles(i, 2) = WB.Name
arrFiles(i, 3) = WB.Sheets(1).UsedRange.Rows.Count - 1
=> WB.Close False '<= Save the file in the same loc, name and ext.
Next i
Application.ScreenUpdating = True
Set wsFiles = ThisWorkbook.Sheets("Sheet1")
wsFiles.Range("F2").Resize(colFiles.Count, 3).Value = arrFiles
Need some assistance here. With the said code below. How can we "SAVEAS" the file/s instead of just "CLOSE"ing it.
Just want to close the file properly so that none of those unclose workbook file still hanging around.
Dim i As Long, objFolder As Object, wsFiles As Worksheet
Dim colFiles As Collection, arrFiles, WB, MyPath As String
MyPath = "H:\0.0.1 LEAD REFERRAL\0.1MyFiles\"
'Filename = "UB Tracking"
Set colFiles = GetMatchingFiles(MyPath, "UB Tracking.xlsx")
Debug.Print "Found " & colFiles.Count & " matching files"
ReDim arrFiles(1 To colFiles.Count, 1 To 3) 'size output array
Application.ScreenUpdating = False
For i = 1 To colFiles.Count
Set WB = Workbooks.Open(colFiles(i), ReadOnly:=True)
arrFiles(i, 1) = WB.Path
arrFiles(i, 2) = WB.Name
arrFiles(i, 3) = WB.Sheets(1).UsedRange.Rows.Count - 1
=> WB.Close False '<= Save the file in the same loc, name and ext.
Next i
Application.ScreenUpdating = True
Set wsFiles = ThisWorkbook.Sheets("Sheet1")
wsFiles.Range("F2").Resize(colFiles.Count, 3).Value = arrFiles