Why am I getting excel object variable or with block variable when running code?

[anoble1]

Hi,

In Access, I am getting error: excel object variable or with block variable when running code. The code runs fine the first time when I start it. When I close excel and hit the button again it gets hung below where it assigns the name. It never makes it to where it assigns the name. The only time it works is when I hit the Stop button in Access. or restart Access. Then it renames the sheet.

Private Sub Command11_Click()
Dim fso
Dim processfpath As String
Dim filename As String
Dim TransFile As String
Dim xlApp As Excel.Application
Dim xlWkbk As Excel.workBook
Dim xlSht As Excel.workSheet
Dim fileSave As String
Dim sheetname As String
 
' get the business unit of the requesting LCS
Requestor = Me.Requestor.Value
strSQL = "SELECT tblUsers.UserName, tblUsers.BusinessUnit FROM tblUsers  WHERE [UserName]='" & Forms!frmProcess!Requestor & "';"
Set rs = CurrentDb.OpenRecordset(strSQL)
business = rs.Fields("BusinessUnit")
 
'Set the application
Set xlApp = New Excel.Application
 
'Make the Application Visible
xlApp.Visible = True
 
'Set the workbook and the filepath
Set xlWkbk = xlApp.Workbooks.Open("H:\TESTING\Golden Springs DS 21342.xlsx")
 
Set xlSht = ActiveSheet
 
xlSht.Name = ("TEST2")
'Here is where the error happens above.
 
 
processfpath = Me.FilePath.Value
 
Set xlApp = Nothing
Set xlWkbk = Nothing
Set xlSht = Nothing
 

[anoble1]

Fixed it.

' get the business unit of the requesting LCS
Requestor = Me.Requestor.Value
strSQL = "SELECT tblUsers.UserName, tblUsers.BusinessUnit FROM tblUsers  WHERE [UserName]='" & Forms!frmProcess!Requestor & "';"
Set rs = CurrentDb.OpenRecordset(strSQL)
business = rs.Fields("BusinessUnit")
 
'Set the application
'
 
 
 
 
'Make the Application Visible
xlApp.Visible = True
 
Set xlApp = New Excel.Application
 
xlApp.Visible = True
 
 
'Set the workbook and the filepath 'Change "C:\Book1.xls " to your own filepath and Workbook name
'Set xlWkbk = xlApp.Workbooks.Open("S:\Workgroups\APC Power Delivery-Contract Services\Trim Notices\Distribution Trim Notices\Golden Springs DS 21342.xlsx")
Set xlWkbk = xlApp.Workbooks.Open("H:\TESTING\Golden Springs DS 21342.xlsx")