VBA Reset

[JimLecocq]

Hello All,

I am running multiple processes in Access via VBA. I have added supposed solutions to VBA I found on the Microsoft Website but they have not been a complete fix. The seemed to have helped some things but not the entire process. I know this does not tell you much but the code is very long and envolved so I hate to paste in on the forum.

Here is my question. I have used the reset button in VBA and can then rerun my code and it works fine. But I tried using VBA.Reset and it will not run. It does not seem to do the same thing as clicking the reset button. Does any one know of a code that will function the same as the Reset button in VBA?

 

[Moniker]

VBA.Reset just closes all the open files in a DB.

The equivalent of clicking on Reset is this:

DoCmd.RunCommand acCmdReset

However, I don't know why you'd want to do this in code (and honestly, I'm not sure you can programmatically do this while code is actually running). What is happening to where you want to perform a reset from code, which essentially stops all running code? What situation are you coming up against where that's necessary? It sounds like there's a deeper issue here.

 

[JimLecocq]

What is happening is, I output queries to Excel. Then I need to open them and combine them on one worksheet. I can open Access and run one of these procedures but there are four of them. When I try running the succeeding procedures I get Runtimer error 462 "The Remote server machine does not exist or is unavailable".

The only way I have been able to get them all to work is to terminate all Excel processes then close Access or Reset VBA and run the next procedure.

The Microsoft website said to set everything (worksheets, workbooks, etc.) to Nothing but that does not fix the problem either. I thought if I could reset VBA through an error handler I could rerun the procedure from another.

There maybe a better way but that is where I am at the moment.

 

[Moniker]

There is a better way.

Not only should you set everything to nothing (and make sure it's everything), you need to explicitly refer to Excel each time you use it.

So, make sure you are closing the Application, the workbook, and the worksheet with something like this:

Set XLApp = Nothing
Set XLWbk = Nothing
Set XLSht = Nothing

Then, while using your Excel object, refer to it explicitly each time you reference it. In other words, everytime you refer to the Worksheet object, address it like this:

XLSht.Range( blah blah )

And not like this:

Range( blah blah )

Don't ask me why that works, but it does. Everywhere you reference the external object (Excel in this case), you have to explicitly refer to it. Otherwise, you'll get that confusing, non-sensical "remote server" BS error message that really tells you nothing.

I don't remember if you need even more explicit declarations like XLApp.XLSht.Range, but I don't think you do. This is one of those bizarre anomalies I discovered a year or two ago and the solution was the explicit declarations.

 

[JimLecocq]

OK, thanks for that. I will try to go through the code again and make sure I have everything declared and set.

 

[boblarson]

Also, make sure you are using explicit references when referring to the Excel COM objects and not by using like

Activesheet.Range("A1").Select

and instead use your workbook object

xlWB.Activesheet.Range("A1").Select

If you don't use an explicit object, the Excel instance you used won't close until Access closes and then that can cause your issues.

 

[Moniker]

Also, note that we are giving Excel examples, but this is true for most external applications. I know you need to do this in Word as well. I don't know if it's just Office applications, but if you start coming across this weird error with other applications, explicit declaration will probably solve it.