none
[Access 2k3 + VBA] Object variable or With block variable not set (Error 91) RRS feed

  • Question

  • Hi everyone,

    I use this code to export data from Access ListBox to Excel:

    'EXPORT to EXCEL
    Public Sub ExportExcelCD()
        Dim myExApp As Excel.Application    'variable for Excel App
        Dim myExSheet As Excel.Worksheet    'variable for Excel Sheet
        Dim i As Long                       'variable for ColumnCount
        Dim j As Long                       'variable for ListCount
        Set myExApp = New Excel.Application
        
        myExApp.Visible = True              'Sets Excel visible
        myExApp.Workbooks.Add               'Add a new Workbook
        Set myExSheet = myExApp.Workbooks(1).Worksheets(1)
        
        myExSheet.Cells.Select              'Select active worksheet
        Selection.NumberFormat = "@"        'Format all fields to text entry to avoid long number to be truncated
        
        myExSheet.Select                    'Select active worksheet
        myExSheet.Name = "Export1"     'Rename first sheet to tab name
        
    For i = 1 To Form_Main.LstBox.ColumnCount
        For j = 1 To Form_Main.LstBox.ListCount
            myExSheet.Cells(j, i) = Form_Main.LstBox.Column(i - 1, j - 1)
        Next j
    Next i
        
        Set myExSheet = Nothing 'Release Worksheet
        Set myExApp = Nothing   'Release Excel Application
    End Sub

    This code work for 1 run, if I run it second time without closing the whole application and opening it again I get this error message:

    Object variable or With block variable not set (Error 91)

    and it points to this line:

    Selection.NumberFormat = "@"        'Format all fields to text entry to avoid long number to be truncated

    How to fix this, please?

    Best Regards

    Friday, June 15, 2012 3:38 PM

Answers

  • When using Automation, you must take great care to make all objects that belong to the "called" application refer to YOUR application object, directly or indirectly.

    In the line Selection.NumberFormat = "@", the Selection object does not refer in any way to the Excel application object myExApp, so it becomes an "orphan". It should be

        myExApp.Selection.NumberFormat = "@"

    or even better, don't select cells, and set the number format directly to myExSheet.Cells. That is, replace the TWO lines

        myExSheet.Cells.Select
        Selection.NumberFormat = "@"

    with the single line

        myExSheet.Cells.NumberFormat = "@"


    Regards, Hans Vogelaar

    • Marked as answer by Admin-Dev Friday, June 15, 2012 8:43 PM
    Friday, June 15, 2012 5:13 PM

All replies

  • When using Automation, you must take great care to make all objects that belong to the "called" application refer to YOUR application object, directly or indirectly.

    In the line Selection.NumberFormat = "@", the Selection object does not refer in any way to the Excel application object myExApp, so it becomes an "orphan". It should be

        myExApp.Selection.NumberFormat = "@"

    or even better, don't select cells, and set the number format directly to myExSheet.Cells. That is, replace the TWO lines

        myExSheet.Cells.Select
        Selection.NumberFormat = "@"

    with the single line

        myExSheet.Cells.NumberFormat = "@"


    Regards, Hans Vogelaar

    • Marked as answer by Admin-Dev Friday, June 15, 2012 8:43 PM
    Friday, June 15, 2012 5:13 PM
  • myExSheet.Cells.NumberFormat = "@"

    Works like a charm :)
    Friday, June 15, 2012 8:44 PM