COnverting CSV to XLS file

[Testguy]

Hi,

I was wondering if one the smart people that frequent this group could
give me a hand with a small program I am attempting to debug.

I am not a highly experienced developer, but can create small
programs.

I was asked to write a small application that would look in a folder,
and convert all the CSV files to XLS.

I thought about using a macro, but they did not want that. Fine.

I wrote a small program in VB .NET (2003, I do nto have 2005 yet).

The program does the following:

For each select file in a list box
delete the existing XLS file, if there
Create a new Excel.Applicati on

The source I used is as follows:
Private myExcelFile As Excel.Applicati on (at the top of the file)

myExcelFile = New Excel.Applicati on
xlConvert(oldFi leName, newFilename)
The function called (xConvert) has the following:

******* begin source
Try

tFileNameCSV = MySrcPath + "\" + oldFileName
tFileNameXLS = MySrcPath + "\" + newFileName

' Open the CSV file
'myExcelFile = New Excel.Applicati on
myExcelFile.Vis ible = False

myExcelFile.Wor kbooks.Open(MyS rcPath + "\" + oldFileName)

'Turn off message box so that we do not get any messages
myExcelFile.Dis playAlerts = False

'Save the file as XLS file
myExcelFile.Act iveWorkbook.Sav eAs(Filename:=M ySrcPath +
"\" + newFileName, FileFormat:=Exc el.XlFileFormat .xlExcel9795,
CreateBackup:=F alse)

'Close the workbook
myExcelFile.Act iveWorkbook.Clo se(SaveChanges: =False)

'Turn the messages back on
myExcelFile.Dis playAlerts = True

'Quit from Excel
myExcelFile.Qui t()

'Kill the variable
myExcelFile = Nothing

******* end source

The problem is that, as the files get converted, more an more memory
is being used (I am converting about 116 files right now).

Even after I close the executable, there is still a bunch of memory
being used.

Looking in the task manager, I could see at one time about 20 copies
of Excel.exe in memory.

After a while, it settles down to 11 copies in memory.

But I thought that, after quitting Excel and setting the variable to
"nothing", that this would free up my memory.

Any ideas as to what I am doing wrong?

Thank you in advance.

André

[Armin Zingler]

"Testguy" <te*****@magma. ca> schrieb

The problem is that, as the files get converted, more an more memory
is being used (I am converting about 116 files right now).

Even after I close the executable, there is still a bunch of memory
being used.

Looking in the task manager, I could see at one time about 20 copies
of Excel.exe in memory.

After a while, it settles down to 11 copies in memory.

But I thought that, after quitting Excel and setting the variable to
"nothing", that this would free up my memory.

Any ideas as to what I am doing wrong?

Thank you in advance.

You must explicitly release COM objects:

http://support.microsoft.com/kb/317109/en-us


Armin

[Testguy]

Thanks for the reply, Armin.

Alas, when I add the line mentioned in the web page, my program blows
chunks.

After doing the first conversion, it goes intot he CATCH, and stops
working.

André

On Mon, 20 Mar 2006 21:14:13 +0100, "Armin Zingler"
<az*******@free net.de> wrote:

"Testguy" <te*****@magma. ca> schrieb

The problem is that, as the files get converted, more an more memory
is being used (I am converting about 116 files right now).

Even after I close the executable, there is still a bunch of memory
being used.

Looking in the task manager, I could see at one time about 20 copies
of Excel.exe in memory.

After a while, it settles down to 11 copies in memory.

But I thought that, after quitting Excel and setting the variable to
"nothing", that this would free up my memory.

Any ideas as to what I am doing wrong?

Thank you in advance.

You must explicitly release COM objects:

http://support.microsoft.com/kb/317109/en-us


Armin

[Brian Gideon]

André,

Blows chunks? That sounds serious :)

Can you post the exception message and stack trace?

Brian

Testguy wrote:

Thanks for the reply, Armin.

Alas, when I add the line mentioned in the web page, my program blows
chunks.

After doing the first conversion, it goes intot he CATCH, and stops
working.

André

On Mon, 20 Mar 2006 21:14:13 +0100, "Armin Zingler"
<az*******@free net.de> wrote:

"Testguy" <te*****@magma. ca> schrieb

The problem is that, as the files get converted, more an more memory
is being used (I am converting about 116 files right now).

Even after I close the executable, there is still a bunch of memory
being used.

Looking in the task manager, I could see at one time about 20 copies
of Excel.exe in memory.

After a while, it settles down to 11 copies in memory.

But I thought that, after quitting Excel and setting the variable to
"nothing", that this would free up my memory.

Any ideas as to what I am doing wrong?

Thank you in advance.

You must explicitly release COM objects:

http://support.microsoft.com/kb/317109/en-us


Armin

[Testguy]

Hi Brian,

Okay. Blows chunks is a bit of an exageration.:-)

The try ... Catch takes care of that.

The message it generates is as follows (typos are mine):
System.NullRefe renceException: Object reference not set to an instance
of an object.
at System.Runtime. InteropServices .Marshal.Releas eComObject(Obje ct o)
at SelectFolder.Fo rm1.xlConvert(S tring oldFileName, String
newFileName) in <program name> : line 219
at SelectFolder.Fo rm1.brnConmvert toXLS_Click(obj ect sender, EventArgs
e) in <program name>: Line 173

Line 219 is the line with
System.Runtime. InteropServices .Marshal.Releas eComObject(myEx celFile)

Line 173 is the line that calls my function.

the variable myExcelFile is declared as follows:
Private myExcelFile As Excel.Applicati on (a global variable)

The way I understand thing, I am told that the object or whatever that
I am releasing is not an object.

