Access VBA help needed

[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

[Rich P]

Hi Melissa,

Here is a way better way to write data to Excel from Access

http://support.microsoft.com/default...8973&Product=a
do

This uses ADO. This is way more flexible/reliable and does not require
an Excel Object. I used this for years (until I stepped up to .Net --
that really is the way to go). But for Access, this link really does
the trick -- although you will still need an Excel object for doing the
formatting -- ADO only writes data - no formatting.

Anyway, the link has a demo app you can try out (or demo code).

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Larry Linson]

"Melissa" <mw*******@snl.comwrote

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:

Actually, I think the problem here is not "Access VBA" but more to do with
use of the Excel object model and named ranges, and such. I would guess if
you were using VBA in any software to do those Excel object model
manipulations, you'd encounter similar situations.

If you set "Break on all errors", perhaps you could pin it down to a
particular statement.

And, if it only occurs sometime, certainly the logical thing to pursue would
be "What's different about the times when it fails?"

And, as an object that is being tested is the Excel range, it's a good
candidate to be the object that is causing the problem. A debug.print to
display the Range, etc., in the Immediate Window just before executing this
code may (1) get the same error, so you'll have pinned it down to the range
not being properly set on entry or (2) give you some indication of the range
currently chosen and its properties.

Larry Linson
Microsoft Access MVP

[Alok]

On Aug 23, 10:21 am, Melissa <[e-mail]:

I currently haveVBAwritten to export query results into anExcel
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

Hi Melissa,

As a matter of practice, it is not necessary nor advisable to select
anything in Excel. Your code should work find with

xlWs.Range("A2").End(xlDown).Row

rather than the two lines you have now, namely

xlWs.Range("A2").End(xlDown).Select
l = Selection.Row

This will also probably fix the error.

Alok

[Melissa]

On Aug 24, 11:38 am, Alok <[e-mail]:

On Aug 23, 10:21 am, Melissa <[e-mail]:

I currently haveVBAwritten to export query results into anExcel
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

Hi Melissa,

As a matter of practice, it is not necessary nor advisable to select
anything in Excel. Your code should work find with xlWs.Range("A2").End(xlDown).Row

rather than the two lines you have now, namely

xlWs.Range("A2").End(xlDown).Select
l = Selection.Row

This will also probably fix the error.

Alok

This solved the problem. Thank you! ! ! Now I'll go fix this issue
in another database.