MS Access Forum / Modules / DAO / VBA / November 2006
How to or delete a Excel sheet or tab thru VBA code?
|
|
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
|
|
|