How to or delete a Excel sheet or tab thru VBA code?

[Guest]

I have a Access VBA code and I like to remove the a Excel tab.

Can anyone help?
Thanks

 

[strive4peace]

Hi Slim,

here is basic code to open a "conversation" with Excel...

'~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Excel_Conversation()

On Error GoTo Proc_Err

Dim xlApp As Excel.Application, _
booLeaveOpen As Boolean

'if Excel is already open, use that instance
booLeaveOpen = True

'attempting to use something that is not available
'will generate an error
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo Proc_Err

'If xlApp is defined, then we
'already have a conversation
If TypeName(xlApp) = "Nothing" Then
booLeaveOpen = False
'Excel was not open -- create a new instance
Set xlApp = CreateObject("Excel.Application")
End If

'Do whatever you want


Proc_Exit:
On Error Resume Next

If TypeName(xlApp) <> "Nothing" Then
xlApp.ActiveWorkbook.Close False
If Not booLeaveOpen Then xlApp.Quit
Set xlApp = Nothing
End If

Exit Function

Proc_Err:
MsgBox Err.Description _
, , "ERROR " & Err.Number & " Excel_Conversation"
'comment next line after debugged
Stop: Resume

Resume Proc_Exit
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~

in the
'Do whatever you want
section...

once you have an object reference to Excel, you can open a workbook and
delete a sheet. We can help you with this, but you can also figure it
out for yourself... turn on the Excel macro recorder and do it -- then
look at the code that was generated and note the important parts

then, translate the code to Access -- simply preface each Excel
statement with xlApp




Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Crystal,

Found your code very helpful for project I'm working on. Only have one
problem, though. When I issue the ActiveWorkbook.Close, I'm getting an error
message that reads:

A file named 'RESUME.XLW' already exists in this location. Do you want to
replace it?

What the heck is "resume.xlw"? How can I prevent the user from having to
respond to the message each time they call my routine?

Bruce

 

[strive4peace]

Hi Bruce,

glad you found the code helpful

"A file named 'RESUME.XLW' already exists in this location."

check to see what files are automatically opening when you start Excel:

Tools, Options, General tab ...
At Startup, open all files in --> some directory

is this where Resume.xlw is? If so, move it out of that directory

You can also do this:

'~~~~~~~~~~~~~~~~~~~~~~~
'put this at the top with other Dim statements

Dim wb as Excel.Workbook
'~~~~~~~~~~~~~~~~~~~~~~~
'put this in the Exit code between
'If TypeName(xlApp) = "Nothing" Then
'AND
'End If

for each wb in xlApp.Workbooks
wb.Close False
next wb
set wb = nothing
'~~~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[RoyVidar]

Crystal,

Found your code very helpful for project I'm working on. Only have
one problem, though. When I issue the ActiveWorkbook.Close, I'm
getting an error message that reads:

A file named 'RESUME.XLW' already exists in this location. Do you
want to replace it?

What the heck is "resume.xlw"? How can I prevent the user from
having to respond to the message each time they call my routine?

Bruce

Resume.xlw is a workspace file.

I would probably be a bit more explicit, declaring and instantiating
a separate workbook object to use in stead of ActiveWorkbook, and see
if that helps. If not, you might try to issue a

xlApp.DisplayAlerts = False

prior to your close line.

 

[Guest]

Crystal,

Checked my Excel options. There is no path listed in "Open all files in.."
box. But, because this is an app to be distributed to multiple users, I
don't think that I could depend upon that setting, anyway. An individual
user could have something different. Also, the new spreadsheet is created in
a folder of the user's choice. They can put it anywhere.

From what I've been able to glean from other sites today, the Resume.xlw
file is created by Excel to store and coordinate spreadsheet data when
accessed by multiple users. Unless my instanciating it from Access creates a
"multi-user" environment, I can't understand why the new spreadsheet I create
is even falling under its purview.

I'll try the code you added below, but I'm afraid that it will close any
workbooks that the user has open when he runs my program. Not
insurmountable, but I would prefer that my app play well with others that
they are running.

Thanks again for your help. If you have any more ideas and I'd love to hear
them.

Bruce

 

[strive4peace]

Hi Bruce,

If the user has Excel open already ... good point!

change
'~~~~~~~~~~~~~~~~~~~~~~~~~~~
If Not booLeaveOpen Then xlApp.Quit
'~~~~~~~~~~~~~~~~~~~~~~~~~~~

to
'~~~~~~~~~~~~~~~~~~~~~~~~~~~
If Not booLeaveOpen Then
for each wb in xlApp.Workbooks
wb.Close False 'False means don't save
next wb
set wb = nothing
xlApp.Quit
End if
'~~~~~~~~~~~~~~~~~~~~~~~~~~~

this way, if Excel was open, it leaves it -- but if Excel was not
already open, it closes all open workbooks and quits. If there are
workbooks you want to save, it should be done before this step

also, to keep Excel from running macros while you are writing to it...

'~~~~~~~~~~~~~~~~~~~~~~~~~~~
xlApp.EnableEvents = False
'~~~~~~~~~~~~~~~~~~~~~~~~~~~

right after you open each file...and, for good measure, I do it before
too... when you open a file, this is set to True for that file unless
you disable it


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

thanks Roy!

xlApp.DisplayAlerts = False
-- good idea!

"Resume.xlw is a workspace file."

how does the workspace file get opened? Is there a way for it not to
open during automation?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Roy,

Specifying the workbook in the .Close, instead of using ActiveWorkbook, did
the trick! No message about Resume.xlw now.

Why, I don't know, but thanks!

Bruce

 

[Guest]

Crystal,

More good advice!

BTW, if you didn't see my post to Roy, specifying the workbook to close,
instead of using ActiveWorkbook, eliminated the Resume.xlw message.

Many thanks,
Bruce

 

[RoyVidar]

thanks Roy!

xlApp.DisplayAlerts = False
-- good idea!

"Resume.xlw is a workspace file."

how does the workspace file get opened? Is there a way for it not to
open during automation?

Some of those questions are probably better addressed in an Excel
NG ;-)

I don't know much about the workspace file, it's just that I've found
that when using some of the implicit referencing methods, it seems one
doesn't always reference what one intends to reference.

Explicit referencing, I've found to be safer.

 

[RoyVidar]

Roy,

Specifying the workbook in the .Close, instead of using
ActiveWorkbook, did the trick! No message about Resume.xlw now.

Why, I don't know, but thanks!

Bruce

You are welcome!

 

[strive4peace]

you're welcome, Bruce happy to help

glad you got it

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*