Hello,
I know there have been numerous posts regarding a ghost instance of Excel being left behind (shows up as a process running in task manager) when formatting an Excel file from VBA (access). I am not using late binding and have tried to not leave any piece unattached to an already created Excel object. The objects are closed properly (I believe) but yet the instance that is opened to format the Excel file is not closing when it should. I have combed the internet for days and am at a standstill. I commented out all the lines of code that do the formatting and have been adding lines back (uncommenting them) one by one. I was able to narrow it down to determine which piece of code is causing the problem so to speak but for the life of me, I do not understand why or cannot figure out how to fix it. The problem is when I set the borders around the range. I was able to set a small range and get the borders to work properly and the instance of Excel closes. When I set the range for a larger area and use the cell function to identify the last cell at the bottom and right, the borders are proerly created but the instance of Excel remains open.
I just dont' get it. The odd thing is that the exact same code is in another sub routine and it doesn't leave Excel open and from what i could tell (and my co-worker could tell), the code is identical so why it would work on one and not the other is another enigma. I hope this is making sense! Nonethless, I am hoping someone can help me rectify this and point out what is wrong. Below is the code from the sub routine (I inherited it from someone else and yes, I realize the variables are not named properly but i can correct that once i get this working 100%). The problem is right below the rTest range part. (it is commented out right now and the rTest was my attempt at doing it differently so I started with a single row first.) Any help/comments/feedback would be greatly appreciated! Thank you!
Private Sub EBFormatDwn(sFile As String, sType As String)
Dim oExcel As Excel.Application
Dim oWorksheet As Excel.Worksheet
Dim oWorkbook As Excel.Workbook
Dim rRng As Excel.Range
Dim rVal As Excel.Range
Set oExcel = CreateObject("Excel.Application")
Set oWorkbook = oExcel.Workbooks.Open(sFile)
With oWorkbook
With .Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
End With
Set oWorksheet = oWorkbook.ActiveSheet
With oWorksheet
With .Cells
.Font.Name = "Arial"
.Font.Size = "8"
.EntireColumn.AutoFit
.WrapText = True
.Replace "00/00/00", ""
'.Sort key1:=[A1], order1:=xlAscending, key2:=[D1], order2:=xlAscending, key3:=[H1], order3:=xlAscending, Header:=True
End With
With .Rows(1)
.Font.Bold = True
.WrapText = True
.HorizontalAlignment = xlCenter
End With
.[A2].Select
With .Application.ActiveWindow
.FreezePanes = True
.DisplayGridlines = False
End With
.[A:A].ColumnWidth = 6.5
Set rRng = oExcel.Range("F2:F" & .[A2].End(xlDown).Row)
For Each rVal In rRng
rVal = Format(rVal, "mm/dd/yyyy")
.Range("H" & rVal.Row) = Format(.Range("H" & rVal.Row), "mm/dd/yyyy")
.Range("I" & rVal.Row) = Format(.Range("I" & rVal.Row), "mm/dd/yyyy")
.Range("J" & rVal.Row) = Format(.Range("J" & rVal.Row), "mm/dd/yyyy")
.Range("N" & rVal.Row) = Format(.Range("N" & rVal.Row), "mm/dd/yyyy")
.Range("Q" & rVal.Row) = Format(.Range("Q" & rVal.Row), "mm/dd/yyyy")
.Range("T" & rVal.Row) = Format(.Range("T" & rVal.Row), "mm/dd/yyyy")
.Range("W" & rVal.Row) = Format(.Range("W" & rVal.Row), "mm/dd/yyyy")
Next rVal
.[F:F].NumberFormat = "#,##0"
.[K:K].NumberFormat = "$#,##0.00"
.Cells.EntireColumn.AutoFit
.[A:A, E:E, G:G, M:M, P, S:S,V:V].HorizontalAlignment = xlCenter
.[E:E].ColumnWidth = 5
.[G:G].ColumnWidth = 6.7
.[1:4].Insert
With .[A1]
.Value = "E-Book Report - " & sType
.HorizontalAlignment = xlLeft
.Font.Size = 12
.Font.Bold = True
.WrapText = False
End With
Dim rtest As Excel.Range
Set rtest = oExcel.Range("A5:W" & .[A2].End(xlDown).Row)
rtest.Select
rtest.Borders.Weight = xlHairline
rtest.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
'With .Range("A5:" & Cells(.[B5].End(xlDown).Row, .[A5].End(xlToRight).Column).Address)
' .Borders.Weight = xlHairline
' .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
'End With
'.Range("A5:" & Cells(5, .[A5].End(xlToRight).Column).Address).BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
With .PageSetup
.LeftMargin = .Application.InchesToPoints(0.25)
.RightMargin = .Application.InchesToPoints(0.25)
.TopMargin = .Application.InchesToPoints(0.5)
.BottomMargin = .Application.InchesToPoints(0.5)
.HeaderMargin = .Application.InchesToPoints(0.5)
.FooterMargin = .Application.InchesToPoints(0.5)
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.PrintGridlines = True
.FirstPageNumber = xlAutomatic
.Zoom = 63
.PrintTitleRows = "$1:$5"
End With
End With
oWorkbook.Close True
oExcel.Quit
Set oWorksheet = Nothing
Set oWorkbook = Nothing
Set oExcel = Nothing
End Sub
I know there have been numerous posts regarding a ghost instance of Excel being left behind (shows up as a process running in task manager) when formatting an Excel file from VBA (access). I am not using late binding and have tried to not leave any piece unattached to an already created Excel object. The objects are closed properly (I believe) but yet the instance that is opened to format the Excel file is not closing when it should. I have combed the internet for days and am at a standstill. I commented out all the lines of code that do the formatting and have been adding lines back (uncommenting them) one by one. I was able to narrow it down to determine which piece of code is causing the problem so to speak but for the life of me, I do not understand why or cannot figure out how to fix it. The problem is when I set the borders around the range. I was able to set a small range and get the borders to work properly and the instance of Excel closes. When I set the range for a larger area and use the cell function to identify the last cell at the bottom and right, the borders are proerly created but the instance of Excel remains open.
I just dont' get it. The odd thing is that the exact same code is in another sub routine and it doesn't leave Excel open and from what i could tell (and my co-worker could tell), the code is identical so why it would work on one and not the other is another enigma. I hope this is making sense! Nonethless, I am hoping someone can help me rectify this and point out what is wrong. Below is the code from the sub routine (I inherited it from someone else and yes, I realize the variables are not named properly but i can correct that once i get this working 100%). The problem is right below the rTest range part. (it is commented out right now and the rTest was my attempt at doing it differently so I started with a single row first.) Any help/comments/feedback would be greatly appreciated! Thank you!Private Sub EBFormatDwn(sFile As String, sType As String)
Dim oExcel As Excel.Application
Dim oWorksheet As Excel.Worksheet
Dim oWorkbook As Excel.Workbook
Dim rRng As Excel.Range
Dim rVal As Excel.Range
Set oExcel = CreateObject("Excel.Application")
Set oWorkbook = oExcel.Workbooks.Open(sFile)
With oWorkbook
With .Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
End With
Set oWorksheet = oWorkbook.ActiveSheet
With oWorksheet
With .Cells
.Font.Name = "Arial"
.Font.Size = "8"
.EntireColumn.AutoFit
.WrapText = True
.Replace "00/00/00", ""
'.Sort key1:=[A1], order1:=xlAscending, key2:=[D1], order2:=xlAscending, key3:=[H1], order3:=xlAscending, Header:=True
End With
With .Rows(1)
.Font.Bold = True
.WrapText = True
.HorizontalAlignment = xlCenter
End With
.[A2].Select
With .Application.ActiveWindow
.FreezePanes = True
.DisplayGridlines = False
End With
.[A:A].ColumnWidth = 6.5
Set rRng = oExcel.Range("F2:F" & .[A2].End(xlDown).Row)
For Each rVal In rRng
rVal = Format(rVal, "mm/dd/yyyy")
.Range("H" & rVal.Row) = Format(.Range("H" & rVal.Row), "mm/dd/yyyy")
.Range("I" & rVal.Row) = Format(.Range("I" & rVal.Row), "mm/dd/yyyy")
.Range("J" & rVal.Row) = Format(.Range("J" & rVal.Row), "mm/dd/yyyy")
.Range("N" & rVal.Row) = Format(.Range("N" & rVal.Row), "mm/dd/yyyy")
.Range("Q" & rVal.Row) = Format(.Range("Q" & rVal.Row), "mm/dd/yyyy")
.Range("T" & rVal.Row) = Format(.Range("T" & rVal.Row), "mm/dd/yyyy")
.Range("W" & rVal.Row) = Format(.Range("W" & rVal.Row), "mm/dd/yyyy")
Next rVal
.[F:F].NumberFormat = "#,##0"
.[K:K].NumberFormat = "$#,##0.00"
.Cells.EntireColumn.AutoFit
.[A:A, E:E, G:G, M:M, P
, S:S,V:V].HorizontalAlignment = xlCenter.[E:E].ColumnWidth = 5
.[G:G].ColumnWidth = 6.7
.[1:4].Insert
With .[A1]
.Value = "E-Book Report - " & sType
.HorizontalAlignment = xlLeft
.Font.Size = 12
.Font.Bold = True
.WrapText = False
End With
Dim rtest As Excel.Range
Set rtest = oExcel.Range("A5:W" & .[A2].End(xlDown).Row)
rtest.Select
rtest.Borders.Weight = xlHairline
rtest.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
'With .Range("A5:" & Cells(.[B5].End(xlDown).Row, .[A5].End(xlToRight).Column).Address)
' .Borders.Weight = xlHairline
' .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
'End With
'.Range("A5:" & Cells(5, .[A5].End(xlToRight).Column).Address).BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
With .PageSetup
.LeftMargin = .Application.InchesToPoints(0.25)
.RightMargin = .Application.InchesToPoints(0.25)
.TopMargin = .Application.InchesToPoints(0.5)
.BottomMargin = .Application.InchesToPoints(0.5)
.HeaderMargin = .Application.InchesToPoints(0.5)
.FooterMargin = .Application.InchesToPoints(0.5)
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.PrintGridlines = True
.FirstPageNumber = xlAutomatic
.Zoom = 63
.PrintTitleRows = "$1:$5"
End With
End With
oWorkbook.Close True
oExcel.Quit
Set oWorksheet = Nothing
Set oWorkbook = Nothing
Set oExcel = Nothing
End Sub
