Excel automation: how to open a blank spreadsheet
in [Visual Basic]
|
Prev: VB Macro to VB Application
Next: invisible tag for RTF?
From: Bob Butler on 16 Jun 2010 14:44 "GS" <gesansom(a)netscape.net> wrote in message news:hvb5mj$6g8$1(a)news.eternal-september.org... > Dee Earley presented the following explanation : >> On 09/06/2010 07:28, GS wrote: >>> Claire laid this down on his screen : >>>> "Claire" <replyto(a)fra> wrote in message >>>> news:eD4hhm4BLHA.5584(a)TK2MSFTNGP06.phx.gbl... >>>>> Hello, >>>>> When using Excel automation: >>>>> .Open command requires the full path to the file (workbook) >>>>> I need to open just blank, unnamed spreadsheet. >>>>> How to do that? >>>>> Thanks, Claire >>>> I have found that doing: >>>> .Visible = True >>>> and then using .Add it will show opened Excel book containing 3 blank >>>> sheets. >>>> Why there are 3 sheets? >>>> I need to start a book with one blank sheet only. >>>> Thanks, Claire >>> >>> The number of worksheets when adding a new workbook will always be the >>> value of Application.SheetsInNewWorkbook property. To only have 1 sheet >>> in a new workbook: >> >> Or remove them (using code) >> >> I've always hated that default setting, but i suppose no one will notice >> the feature if there is just one. >> A slightly more intelligent design (like IE, chrome, etc) with a "new" >> tab would have done > > Hmm! I suppose that might be a realistic idea. I see Excel12 has a new > approach attached to its "New" menu. Not decided if any approach by M$ is > better than configuring it how I want, though. > > As for automating, it's pretty simple to store the existing property, set > as desired, then restore original afterwards. It's a bit easier to handle > than deleting however many sheets greater than a count of 1. Less coding > required to set as desired!<IMO> not a whole lot of extra coding... oxl.displayalerts=false do while owb.worksheets.count>1 owb.worksheets(1).delete loop oxl.displayalerts=true
From: GS on 16 Jun 2010 15:12
Bob Butler has brought this to us : > "GS" <gesansom(a)netscape.net> wrote in message > news:hvb5mj$6g8$1(a)news.eternal-september.org... >> Dee Earley presented the following explanation : >>> On 09/06/2010 07:28, GS wrote: >>>> Claire laid this down on his screen : >>>>> "Claire" <replyto(a)fra> wrote in message >>>>> news:eD4hhm4BLHA.5584(a)TK2MSFTNGP06.phx.gbl... >>>>>> Hello, >>>>>> When using Excel automation: >>>>>> .Open command requires the full path to the file (workbook) >>>>>> I need to open just blank, unnamed spreadsheet. >>>>>> How to do that? >>>>>> Thanks, Claire >>>>> I have found that doing: >>>>> .Visible = True >>>>> and then using .Add it will show opened Excel book containing 3 blank >>>>> sheets. >>>>> Why there are 3 sheets? >>>>> I need to start a book with one blank sheet only. >>>>> Thanks, Claire >>>> >>>> The number of worksheets when adding a new workbook will always be the >>>> value of Application.SheetsInNewWorkbook property. To only have 1 sheet >>>> in a new workbook: >>> >>> Or remove them (using code) >>> >>> I've always hated that default setting, but i suppose no one will notice >>> the feature if there is just one. >>> A slightly more intelligent design (like IE, chrome, etc) with a "new" tab >>> would have done >> >> Hmm! I suppose that might be a realistic idea. I see Excel12 has a new >> approach attached to its "New" menu. Not decided if any approach by M$ is >> better than configuring it how I want, though. >> >> As for automating, it's pretty simple to store the existing property, set >> as desired, then restore original afterwards. It's a bit easier to handle >> than deleting however many sheets greater than a count of 1. Less coding >> required to set as desired!<IMO> > > not a whole lot of extra coding... > > oxl.displayalerts=false > do while owb.worksheets.count>1 > owb.worksheets(1).delete > loop > oxl.displayalerts=true True enough, Bob. I guess I'm too used to totally controlling the Excel UI as most of my apps are designed specifically for use on that platform. Given that the coding isn't really all that much more than directly setting a property, the loop takes a bit longer and that is a variable I don't have to deal with where users have increased the number of sheets in a new workbook. Unfortunately, the Workbooks.Add method doesn't provide an arg for specifying the sheet count as does the Worksheets.Add. <FWIW>I suppose it's a matter of preference, and so I find it easier to 'set' my preferences before taking action than fixing undesired defaults afterwards.<g> -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |