Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / November 2006

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
slimleh - 03 Nov 2006 20:04 GMT
I have a Access VBA code and I like to remove the a Excel tab.

Can anyone help?
Thanks
strive4peace - 04 Nov 2006 03:01 GMT
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
  *

> I have a Access VBA code and I like to remove the a Excel tab.
>
> Can anyone help?
> Thanks
BruceS - 04 Nov 2006 14:54 GMT
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

> Hi Slim,
>
[quoted text clipped - 74 lines]
> > Can anyone help?
> > Thanks
strive4peace - 04 Nov 2006 18:26 GMT
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
  *

> Crystal,
>
[quoted text clipped - 88 lines]
>>> Can anyone help?
>>> Thanks
BruceS - 04 Nov 2006 19:03 GMT
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

> Hi Bruce,
>
[quoted text clipped - 129 lines]
> >>> Can anyone help?
> >>> Thanks
strive4peace - 04 Nov 2006 20:16 GMT
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
  *

> Crystal,
>
[quoted text clipped - 153 lines]
>>>>> Can anyone help?
>>>>> Thanks
BruceS - 04 Nov 2006 21:29 GMT
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

> Hi Bruce,
>
[quoted text clipped - 197 lines]
> >>>>> Can anyone help?
> >>>>> Thanks
strive4peace - 05 Nov 2006 18:44 GMT
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
  *

> Crystal,
>
[quoted text clipped - 207 lines]
>>>>>>> Can anyone help?
>>>>>>> Thanks
RoyVidar - 04 Nov 2006 18:45 GMT
"BruceS" <BruceS@discussions.microsoft.com> wrote in message
<68DFDEB4-A93D-45B8-966E-717241DD7609@microsoft.com>:
> Crystal,
>
[quoted text clipped - 89 lines]
>>> Can anyone help?
>>> Thanks

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.

Signature

Roy-Vidar

strive4peace - 04 Nov 2006 20:18 GMT
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
  *

> "BruceS" <BruceS@discussions.microsoft.com> wrote in message
> <68DFDEB4-A93D-45B8-966E-717241DD7609@microsoft.com>:
[quoted text clipped - 101 lines]
>
> prior to your close line.
RoyVidar - 05 Nov 2006 00:31 GMT
"strive4peace" <strive4peace2006@yahoo.com> wrote in message
<uOAC$XEAHHA.3308@TK2MSFTNGP03.phx.gbl>:
> thanks Roy!
>
[quoted text clipped - 5 lines]
> 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.

Signature

Roy-Vidar

BruceS - 04 Nov 2006 21:22 GMT
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

> "BruceS" <BruceS@discussions.microsoft.com> wrote in message
> <68DFDEB4-A93D-45B8-966E-717241DD7609@microsoft.com>:
[quoted text clipped - 101 lines]
>
> prior to your close line.
RoyVidar - 05 Nov 2006 00:32 GMT
"BruceS" <BruceS@discussions.microsoft.com> wrote in message
<B8D1AFA1-494B-4DB4-A73B-0FFA1A994D45@microsoft.com>:
> Roy,
>
[quoted text clipped - 4 lines]
>
> Bruce

You are welcome!

Signature

Roy-Vidar

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.