Excel automation: how to open a blank spreadsheet
in [Visual Basic]
|
Prev: VB Macro to VB Application
Next: invisible tag for RTF?
From: Claire on 9 Jun 2010 00:09 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
From: Claire on 9 Jun 2010 00:36 "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
From: GS on 9 Jun 2010 02:28 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 Hi 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: With appXL .SheetsInNewWorkbook = 1 .Workbooks.Add 'do some more stuff, maybe .visible = True .UserControl = True End With where appXL would be replaced by your object ref to the Excel instance you are automating. Note that the instance does not have to be visible to do this, and is probably a good idea to not make it visible until it's ready to turn over to your user (if that's your intent). HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc
From: GS on 9 Jun 2010 03:34 Sorry, but I failed to mention that it is good practice to always restore any persistent settings you change when automating any app. That said, you should store the existing SheetsInNewWorkbook setting in a variable and reset it after you add your workbook. Revised code snippet: Dim lCount As Long With appXL lCount = .SheetsInNewWorkbook .SheetsInNewWorkbook = 1 .Workbooks.Add .SheetsInNewWorkbook = lCount 'do some more stuff, maybe .visible = True .UserControl = True End With -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc
From: Claire on 9 Jun 2010 09:46
Thank you, Gary Claire "GS" <gesansom(a)netscape.net> wrote in message news:hung5c$3na$1(a)news.eternal-september.org... > Sorry, but I failed to mention that it is good practice to always restore > any persistent settings you change when automating any app. That said, you > should store the existing SheetsInNewWorkbook setting in a variable and > reset it after you add your workbook. > > Revised code snippet: > > Dim lCount As Long > With appXL > lCount = .SheetsInNewWorkbook > .SheetsInNewWorkbook = 1 > .Workbooks.Add > .SheetsInNewWorkbook = lCount > 'do some more stuff, maybe > .visible = True > .UserControl = True > End With > > -- > Garry > > Free usenet access at http://www.eternal-september.org > ClassicVB Users Regroup! comp.lang.basic.visual.misc > > |