472,805 Members | 3,242 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

Excel automation???

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
7 2358
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: Ange T | last post by:
Hi there, I'm having pain with the VB behind an Access form. The form is used to create reports in Excel based on the details entered in the form. This has always worked without error on my...
2
by: jeffgeorge | last post by:
I'm currently exporting a form to Excel. Because there are controls and totals in the header, I first have a button for users to convert to a datasheet. Then I use the automated quick office...
7
by: taylor.bryant | last post by:
I am running: Win XP SP2 Excel 2002, Access 2002 (Office XP SP3) Using Visual Basic (not VB.NET) At one point (prior to XP SP2?!? - I can't pin it down), this did not happen and I was easily...
1
by: cybertof | last post by:
Hello, Is there a way to connect (through automation) a c# application to a running Excel 2003 instance on a specific workbook ? In the past, i used to use GetObject(...) function in VB6. ...
17
by: Mansi | last post by:
I need to do some research on how to use excel automation from c#. Does anyone know of any good books related to this subject? Thanks. Mansi
12
by: D. Shane Fowlkes | last post by:
This most likely belongs in another forum but I thought I'd start here. I have a COM Object written in VB6. The DLL will access MS Excel and use it's Object Library to write a customized report...
3
by: Carlos Magalhaes | last post by:
Hey All, I am doing some excel automation using the excel COM. I can do most of the functions and its working well until I come across a formula. I can run a formula and insert the formula...
3
by: Mitchell Vincent | last post by:
Does anyone have some good examples of Excel automation with (VB).NET? I have some Excel spreadsheets that a customer needs parsed out but I've never tried to use Excel programatically before! ...
6
by: a.theil | last post by:
Please help! I need a simple excel automation, just 2 write some files into excel. I do: Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oRng As...
1
by: Troy | last post by:
I have VB.Net code that opens Excel files and imports data from them. It works fine for all versions up to 2003 and has for over a year. We have users updating to Office 2003 and the Excel...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.