By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,456 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Excel automation???

P: n/a
Hi everybody.

In my program I have some data that is calculated after some input from the
user. I have written some code that opens an Excel workbook, with 5
worksheets and the calculated data (no database!)with some titles and info,
is entered in the worksheet in a printable format.

This is some of the code...

Public exlAppl As Excel.Application
Public exlWbook As Excel.Workbook
Public exlWsheet As Excel.Worksheet
Public intExcelSheets As Int16 = 1

exlAppl = CType(CreateObject("Excel.Application"), Excel.Application)
exlAppl.SheetsInNewWorkbook = 5
exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
exlWsheet = CType(exlWbook.Worksheets(intExcelSheets), Excel.Worksheet)

'Voeg data toe aan het excel werkblad
With exlWsheet
..Name = "Metrisch " & intExcelSheets
..Columns.ColumnWidth = 15
..Range("A1").Value = "METRISCHE GARENLENGTE OMGEZET NAAR ALLE NUMMERSOORTEN"
With .Range("A1")
..Font.Name = "Times New Roman"
.....
.....
intExcelSheets += 1
exlWsheet.Application.Visible = True
exlWsheet.Select()

This code works wonderful with Excel 2003 installed on a computer, but it
doesn't with an older version installed.
Reason???
What possibilities do I have to make it work with no matter what version of
Excel installed???

Thanks.

Alain.
Jan 4 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
I don't have an earlier version of Excel handy so I'm guessing here....

A few comments made inline below. I can't really comment beyond that
without knowing how it fails with <XL2003.
Alain "Mbuna" wrote:
Hi everybody.

In my program I have some data that is calculated after some input from the
user. I have written some code that opens an Excel workbook, with 5
worksheets and the calculated data (no database!)with some titles and info,
is entered in the worksheet in a printable format.

This is some of the code...

Public exlAppl As Excel.Application
Public exlWbook As Excel.Workbook
Public exlWsheet As Excel.Worksheet
Public intExcelSheets As Int16 = 1

exlAppl = CType(CreateObject("Excel.Application"), Excel.Application)
exlAppl.SheetsInNewWorkbook = 5
This changes the user's Excel settings for good. You should do
something like:

OriginalSheetsInNewWorkbook = exlAppl.SheetsInNewWorkbook
exlAppl.SheetsInNewWorkbook = 5
exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
exlAppl.SheetsInNewWorkbook = OriginalSheetsInNewWorkbook
exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
exlWsheet = CType(exlWbook.Worksheets(intExcelSheets), Excel.Worksheet)

'Voeg data toe aan het excel werkblad
With exlWsheet
.Name = "Metrisch " & intExcelSheets
.Columns.ColumnWidth = 15
.Range("A1").Value = "METRISCHE GARENLENGTE OMGEZET NAAR ALLE NUMMERSOORTEN"
With .Range("A1")
.Font.Name = "Times New Roman"
....
....
intExcelSheets += 1
exlWsheet.Application.Visible = True This looks a bit strange to me - why not use
exlAppl.Visible = True
exlWsheet.Select() I think this should work but have you tried .Activate too.

This code works wonderful with Excel 2003 installed on a computer, but it
doesn't with an older version installed.
Reason???
What possibilities do I have to make it work with no matter what version of
Excel installed???

Thanks.

Alain.

Jan 5 '06 #2

P: n/a
To clarify my response: Can you post the error message, the line it
trips up on etc. What do you mean by "it doesn't [work] with an older
version installed"?

Gman wrote:
I don't have an earlier version of Excel handy so I'm guessing here....

A few comments made inline below. I can't really comment beyond that
without knowing how it fails with <XL2003.
Alain "Mbuna" wrote:
Hi everybody.

In my program I have some data that is calculated after some input
from the user. I have written some code that opens an Excel workbook,
with 5 worksheets and the calculated data (no database!)with some
titles and info, is entered in the worksheet in a printable format.

