M
Melissa
I currently have VBA written to export query results into an Excel
file. That file is then formatted using the code below. The problem
I'm having is that it keeps throwing Error 91 (Object variable or With
block variable not set) at this part of the code:
If xlWs.Range("A3") <> "" Then
xlWs.Range("A2").End(xlDown).Select
l = Selection.Row
Debug.Print "l = " & l
ElseIf xlWs.Range("A2") <> "" Then
l = 2
End If
I'm at a loss since sometimes it works flawlessly and other times it
crashes. Can someone please take a look at this and help me out?
Thanks!
Melissa
Sub FormatBrokerReport()
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim arrColsToHide(6) As Variant
Dim i As Byte
Dim xlRng As Excel.Range
Dim j As Long
Dim l As Integer
Dim m As Integer
Dim xlAutoFilterRange As Excel.Range
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open(strBrokerReport)
Set xlWs = xlApp.Worksheets("QRY_Broker Report - New & Pendi")
'Rename the worksheet.
xlWs.Name = "Increased Access Requests"
Set xlWs = xlApp.Worksheets("Increased Access Requests")
'Insert a new column and name its column heading.
Set xlRng = xlWs.Columns("E:E")
With xlRng
.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
End With
Set xlRng = Nothing
xlWs.Cells(1, 5).Value = "Response"
xlWs.Cells(1, 6).Value = "Embargo Period"
xlWs.Cells(1, 7).Value = "Payment Option"
'Set the data validation fields.
xlWs.Cells(1, 25).Value = "Status"
xlWs.Cells(2, 25).Value = "Approved - Company"
xlWs.Cells(3, 25).Value = "Approved - User"
xlWs.Cells(4, 25).Value = "Denied"
xlWs.Cells(1, 26).Value = "Delay Period"
xlWs.Cells(2, 26).Value = "Real-time"
xlWs.Cells(3, 26).Value = "1-Day"
xlWs.Cells(4, 26).Value = "2-Day"
xlWs.Cells(5, 26).Value = "3-Day"
xlWs.Cells(6, 26).Value = "4-Day"
xlWs.Cells(7, 26).Value = "5-Day"
xlWs.Cells(8, 26).Value = "6-Day"
xlWs.Cells(9, 26).Value = "7-Day"
xlWs.Cells(10, 26).Value = "8-Day"
xlWs.Cells(11, 26).Value = "9-Day"
xlWs.Cells(12, 26).Value = "10-Day"
xlWs.Cells(13, 26).Value = "11-Day"
xlWs.Cells(14, 26).Value = "12-Day"
xlWs.Cells(15, 26).Value = "13-Day"
xlWs.Cells(16, 26).Value = "14-Day"
xlWs.Cells(17, 26).Value = "15-Day"
xlWs.Cells(18, 26).Value = "No Access"
xlWs.Cells(1, 27).Value = "Payment Required"
xlWs.Cells(2, 27).Value = "Free"
xlWs.Cells(3, 27).Value = "Pay"
'Format column headings.
Set xlRng = xlWs.Range("A1:AA1")
With xlRng
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 11
.Font.ColorIndex = 2
.Font.Bold = True
End With
Set xlRng = Nothing
'Resize the columns.
xlWs.Columns("A:AA").EntireColumn.AutoFit
xlWs.Columns("E:G").ColumnWidth = 17.71
'Determine the number of the last row of the worksheet.
l = 0
If xlWs.Range("A2") = "" Then
MsgBox ("No Data Exists")
Exit Sub
End If
If xlWs.Range("A3") <> "" Then
xlWs.Range("A2").End(xlDown).Select
l = Selection.Row
Debug.Print "l = " & l
ElseIf xlWs.Range("A2") <> "" Then
l = 2
End If
'Set validation lists.
For m = 2 To l
With xlWs.Cells(m, 5).Validation
.Add Type:=xlValidateList, Formula1:="=$Y$2:$Y$4"
.InCellDropdown = True
.ShowInput = True
End With
With xlWs.Cells(m, 5)
.Font.ColorIndex = 5
.Font.Bold = True
.Font.Name = "Arial"
.Font.Size = 8
.HorizontalAlignment = xlCenter
End With
Next m
For m = 2 To l
With xlWs.Cells(m, 6).Validation
.Add Type:=xlValidateList, Formula1:="=$Z$2:$Z$18"
.InCellDropdown = True
.ShowInput = True
End With
With xlWs.Cells(m, 6)
.Font.ColorIndex = 5
.Font.Bold = True
.Font.Name = "Arial"
.Font.Size = 8
.HorizontalAlignment = xlCenter
End With
Next m
For m = 2 To l
With xlWs.Cells(m, 7).Validation
.Add Type:=xlValidateList, Formula1:="=$AA$2:$AA$3"
.InCellDropdown = True
.ShowInput = True
End With
With xlWs.Cells(m, 7)
.Font.ColorIndex = 5
.Font.Bold = True
.Font.Name = "Arial"
.Font.Size = 8
.HorizontalAlignment = xlCenter
End With
Next m
'Hide appropriate columns.
arrColsToHide(1) = "A:A"
arrColsToHide(2) = "B:B"
arrColsToHide(3) = "D
"
arrColsToHide(4) = "Y:Y"
arrColsToHide(5) = "Z:Z"
arrColsToHide(6) = "AA:AA"
For i = 1 To UBound(arrColsToHide)
Set xlRng = xlWs.Range(arrColsToHide(i))
With xlRng
.EntireColumn.Hidden = True
End With
Next i
Set xlRng = Nothing
'Save formatted file.
xlWb.Save
MsgBox ("Formatting Finished")
Set xlApp = Nothing
Set xlWb = Nothing
Set xlWs = Nothing
Set xlRng = Nothing
End Sub
file. That file is then formatted using the code below. The problem
I'm having is that it keeps throwing Error 91 (Object variable or With
block variable not set) at this part of the code:
If xlWs.Range("A3") <> "" Then
xlWs.Range("A2").End(xlDown).Select
l = Selection.Row
Debug.Print "l = " & l
ElseIf xlWs.Range("A2") <> "" Then
l = 2
End If
I'm at a loss since sometimes it works flawlessly and other times it
crashes. Can someone please take a look at this and help me out?
Thanks!
Melissa
Sub FormatBrokerReport()
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim arrColsToHide(6) As Variant
Dim i As Byte
Dim xlRng As Excel.Range
Dim j As Long
Dim l As Integer
Dim m As Integer
Dim xlAutoFilterRange As Excel.Range
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open(strBrokerReport)
Set xlWs = xlApp.Worksheets("QRY_Broker Report - New & Pendi")
'Rename the worksheet.
xlWs.Name = "Increased Access Requests"
Set xlWs = xlApp.Worksheets("Increased Access Requests")
'Insert a new column and name its column heading.
Set xlRng = xlWs.Columns("E:E")
With xlRng
.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
End With
Set xlRng = Nothing
xlWs.Cells(1, 5).Value = "Response"
xlWs.Cells(1, 6).Value = "Embargo Period"
xlWs.Cells(1, 7).Value = "Payment Option"
'Set the data validation fields.
xlWs.Cells(1, 25).Value = "Status"
xlWs.Cells(2, 25).Value = "Approved - Company"
xlWs.Cells(3, 25).Value = "Approved - User"
xlWs.Cells(4, 25).Value = "Denied"
xlWs.Cells(1, 26).Value = "Delay Period"
xlWs.Cells(2, 26).Value = "Real-time"
xlWs.Cells(3, 26).Value = "1-Day"
xlWs.Cells(4, 26).Value = "2-Day"
xlWs.Cells(5, 26).Value = "3-Day"
xlWs.Cells(6, 26).Value = "4-Day"
xlWs.Cells(7, 26).Value = "5-Day"
xlWs.Cells(8, 26).Value = "6-Day"
xlWs.Cells(9, 26).Value = "7-Day"
xlWs.Cells(10, 26).Value = "8-Day"
xlWs.Cells(11, 26).Value = "9-Day"
xlWs.Cells(12, 26).Value = "10-Day"
xlWs.Cells(13, 26).Value = "11-Day"
xlWs.Cells(14, 26).Value = "12-Day"
xlWs.Cells(15, 26).Value = "13-Day"
xlWs.Cells(16, 26).Value = "14-Day"
xlWs.Cells(17, 26).Value = "15-Day"
xlWs.Cells(18, 26).Value = "No Access"
xlWs.Cells(1, 27).Value = "Payment Required"
xlWs.Cells(2, 27).Value = "Free"
xlWs.Cells(3, 27).Value = "Pay"
'Format column headings.
Set xlRng = xlWs.Range("A1:AA1")
With xlRng
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 11
.Font.ColorIndex = 2
.Font.Bold = True
End With
Set xlRng = Nothing
'Resize the columns.
xlWs.Columns("A:AA").EntireColumn.AutoFit
xlWs.Columns("E:G").ColumnWidth = 17.71
'Determine the number of the last row of the worksheet.
l = 0
If xlWs.Range("A2") = "" Then
MsgBox ("No Data Exists")
Exit Sub
End If
If xlWs.Range("A3") <> "" Then
xlWs.Range("A2").End(xlDown).Select
l = Selection.Row
Debug.Print "l = " & l
ElseIf xlWs.Range("A2") <> "" Then
l = 2
End If
'Set validation lists.
For m = 2 To l
With xlWs.Cells(m, 5).Validation
.Add Type:=xlValidateList, Formula1:="=$Y$2:$Y$4"
.InCellDropdown = True
.ShowInput = True
End With
With xlWs.Cells(m, 5)
.Font.ColorIndex = 5
.Font.Bold = True
.Font.Name = "Arial"
.Font.Size = 8
.HorizontalAlignment = xlCenter
End With
Next m
For m = 2 To l
With xlWs.Cells(m, 6).Validation
.Add Type:=xlValidateList, Formula1:="=$Z$2:$Z$18"
.InCellDropdown = True
.ShowInput = True
End With
With xlWs.Cells(m, 6)
.Font.ColorIndex = 5
.Font.Bold = True
.Font.Name = "Arial"
.Font.Size = 8
.HorizontalAlignment = xlCenter
End With
Next m
For m = 2 To l
With xlWs.Cells(m, 7).Validation
.Add Type:=xlValidateList, Formula1:="=$AA$2:$AA$3"
.InCellDropdown = True
.ShowInput = True
End With
With xlWs.Cells(m, 7)
.Font.ColorIndex = 5
.Font.Bold = True
.Font.Name = "Arial"
.Font.Size = 8
.HorizontalAlignment = xlCenter
End With
Next m
'Hide appropriate columns.
arrColsToHide(1) = "A:A"
arrColsToHide(2) = "B:B"
arrColsToHide(3) = "D
"arrColsToHide(4) = "Y:Y"
arrColsToHide(5) = "Z:Z"
arrColsToHide(6) = "AA:AA"
For i = 1 To UBound(arrColsToHide)
Set xlRng = xlWs.Range(arrColsToHide(i))
With xlRng
.EntireColumn.Hidden = True
End With
Next i
Set xlRng = Nothing
'Save formatted file.
xlWb.Save
MsgBox ("Formatting Finished")
Set xlApp = Nothing
Set xlWb = Nothing
Set xlWs = Nothing
Set xlRng = Nothing
End Sub