Excel automation???

[Alain \Mbuna\]

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.

 

[Gman]

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.

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.

 

[Gman]

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"?

 

[Alain \Mbuna\]

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

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"?

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.




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


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


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

 

[kevininstructor]

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.

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

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"?

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.

 

[Gman]

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

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

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"?

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.

 

[Alain \Mbuna\]

See comments between lines:

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

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

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.

 

[Gman]

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.

See comments between lines:

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

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


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.