This is some of the code...

Public exlAppl As Excel.Application
Public exlWbook As Excel.Workbook
Public exlWsheet As Excel.Worksheet
Public intExcelSheets As Int16 = 1

exlAppl = CType(CreateObject("Excel.Application"), Excel.Application)
exlAppl.SheetsInNewWorkbook = 5

This changes the user's Excel settings for good. You should do
something like:

OriginalSheetsInNewWorkbook = exlAppl.SheetsInNewWorkbook
exlAppl.SheetsInNewWorkbook = 5
exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
exlAppl.SheetsInNewWorkbook = OriginalSheetsInNewWorkbook
exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
exlWsheet = CType(exlWbook.Worksheets(intExcelSheets), Excel.Worksheet)

'Voeg data toe aan het excel werkblad
With exlWsheet
.Name = "Metrisch " & intExcelSheets
.Columns.ColumnWidth = 15
.Range("A1").Value = "METRISCHE GARENLENGTE OMGEZET NAAR ALLE
NUMMERSOORTEN"
With .Range("A1")
.Font.Name = "Times New Roman"
....
....
intExcelSheets += 1
exlWsheet.Application.Visible = True


This looks a bit strange to me - why not use
exlAppl.Visible = True
exlWsheet.Select()


I think this should work but have you tried .Activate too.

This code works wonderful with Excel 2003 installed on a computer, but
it doesn't with an older version installed.
Reason???
What possibilities do I have to make it work with no matter what
version of Excel installed???

Thanks.

Alain.

Jan 5 '06 #3

P: n/a
Hi.

What I do (try) is open a Excel workbook with 5 worksheets and each time the
user of the application chooses to copy his results to Excel, then one of
the standard names of a worksheet is overwritten with the new name
"Metrisch" and number 1 to 5, and the data is entered in that worksheet.
Once 5 worksheets are used the user is forced to either save the excel
workbook or not and close excel.

There I use the COMexception to generate a messagebox (dutch text) which
tells the user to close or save excel. With a version of Excel older then
2003, the exception (so the messagebox) is thrown directly and Excel is not
shown. But, something does happen cause when I shut down the computer it
asks me if I want to save the Excel workbook (that I never saw).

