VB6 with Excel Objects: Please review my code

[Kurt Remlin]

Hi,

I wrote that simple program to get an idea how to work with Excel
objects from VB.

It should (and actually does) create a new Excel workbook with three
worksheets named "1", "2" and "3" (in this order from left to right)
and save it.

Please look at the code below and let me know if this is a good way to
do it. Is there a way (and need) to streamline the code?

TIA

'============================================
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Dim i As Integer
Dim strFileName As String
Dim bAlerts As Boolean

On Error GoTo Quit

Set xlApp = New Excel.Application
xlApp.Visible = False
xlApp.SheetsInNewWorkbook = 1
Set xlWB = xlApp.Workbooks.Add
For i = 1 To 3
Set xlWS = xlWB.Worksheets.Add(After:=xlWB.Worksheets(i))
xlWS.Name = Str$(i)
Next i
bAlerts = xlApp.DisplayAlerts
xlApp.DisplayAlerts = False
xlWB.Worksheets("Sheet1").Delete
xlWB.Worksheets(1).Activate

' strFileName = xlApp.GetSaveAsFilename
strFileName = "C:\Temp\Temp.xls"

xlWB.Close SaveChanges:=True, FileName:=strFileName
xlApp.DisplayAlerts = bAlerts
Quit:
xlApp.Quit

Set xlWS = Nothing
Set xlWB = Nothing
Set xlApp = Nothing
'===================================================

 

[Bob Phillips]

When you add a workbook, it will always be created with a number of
worksheets based upon an application setting, so adding three sheets is
adding an extra three sheets, so that is probably unnecessary.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jim Rech]

I'm game. See below.
--
Jim Rech
Excel MVP

The default is not visible I believe

xlApp.Visible = False

Doing this changes the user's default, so if you want to go this route, get
and restore the user's setting.

xlApp.SheetsInNewWorkbook = 1

Another way is: xlApp.Workbooks.Add -4167, which adds a one sheet workbook
and you do not have to touch SheetsInNewWorkbook. Then your code would add
2 sheets instead of 3:
xlWB.Worksheets.Add , , 2
and rename them in the loop.

Set xlWB = xlApp.Workbooks.Add
For i = 1 To 3
Set xlWS = xlWB.Worksheets.Add(After:=xlWB.Worksheets(i))
xlWS.Name = Str$(i)
Next i

There is no reason to capture/restore this since it does not become the
user's setting and in fact is always turned off automatically when code
execution ends.

 

[Todd Beaulieu]

How the heck did you figure that one out? Where's that magic number come
from?

I chose a different route. Probably less elegant, but it doesn't touch
settings. After starting a new workbook, I simply delete all worksheets
after the first one.

 

[Tom Ogilvy]

From help on the workbooks.Add method:

If this argument is a constant, the new workbook contains a single sheet of
the specified type. Can be one of the following XlWBATemplate constants:
xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or
xlWBATWorksheet.


then from the immediate window:

? xlWBATWorksheet
-4167


Use the value of the constant since if you use late binding, the constant
won't be defined.

 

[Todd Beaulieu]

Excellent. Thanks!

I used to work so much with Excel/VBA and I know it's ultra-capable. I'd
like to get more into it again.

Do you use late binding to allow for the different versions? I hate having
to do that.

 

[Tom Ogilvy]

That would be one of the primary reasons.