Error when creating Excel file from Access VBA

[sierra467]

Hello,

I am trying to figure out how to click on a button in an Access Form to create an Excel spreadsheet with certain rows and columns hidden. This specific example is supposed to create an Excel workbook with a single sheet named "TheOnlyWorksheet" containing only the autofited "A1" Cell containing the words, I am the only one". This Excel file should remain open until closed by the user within Excel (Access should remain open at all times).

I can push the button in the access form and get the resulting spreadsheet displayed as I thought it would. However, once closing Excel (from within excel) and pushing the button in the access form again, the spreadsheet does not appear - instead I get the error: "Run-time error '91'; Object variable or With block variable not set" and it stops on the line ".Range("B:B", Selection.End(xlToRight)).Select"

I am not sure what that means or how to fix it.

I also went into Task Manager after the first run of the code and having closed Excel, but EXCEL.EXE is still listed in the Process list - why did it not close when I closed the excel program?

To make it more strange, after the first run that works correctly, if I go into Task Manager and end the EXCEL.EXE Process, I then get an error "Run-time error '462'; The remote server machine does not exist or is unavailable". Pressing the Debug button shows me that it stops the code on the same line again, ".Range("B:B", Selection.End(xlToRight)).Select"


Below is the code for the button on the access form?

Private Sub cmdHide_Click()

''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Must Reference Microsoft Excel 11.0 Object Library '
'               Tools>References...                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''
'   Declare variables
    Dim objXLApp As New Excel.Application
    Dim objXLWorkbook As Excel.Workbook
    Dim objXLWorksheet As Excel.Worksheet
        
'   Create an instance of Excel workbook with one sheet in Excel
    
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLWorkbook = objXLApp.Workbooks.Add
    Set objXLWorksheet = objXLApp.Sheets(1)

'   Specify how many worksheets there will be in the new workbook
    objXLApp.SheetsInNewWorkbook = 1

'   Name single sheet
    With objXLWorksheet
        .Select
        .Name = "TheOnlyWorksheet"
    End With

'--------------------------------------------------------------------
'   Hide Cell Ranges
    'Must do after making aplication visible or else it does not work
    'Hide Columns
    With objXLWorksheet
        .Columns("B:B").Select
        .Range("B:B", Selection.End(xlToRight)).Select
    End With
    Selection.EntireColumn.Hidden = True
    

    'Hide Rows
    With objXLWorksheet
        .Rows("2:2").Select
        .Range(Selection, Selection.End(xlDown)).Select
    End With
    Selection.EntireRow.Hidden = True
'---------------------------------------------------------------------

'   Enter Data into cells and fit cells to text
    Range("A1").Value = "I am the only one"
    Columns("A:A").EntireColumn.AutoFit

'   Make Excel Application visible
    objXLApp.Visible = True

'   close objects
    Set objXLWorksheet = Nothing
    Set objXLWorkbook = Nothing
    Set objXLApp = Nothing
    
End Sub

Any Help would be greatly appreciated - Thanks in advance.