Please try this updated version:
Code:
'**********************************
Public Sub Format_Excel_Borrower()
'**********************************
Dim objExcel As Object
Dim objFso As Object
Dim objFolder As Object
Dim objFile As Object
Dim objWorkbook As Object
Dim sh As Object
Dim ColumnCount As Integer
Dim j, i As Integer
Dim strFirstShName As String
Dim strExcel_Date As String
Dim strBranch As String
Dim strDate As String
On Error GoTo err_Handler
strFirstShName = ""
strDate = Date
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(Get_DBPath)
For Each objFile In objFolder.Files
If objFso.GetExtensionName(objFile.Path) = "xlsx" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
For Each sh In objWorkbook.Worksheets
Debug.Print sh.Name
If sh.Name = "vwExportExcel_1" Then
Else
GoTo endfunc
End If
With sh '.Name
.Range("A1:M1").Select
With Selection.Font
.Color = RGB(255, 255, 255)
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 192
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End With
If sh.UsedRange.Address <> "$A$1" Or sh.Range("A1") <> "" Then
With sh
ColumnCount = .Cells(1, 256).End(xlToLeft).Column
For j = 1 To ColumnCount
With .Cells(1, j)
i = Len(.Value)
If j = 1 Then
.ColumnWidth = 5
ElseIf j = 2 Then
.ColumnWidth = 12
ElseIf j = 3 Then
.ColumnWidth = 9
ElseIf j = 4 Then
.ColumnWidth = 15
ElseIf j = 5 Then
.ColumnWidth = 6
ElseIf j = 6 Then
.ColumnWidth = 19
ElseIf j = 7 Then
.ColumnWidth = 15
ElseIf j = 8 Then
.ColumnWidth = 9
ElseIf j = 9 Then
.ColumnWidth = 9
ElseIf j = 10 Then
.ColumnWidth = 9
ElseIf j = 11 Then
.ColumnWidth = 18
ElseIf j = 12 Then
.ColumnWidth = 10
ElseIf j = 13 Then
.ColumnWidth = 18
Else
.ColumnWidth = 10
End If
End With
'sh.Name
Next
'Freeze Pane
'Range("A2").Select
'ActiveWindow.FreezePanes = True
End With
With sh '.Name Vlad
.Rows("1:3").EntireRow.Insert
.Range("A1").Font.Bold = True
.Range("A1").Font.Size = 13
.Range("A1").Value = "Mers Reonconcilation"
.Range("A1").Font.Bold = True
.Range("A2").Value = "Compare Borrowers"
.Range("A2").Font.Bold = True
.Range("A2").Font.Size = 13
strDate = "Date: " & strDate
.Range("A3").Value = strDate
.Range("A3").Font.Size = 12
.Range("A3").Font.Bold = True
End With
End If
MoveToNextSheet:
Next ' Moves to the Next Sheet
objWorkbook.Worksheets(1).Select
objWorkbook.Close True
End If
Next
endfunc:
objExcel.Quit
If Not objWorkbook Is Nothing Then
Set objWorkbook = Nothing
End If
' Same goes for quitting the application
If Not objExcel Is Nothing Then
objExcel.Quit
Set objExcel = Nothing
End If
Exit Sub
err_Handler:
Debug.Print Err.Description
Resume Next
End Sub
Cheers,
Vlad