Object variable or With block variable not set

[Whasigga]

Hello

I am working on a project to export data from a query into an Excel file. I have a form with a button on it and the button when clicked runs a function that is supposed to run the query and store it into a recordset, create a new Excel file, dump the contents into the Excel file and do some cell formatting on it to make it look decent.

I have put it together piece by piece, and it works fine up until I try to add cell formatting. I get the error "Object variable or With block variable not set" in my code. I first got "Method 'Range' of object '_Global' failed" on the line when I selected a range of cells, but only after it ran successfully once. Looking around online it seemed that I needed to set my objects that I created to Nothing, so that Access would not have them held up anymore. I did that, or at least tried to set them to nothing, but it still doesn't work. With the code I have now, it gives the the Object variable or with block error, at line 59 of my code. I'm not sure if it is still the same problem of Access still not completely letting go from the first time the function ran or if it's something else. Does anybody have any suggestions?

thanks
Josh

Private Sub Command0_Click()
    'create and set connection to current database
    Dim conn As ADODB.Connection
    Set conn = CurrentProject.Connection
 
    'create and connect new recordset to the new connection
    Dim myRecordSet As New ADODB.Recordset
    myRecordSet.ActiveConnection = conn
 
    'pop up input box to retrieve Officer Badge
    Dim strInput As String
    strInput = InputBox("Enter Officer Badge", "Officer Badge")
 
    'assign the SQL query that pulls all records matching SOBadge to strSQL
    Dim strSQL As String
    strSQL = "SELECT tblDRIVEINPASS.SOBadge, tblDRIVEINPASS.* FROM tblDRIVEINPASS "
    strSQL = strSQL + "WHERE ((tblDRIVEINPASS.SOBadge = '" & strInput & "'));"
 
    'populate the recordset with results from the query
    myRecordSet.Open strSQL
 
    'create excel application to push query results to
    Dim xlApp As Excel.Application
    Dim xlWorkbook As Excel.Workbook
    Dim xlsht As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")
    Set xlWorkbook = xlApp.Workbooks.Add
    xlApp.Visible = True
 
    Dim intRows As Integer
    Dim qryResultsCols As Integer
    intRows = 2
 
    xlApp.Sheets("Sheet1").Select
 
    'fill in column headings
    For intCols = 1 To 13 Step 1
        xlApp.Cells(1, intCols).Value = myRecordSet.Fields(intCols - 1).Name
    Next intCols
 
    'fill in records from database
    If (myRecordSet.EOF And myRecordSet.BOF) Then
        'no records to pull - do nothing
    Else
        myRecordSet.MoveFirst
        xlApp.Cells(1, 1).Value = "Officer Badge"
 
 
        Do Until (myRecordSet.EOF)
            For qryResultsCols = 0 To 12 Step 1
                xlApp.Cells(intRows, qryResultsCols + 1).Value = myRecordSet.Fields(qryResultsCols).Value
            Next qryResultsCols
 
            myRecordSet.MoveNext
            intRows = intRows + 1
        Loop
    End If
 
    xlApp.ActiveSheet.Range("A1:M1").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Font
        .Name = "GE Inspira Medium"
        .Size = 12
        .Bold = True
    End With
 
 
Set xlWorkbook = Nothing
Set xlSheet = Nothing
Set xlApp = Nothing
 
myRecordSet.Close
Set myRecordSet = Nothing
 
End Sub

[Stewart Ross]

Hi. Problem is the use of the Selection method, which is available in Excel but not reliably when Access is using Excel as an automation server. You don't need it, and it is considerably faster to refer directly to ranges rather than select them first. Add an Excel range object variable object to your code and change the Select references in lines 60 and beyond as follows:

Dim Cellrange as Excel.Range 
…
    Set CellRange = xlApp.ActiveSheet.Range("A1:M1")
    With CellRange.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With CellRange.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With CellRange.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With CellRange.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With CellRange.Font
        .Name = "GE Inspira Medium"
        .Size = 12
        .Bold = True
    End With

you may also find the discussion in the later part of the following post relevant for more detailed explanations: http://www.thescripts.com/forum/thread784045.html.

-Stewart

[Whasigga]

Awesome, that seems to have helped. Thanks for your suggestion