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.