Run-time 91 error, not sure why
Posted on 2016-11-10
Hi everyone. I'm trying to change the formatting of a MS Excel spreadsheet via MS Access using VBA, but I'm running into an error every other time the code runs. When I run the code the first time it works fine, but if I try a second time I get "Run-time error '91': Object variable or With block variable not set"
The code I'm using is below. All the variables here appear to be named and defined properly so I'm not sure why this is happening:
Dim xl As Object
Dim xlWkbk As Object
Dim xlsheet As Object
Set xl = Excel.Application
Set xlWkbk = xl.ActiveWorkbook
Set xlsheet = xlWkbk.Sheets("Sheet1")
xlsheet.Name = "rename"
Set xl = Nothing
Set xlWkbk = Nothing
Set xlsheet = Nothing
When I try debugging, it tells me the error lies on line "Set xlsheet = xlWkbk.Sheets("Sheet1")" Does anyone have any guess as to what the problem is here?
Thanks in advance for your help!