See code below for use of exception:
Catch ex As Runtime.InteropServices.COMException
MessageBox.Show("Maximum aantal werkbladen voor deze applicatie is geopend!
" _
& "Bewaar uw gegevens en sluit Excel. " & vbCrLf _
& "Start Excel opnieuw via het menu ""kopieer bestand""!", "Fout! Maximum
bereikt!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

"Gman" <nah> schreef in bericht news:Of*************@TK2MSFTNGP14.phx.gbl...
To clarify my response: Can you post the error message, the line it trips
up on etc. What do you mean by "it doesn't [work] with an older version
installed"?

Gman wrote:
I don't have an earlier version of Excel handy so I'm guessing here....

A few comments made inline below. I can't really comment beyond that
without knowing how it fails with <XL2003.
Alain "Mbuna" wrote:
Hi everybody.

In my program I have some data that is calculated after some input from
the user. I have written some code that opens an Excel workbook, with 5
worksheets and the calculated data (no database!)with some titles and
info, is entered in the worksheet in a printable format.

This is some of the code...

Public exlAppl As Excel.Application
Public exlWbook As Excel.Workbook
Public exlWsheet As Excel.Worksheet
Public intExcelSheets As Int16 = 1

exlAppl = CType(CreateObject("Excel.Application"), Excel.Application)
exlAppl.SheetsInNewWorkbook = 5

This changes the user's Excel settings for good. You should do
something like:

OriginalSheetsInNewWorkbook = exlAppl.SheetsInNewWorkbook
exlAppl.SheetsInNewWorkbook = 5
exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
exlAppl.SheetsInNewWorkbook = OriginalSheetsInNewWorkbook
exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
exlWsheet = CType(exlWbook.Worksheets(intExcelSheets), Excel.Worksheet)

'Voeg data toe aan het excel werkblad
With exlWsheet
.Name = "Metrisch " & intExcelSheets
.Columns.ColumnWidth = 15
.Range("A1").Value = "METRISCHE GARENLENGTE OMGEZET NAAR ALLE
NUMMERSOORTEN"
With .Range("A1")
.Font.Name = "Times New Roman"
....
....
intExcelSheets += 1
exlWsheet.Application.Visible = True


This looks a bit strange to me - why not use
exlAppl.Visible = True
exlWsheet.Select()


I think this should work but have you tried .Activate too.

This code works wonderful with Excel 2003 installed on a computer, but
it doesn't with an older version installed.
Reason???
What possibilities do I have to make it work with no matter what version
of Excel installed???

Thanks.

Alain.

Jan 5 '06 #4

P: n/a
I suspect your problem is linked into "COMexception" only because your code
works for me but did not try the "COMexception". I am using Excel 2002.

"Alain "Mbuna"" <mb***@telenet.be> wrote in message
news:6S*********************@phobos.telenet-ops.be...
Hi.

What I do (try) is open a Excel workbook with 5 worksheets and each time
the user of the application chooses to copy his results to Excel, then one
of the standard names of a worksheet is overwritten with the new name
"Metrisch" and number 1 to 5, and the data is entered in that worksheet.
Once 5 worksheets are used the user is forced to either save the excel
workbook or not and close excel.

There I use the COMexception to generate a messagebox (dutch text) which
tells the user to close or save excel. With a version of Excel older then
2003, the exception (so the messagebox) is thrown directly and Excel is
not shown. But, something does happen cause when I shut down the computer
it asks me if I want to save the Excel workbook (that I never saw).

See code below for use of exception:
Catch ex As Runtime.InteropServices.COMException
MessageBox.Show("Maximum aantal werkbladen voor deze applicatie is
geopend! " _
& "Bewaar uw gegevens en sluit Excel. " & vbCrLf _
& "Start Excel opnieuw via het menu ""kopieer bestand""!", "Fout! Maximum
bereikt!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

"Gman" <nah> schreef in bericht
news:Of*************@TK2MSFTNGP14.phx.gbl...
To clarify my response: Can you post the error message, the line it trips
up on etc. What do you mean by "it doesn't [work] with an older version
installed"?

Gman wrote:
I don't have an earlier version of Excel handy so I'm guessing here....

A few comments made inline below. I can't really comment beyond that
without knowing how it fails with <XL2003.
Alain "Mbuna" wrote:

Hi everybody.

In my program I have some data that is calculated after some input from
the user. I have written some code that opens an Excel workbook, with 5
worksheets and the calculated data (no database!)with some titles and
info, is entered in the worksheet in a printable format.

This is some of the code...

Public exlAppl As Excel.Application
Public exlWbook As Excel.Workbook
Public exlWsheet As Excel.Worksheet
Public intExcelSheets As Int16 = 1

exlAppl = CType(CreateObject("Excel.Application"), Excel.Application)
exlAppl.SheetsInNewWorkbook = 5
This changes the user's Excel settings for good. You should do
something like:

OriginalSheetsInNewWorkbook = exlAppl.SheetsInNewWorkbook
exlAppl.SheetsInNewWorkbook = 5
exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
exlAppl.SheetsInNewWorkbook = OriginalSheetsInNewWorkbook

exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
exlWsheet = CType(exlWbook.Worksheets(intExcelSheets), Excel.Worksheet)

'Voeg data toe aan het excel werkblad
With exlWsheet
.Name = "Metrisch " & intExcelSheets
.Columns.ColumnWidth = 15
.Range("A1").Value = "METRISCHE GARENLENGTE OMGEZET NAAR ALLE
NUMMERSOORTEN"
With .Range("A1")
.Font.Name = "Times New Roman"
....
....
intExcelSheets += 1
exlWsheet.Application.Visible = True

This looks a bit strange to me - why not use
exlAppl.Visible = True

exlWsheet.Select()

I think this should work but have you tried .Activate too.
This code works wonderful with Excel 2003 installed on a computer, but
it doesn't with an older version installed.
Reason???
What possibilities do I have to make it work with no matter what
version of Excel installed???

Thanks.

Alain.


Jan 5 '06 #5

P: n/a
If you can't see the Excel instance, it appears the line where you make
Excel visible is either not being run at all - or isn't working. Two
follow up questions:

(a) Did you try my suggested replacement line there? (exlAppl.Visible =
True).

(b) Are you sure that when running on earlier XL versions that this line
is even reached?

With respect to needing the user to save the workbook, you could try a
different approach whereby you perform this directly through Excel
rather than catching the exception. (I've never used COMexception so I
have no suggestions with that method.) For example:

When you've finished the workbook and need the user to save try
something like:

'Making your workbook and worksheets here

'Loop until the user either saves the workbook or deliberately
'chooses to abort
Do While not exlWbook.Saved

'force a save, this prompts user to choose a location
exlAppl.Dialogs(5).Show '(xlDialogSaveAs = 5)

'check whether the user really did save the workbook
If Not exlWbook.Saved then

'ask user whether they want to abort
myAnswer = MessageBox.Show( _
"You should save the workbook. Do you wish to try again?", _
"Saving = good", _
MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)

'if they do want to abort then set the "saved"
'property to true
if myanswer = vbNo then exlWbook.Saved = True

End if

Loop

'If the user has aborted then myAnswer = vbno

HTH
Alain "Mbuna" wrote:
Hi.

What I do (try) is open a Excel workbook with 5 worksheets and each time the
user of the application chooses to copy his results to Excel, then one of
the standard names of a worksheet is overwritten with the new name
"Metrisch" and number 1 to 5, and the data is entered in that worksheet.
Once 5 worksheets are used the user is forced to either save the excel
workbook or not and close excel.

There I use the COMexception to generate a messagebox (dutch text) which
tells the user to close or save excel. With a version of Excel older then
2003, the exception (so the messagebox) is thrown directly and Excel is not
shown. But, something does happen cause when I shut down the computer it
asks me if I want to save the Excel workbook (that I never saw).

See code below for use of exception:
Catch ex As Runtime.InteropServices.COMException
MessageBox.Show("Maximum aantal werkbladen voor deze applicatie is geopend!
" _
& "Bewaar uw gegevens en sluit Excel. " & vbCrLf _
& "Start Excel opnieuw via het menu ""kopieer bestand""!", "Fout! Maximum
bereikt!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

"Gman" <nah> schreef in bericht news:Of*************@TK2MSFTNGP14.phx.gbl...
To clarify my response: Can you post the error message, the line it trips
up on etc. What do you mean by "it doesn't [work] with an older version
installed"?

Gman wrote:
I don't have an earlier version of Excel handy so I'm guessing here....

A few comments made inline below. I can't really comment beyond that
without knowing how it fails with <XL2003.
Alain "Mbuna" wrote:
Hi everybody.

In my program I have some data that is calculated after some input from
the user. I have written some code that opens an Excel workbook, with 5
worksheets and the calculated data (no database!)with some titles and
info, is entered in the worksheet in a printable format.

This is some of the code...

Public exlAppl As Excel.Application
Public exlWbook As Excel.Workbook
Public exlWsheet As Excel.Worksheet
Public intExcelSheets As Int16 = 1

exlAppl = CType(CreateObject("Excel.Application"), Excel.Application)
exlAppl.SheetsInNewWorkbook = 5
This changes the user's Excel settings for good. You should do
something like:

OriginalSheetsInNewWorkbook = exlAppl.SheetsInNewWorkbook
exlAppl.SheetsInNewWorkbook = 5
exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
exlAppl.SheetsInNewWorkbook = OriginalSheetsInNewWorkbook
exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
exlWsheet = CType(exlWbook.Worksheets(intExcelSheets), Excel.Worksheet)

'Voeg data toe aan het excel werkblad
With exlWsheet
.Name = "Metrisch " & intExcelSheets
.Columns.ColumnWidth = 15
.Range("A1").Value = "METRISCHE GARENLENGTE OMGEZET NAAR ALLE
NUMMERSOORTEN"
With .Range("A1")
.Font.Name = "Times New Roman"
....
....
intExcelSheets += 1
exlWsheet.Application.Visible = True

This looks a bit strange to me - why not use
exlAppl.Visible = True
exlWsheet.Select()

I think this should work but have you tried .Activate too.
This code works wonderful with Excel 2003 installed on a computer, but
it doesn't with an older version installed.
Reason???
What possibilities do I have to make it work with no matter what version
of Excel installed???

Thanks.

Alain.


Jan 5 '06 #6

P: n/a
See comments between lines:

"Gman" <nah> schreef in bericht
news:Og**************@TK2MSFTNGP14.phx.gbl...
If you can't see the Excel instance, it appears the line where you make
Excel visible is either not being run at all - or isn't working. Two
follow up questions:

(a) Did you try my suggested replacement line there? (exlAppl.Visible =
True).
Not yet! I will make these changes tonight and then try them out tomorrow,
because I need to do this at work. At home no longer a PC running previous
Excel versions.

(b) Are you sure that when running on earlier XL versions that this line
is even reached?
Not really, because I don't have VB.net at work. So, the only thing I can do
is install the published application and run it to check on errors or
problems.

With respect to needing the user to save the workbook, you could try a
different approach whereby you perform this directly through Excel rather
than catching the exception. (I've never used COMexception so I have no
suggestions with that method.) For example:

When you've finished the workbook and need the user to save try something
like:

'Making your workbook and worksheets here

'Loop until the user either saves the workbook or deliberately
'chooses to abort
Do While not exlWbook.Saved

'force a save, this prompts user to choose a location
exlAppl.Dialogs(5).Show '(xlDialogSaveAs = 5)

'check whether the user really did save the workbook
If Not exlWbook.Saved then

'ask user whether they want to abort
myAnswer = MessageBox.Show( _
"You should save the workbook. Do you wish to try again?", _
"Saving = good", _
MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)

'if they do want to abort then set the "saved"
'property to true
if myanswer = vbNo then exlWbook.Saved = True

End if

Loop

'If the user has aborted then myAnswer = vbno

HTH
Alain "Mbuna" wrote:
Hi.

What I do (try) is open a Excel workbook with 5 worksheets and each time
the user of the application chooses to copy his results to Excel, then
one of the standard names of a worksheet is overwritten with the new name
"Metrisch" and number 1 to 5, and the data is entered in that worksheet.
Once 5 worksheets are used the user is forced to either save the excel
workbook or not and close excel.

There I use the COMexception to generate a messagebox (dutch text) which
tells the user to close or save excel. With a version of Excel older then
2003, the exception (so the messagebox) is thrown directly and Excel is
not shown. But, something does happen cause when I shut down the computer
it asks me if I want to save the Excel workbook (that I never saw).

See code below for use of exception:
Catch ex As Runtime.InteropServices.COMException
MessageBox.Show("Maximum aantal werkbladen voor deze applicatie is
geopend! " _
& "Bewaar uw gegevens en sluit Excel. " & vbCrLf _
& "Start Excel opnieuw via het menu ""kopieer bestand""!", "Fout! Maximum
bereikt!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

"Gman" <nah> schreef in bericht
news:Of*************@TK2MSFTNGP14.phx.gbl...
To clarify my response: Can you post the error message, the line it trips
up on etc. What do you mean by "it doesn't [work] with an older version
installed"?

Gman wrote:

I don't have an earlier version of Excel handy so I'm guessing here....

A few comments made inline below. I can't really comment beyond that
without knowing how it fails with <XL2003.
Alain "Mbuna" wrote:
>Hi everybody.
>
>In my program I have some data that is calculated after some input from
>the user. I have written some code that opens an Excel workbook, with 5
>worksheets and the calculated data (no database!)with some titles and
>info, is entered in the worksheet in a printable format.
>
>This is some of the code...
>
>Public exlAppl As Excel.Application
>Public exlWbook As Excel.Workbook
>Public exlWsheet As Excel.Worksheet
>Public intExcelSheets As Int16 = 1
>
>exlAppl = CType(CreateObject("Excel.Application"), Excel.Application)
>exlAppl.SheetsInNewWorkbook = 5
This changes the user's Excel settings for good. You should do
something like:

OriginalSheetsInNewWorkbook = exlAppl.SheetsInNewWorkbook
exlAppl.SheetsInNewWorkbook = 5
exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
exlAppl.SheetsInNewWorkbook = OriginalSheetsInNewWorkbook
>exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
>exlWsheet = CType(exlWbook.Worksheets(intExcelSheets), Excel.Worksheet)
>
>'Voeg data toe aan het excel werkblad
>With exlWsheet
>.Name = "Metrisch " & intExcelSheets
>.Columns.ColumnWidth = 15
>.Range("A1").Value = "METRISCHE GARENLENGTE OMGEZET NAAR ALLE
>NUMMERSOORTEN"
>With .Range("A1")
>.Font.Name = "Times New Roman"
>....
>....
>intExcelSheets += 1
>exlWsheet.Application.Visible = True

This looks a bit strange to me - why not use
exlAppl.Visible = True
>exlWsheet.Select()

I think this should work but have you tried .Activate too.
>This code works wonderful with Excel 2003 installed on a computer, but
>it doesn't with an older version installed.
>Reason???
>What possibilities do I have to make it work with no matter what
>version of Excel installed???
>
>Thanks.
>
>Alain.
>



Jan 5 '06 #7

P: n/a
That's the problem with checking for multiple versions :-(

When I need to check something on a machine w/o the development
environment I create a new version that creates a log file and appends a
line for each area of concern. Something like:

Date/Time - Creating Excel Instance...
Date/Time - Excel Instance created
Date/Time - Creating workbook...
etc.

That way you can see exactly where it's getting to and where it's
tripped up.

Alain "Mbuna" wrote:
See comments between lines:

"Gman" <nah> schreef in bericht
news:Og**************@TK2MSFTNGP14.phx.gbl...
If you can't see the Excel instance, it appears the line where you make
Excel visible is either not being run at all - or isn't working. Two
follow up questions:

(a) Did you try my suggested replacement line there? (exlAppl.Visible =
True).

Not yet! I will make these changes tonight and then try them out tomorrow,
because I need to do this at work. At home no longer a PC running previous
Excel versions.

(b) Are you sure that when running on earlier XL versions that this line
is even reached?

Not really, because I don't have VB.net at work. So, the only thing I can do
is install the published application and run it to check on errors or
problems.

With respect to needing the user to save the workbook, you could try a
different approach whereby you perform this directly through Excel rather
than catching the exception. (I've never used COMexception so I have no
suggestions with that method.) For example:

When you've finished the workbook and need the user to save try something
like:

'Making your workbook and worksheets here

'Loop until the user either saves the workbook or deliberately
'chooses to abort
Do While not exlWbook.Saved

'force a save, this prompts user to choose a location
exlAppl.Dialogs(5).Show '(xlDialogSaveAs = 5)

'check whether the user really did save the workbook
If Not exlWbook.Saved then

'ask user whether they want to abort
myAnswer = MessageBox.Show( _
"You should save the workbook. Do you wish to try again?", _
"Saving = good", _
MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)

'if they do want to abort then set the "saved"
'property to true
if myanswer = vbNo then exlWbook.Saved = True

End if

Loop

'If the user has aborted then myAnswer = vbno

HTH
Alain "Mbuna" wrote:
Hi.

What I do (try) is open a Excel workbook with 5 worksheets and each time
the user of the application chooses to copy his results to Excel, then
one of the standard names of a worksheet is overwritten with the new name
"Metrisch" and number 1 to 5, and the data is entered in that worksheet.
Once 5 worksheets are used the user is forced to either save the excel
workbook or not and close excel.

There I use the COMexception to generate a messagebox (dutch text) which
tells the user to close or save excel. With a version of Excel older then
2003, the exception (so the messagebox) is thrown directly and Excel is
not shown. But, something does happen cause when I shut down the computer
it asks me if I want to save the Excel workbook (that I never saw).

See code below for use of exception:
Catch ex As Runtime.InteropServices.COMException
MessageBox.Show("Maximum aantal werkbladen voor deze applicatie is
geopend! " _
& "Bewaar uw gegevens en sluit Excel. " & vbCrLf _
& "Start Excel opnieuw via het menu ""kopieer bestand""!", "Fout! Maximum
bereikt!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

"Gman" <nah> schreef in bericht
news:Of*************@TK2MSFTNGP14.phx.gbl...
To clarify my response: Can you post the error message, the line it trips
up on etc. What do you mean by "it doesn't [work] with an older version
installed"?

Gman wrote:
>I don't have an earlier version of Excel handy so I'm guessing here....
>
>A few comments made inline below. I can't really comment beyond that
>without knowing how it fails with <XL2003.
>
>
>Alain "Mbuna" wrote:
>
>
>
>>Hi everybody.
>>
>>In my program I have some data that is calculated after some input from
>>the user. I have written some code that opens an Excel workbook, with 5
>>worksheets and the calculated data (no database!)with some titles and
>>info, is entered in the worksheet in a printable format.
>>
>>This is some of the code...
>>
>>Public exlAppl As Excel.Application
>>Public exlWbook As Excel.Workbook
>>Public exlWsheet As Excel.Worksheet
>>Public intExcelSheets As Int16 = 1
>>
>>exlAppl = CType(CreateObject("Excel.Application"), Excel.Application)
>>exlAppl.SheetsInNewWorkbook = 5
>
>
> This changes the user's Excel settings for good. You should do
>something like:
>
> OriginalSheetsInNewWorkbook = exlAppl.SheetsInNewWorkbook
> exlAppl.SheetsInNewWorkbook = 5
> exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
> exlAppl.SheetsInNewWorkbook = OriginalSheetsInNewWorkbook
>
>
>
>>exlWbook = CType(exlAppl.Workbooks.Add, Excel.Workbook)
>>exlWsheet = CType(exlWbook.Worksheets(intExcelSheets), Excel.Worksheet)
>>
>>'Voeg data toe aan het excel werkblad
>>With exlWsheet
>>.Name = "Metrisch " & intExcelSheets
>>.Columns.ColumnWidth = 15
>>.Range("A1").Value = "METRISCHE GARENLENGTE OMGEZET NAAR ALLE
>>NUMMERSOORTEN"
>>With .Range("A1")
>>.Font.Name = "Times New Roman"
>>....
>>....
>>intExcelSheets += 1
>>exlWsheet.Application.Visible = True
>
> This looks a bit strange to me - why not use
> exlAppl.Visible = True
>
>
>
>>exlWsheet.Select()
>
> I think this should work but have you tried .Activate too.
>
>
>
>>This code works wonderful with Excel 2003 installed on a computer, but
>>it doesn't with an older version installed.
>>Reason???
>>What possibilities do I have to make it work with no matter what
>>version of Excel installed???
>>
>>Thanks.
>>
>>Alain.
>>

Jan 5 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.