VB6 with Excel Objects: Please review my code
 

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
'================================================= ==

VB6 with Excel Objects: Please review my code
 

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)

"Kurt Remlin" wrote in message
om...
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
'================================================= ==

VB6 with Excel Objects: Please review my code
 

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.
bAlerts = xlApp.DisplayAlerts
xlApp.DisplayAlerts = False
xlWB.Worksheets("Sheet1").Delete
xlWB.Worksheets(1).Activate

VB6 with Excel Objects: Please review my code
 

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.

"Jim Rech" wrote in message
...
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.
bAlerts = xlApp.DisplayAlerts
xlApp.DisplayAlerts = False
xlWB.Worksheets("Sheet1").Delete
xlWB.Worksheets(1).Activate

VB6 with Excel Objects: Please review my code
 

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.

--
Regards,
Tom Ogilvy



"Todd Beaulieu" wrote in message
...
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.

"Jim Rech" wrote in message
...
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.
bAlerts = xlApp.DisplayAlerts
xlApp.DisplayAlerts = False
xlWB.Worksheets("Sheet1").Delete
xlWB.Worksheets(1).Activate

VB6 with Excel Objects: Please review my code
 

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" wrote in message
...
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.

--
Regards,
Tom Ogilvy



"Todd Beaulieu" wrote in message
...
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.

"Jim Rech" wrote in message
...
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.
bAlerts = xlApp.DisplayAlerts
xlApp.DisplayAlerts = False
xlWB.Worksheets("Sheet1").Delete
xlWB.Worksheets(1).Activate

VB6 with Excel Objects: Please review my code
 

That would be one of the primary reasons.

--
Regards,
Tom Ogilvy


"Todd Beaulieu" wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy



"Todd Beaulieu" wrote in message
...
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.

"Jim Rech" wrote in message
...
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.
bAlerts = xlApp.DisplayAlerts
xlApp.DisplayAlerts = False
xlWB.Worksheets("Sheet1").Delete
xlWB.Worksheets(1).Activate