Access

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: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
 
P

Pendragon

Do you know which line is specifically failing?

Melissa said:
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: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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top