The following lines are what I got from the "Call stack" window:
SelectFolder.ex e!SelectFolder. Form1.xlConvert (String oldFileName =
"1000ES_fieldle ngths.csv", String newFileName =
"1000ES_fieldle ngths.xls") Line 193 Basic
SelectFolder.ex e!SelectFolder. Form1.btnConver ttoXLS_Click(Ob ject
sender = {System.Windows .Forms.Button}, System.EventArg s e =
{System.EventAr gs}) Line 173 + 0x11 bytes Basic
[<Non-user Code>]
SelectFolder.ex e!SelectFolder. Form1.Main() Line 4 + 0x1d bytes Basic
Not sure how to get Trace information. Sorry.

André

On 20 Mar 2006 12:48:33 -0800, "Brian Gideon" <br*********@ya hoo.com>
wrote:

André,

Blows chunks? That sounds serious :)

Can you post the exception message and stack trace?

Brian

Testguy wrote:

Thanks for the reply, Armin.

Alas, when I add the line mentioned in the web page, my program blows
chunks.

After doing the first conversion, it goes intot he CATCH, and stops
working.

André

On Mon, 20 Mar 2006 21:14:13 +0100, "Armin Zingler"
<az*******@free net.de> wrote:

>"Testguy" <te*****@magma. ca> schrieb
>> The problem is that, as the files get converted, more an more memory
>> is being used (I am converting about 116 files right now).
>>
>> Even after I close the executable, there is still a bunch of memory
>> being used.
>>
>> Looking in the task manager, I could see at one time about 20 copies
>> of Excel.exe in memory.
>>
>> After a while, it settles down to 11 copies in memory.
>>
>> But I thought that, after quitting Excel and setting the variable to
>> "nothing", that this would free up my memory.
>>
>> Any ideas as to what I am doing wrong?
>>
>> Thank you in advance.
>
>
>You must explicitly release COM objects:
>
>http://support.microsoft.com/kb/317109/en-us
>
>
>
>
>Armin

[Testguy]

Hi again,

I was playing with the program and, just for the hell, of it decided
to move my statements around.

my declaration for the "excel.applicat ion" variable is still a global.

But I moved the "myExcelFil e = New Excel.Applicati on" statement in the
subroutine "Form1_Load ", and moved the statement "myExcelFil e =
Nothing" in the subroutine "Form1_Clos ing" .

When running the application, Excel is opening once, and the problem
disappears.

Maybe the program was starting and stop the excel process so fast that
it could not deal with the memory issue.

I am confused. It now works properly, but I do not understand why I
previously had all these copy in Excel in memory.

Thanks for all the help.

André

On Mon, 20 Mar 2006 16:31:03 -0500, Testguy <te*****@magma. ca> wrote:

Hi Brian,

Okay. Blows chunks is a bit of an exageration.:-)

The try ... Catch takes care of that.

The message it generates is as follows (typos are mine):
System.NullRef erenceException : Object reference not set to an instance
of an object.
at System.Runtime. InteropServices .Marshal.Releas eComObject(Obje ct o)
at SelectFolder.Fo rm1.xlConvert(S tring oldFileName, String
newFileName) in <program name> : line 219
at SelectFolder.Fo rm1.brnConmvert toXLS_Click(obj ect sender, EventArgs
e) in <program name>: Line 173

Line 219 is the line with
System.Runtime .InteropService s.Marshal.Relea seComObject(myE xcelFile)

Line 173 is the line that calls my function.

the variable myExcelFile is declared as follows:
Private myExcelFile As Excel.Applicati on (a global variable)

The way I understand thing, I am told that the object or whatever that
I am releasing is not an object.

The following lines are what I got from the "Call stack" window:
SelectFolder.e xe!SelectFolder .Form1.xlConver t(String oldFileName =
"1000ES_fieldl engths.csv", String newFileName =
"1000ES_fieldl engths.xls") Line 193 Basic
SelectFolder.e xe!SelectFolder .Form1.btnConve rttoXLS_Click(O bject
sender = {System.Windows .Forms.Button}, System.EventArg s e =
{System.EventA rgs}) Line 173 + 0x11 bytes Basic
[<Non-user Code>]
SelectFolder.e xe!SelectFolder .Form1.Main() Line 4 + 0x1d bytes Basic
Not sure how to get Trace information. Sorry.

André

On 20 Mar 2006 12:48:33 -0800, "Brian Gideon" <br*********@ya hoo.com>
wrote:

André,

Blows chunks? That sounds serious :)

Can you post the exception message and stack trace?

Brian

Testguy wrote:

Thanks for the reply, Armin.

Alas, when I add the line mentioned in the web page, my program blows
chunks.

After doing the first conversion, it goes intot he CATCH, and stops
working.

André

On Mon, 20 Mar 2006 21:14:13 +0100, "Armin Zingler"
<az*******@free net.de> wrote:

>"Testguy" <te*****@magma. ca> schrieb
>> The problem is that, as the files get converted, more an more memory
>> is being used (I am converting about 116 files right now).
>>
>> Even after I close the executable, there is still a bunch of memory
>> being used.
>>
>> Looking in the task manager, I could see at one time about 20 copies
>> of Excel.exe in memory.
>>
>> After a while, it settles down to 11 copies in memory.
>>
>> But I thought that, after quitting Excel and setting the variable to
>> "nothing", that this would free up my memory.
>>
>> Any ideas as to what I am doing wrong?
>>
>> Thank you in advance.
>
>
>You must explicitly release COM objects:
>
>http://support.microsoft.com/kb/317109/en-us
>
>
>
>
>Armin