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.

re: Excel automation???

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:[color=blue]
> 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[/color]

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
[color=blue]
> 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[/color]
This looks a bit strange to me - why not use
exlAppl.Visible = True
[color=blue]
> exlWsheet.Select()[/color]
I think this should work but have you tried .Activate too.
[color=blue]
>
> 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.
>
>[/color]

re: Excel automation???

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:[color=blue]
> 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:
>[color=green]
>> 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[/color]
>
>
> 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
>[color=green]
>> 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[/color]
>
> This looks a bit strange to me - why not use
> exlAppl.Visible = True
>[color=green]
>> exlWsheet.Select()[/color]
>
> I think this should work but have you tried .Activate too.
>[color=green]
>>
>> 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.
>>[/color][/color]

re: Excel automation???

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:OfAPWhbEGHA.336@TK2MSFTNGP14.phx.gbl...[color=blue]
> 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:[color=green]
>> 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:
>>[color=darkred]
>>> 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[/color]
>>
>>
>> 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
>>[color=darkred]
>>> 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[/color]
>>
>> This looks a bit strange to me - why not use
>> exlAppl.Visible = True
>>[color=darkred]
>>> exlWsheet.Select()[/color]
>>
>> I think this should work but have you tried .Activate too.
>>[color=darkred]
>>>
>>> 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.
>>>[/color][/color][/color]

re: Excel automation???

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"" <mbuna@telenet.be> wrote in message
news:6S2vf.88949$np2.5874755@phobos.telenet-ops.be...[color=blue]
> 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:OfAPWhbEGHA.336@TK2MSFTNGP14.phx.gbl...[color=green]
>> 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:[color=darkred]
>>> 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.
>>>>[/color][/color]
>
>[/color]

re: Excel automation???

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:[color=blue]
> 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:OfAPWhbEGHA.336@TK2MSFTNGP14.phx.gbl...
>[color=green]
>>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:
>>[color=darkred]
>>>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.
>>>>[/color][/color]
>
>
>[/color]

re: Excel automation???

See comments between lines:

"Gman" <nah> schreef in bericht
news:OgvnvtgEGHA.2040@TK2MSFTNGP14.phx.gbl...[color=blue]
> 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).[/color]

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.
[color=blue]
>
> (b) Are you sure that when running on earlier XL versions that this line
> is even reached?[/color]

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.
[color=blue]
>
> 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:[color=green]
>> 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:OfAPWhbEGHA.336@TK2MSFTNGP14.phx.gbl...
>>[color=darkred]
>>>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.
>>>>>[/color]
>>
>>[/color][/color]

re: Excel automation???

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:[color=blue]
> See comments between lines:
>
> "Gman" <nah> schreef in bericht
> news:OgvnvtgEGHA.2040@TK2MSFTNGP14.phx.gbl...
>[color=green]
>>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).[/color]
>
>
> 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.
>
>[color=green]
>>(b) Are you sure that when running on earlier XL versions that this line
>>is even reached?[/color]
>
>
> 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.
>
>[color=green]
>>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:
>>[color=darkred]
>>>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:OfAPWhbEGHA.336@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.
>>>>>>
>>>
>>>[/color][/color]
>[/color]