COnverting CSV to XLS file

microsoft.public.dotnet.languages.vb

20 Mar 2006 7:48 PM

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.Application

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

myExcelFile = New Excel.Application
xlConvert(oldFileName, newFilename)

The function called (xConvert) has the following:

******* begin source
        Try

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

            ' Open the CSV file
            'myExcelFile = New Excel.Application
            myExcelFile.Visible = False

            myExcelFile.Workbooks.Open(MySrcPath + "\" + oldFileName)

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

            'Save the file as XLS file
            myExcelFile.ActiveWorkbook.SaveAs(Filename:=MySrcPath +
"\" + newFileName, FileFormat:=Excel.XlFileFormat.xlExcel9795,
CreateBackup:=False)

            'Close the workbook
            myExcelFile.ActiveWorkbook.Close(SaveChanges:=False)

            'Turn the messages back on
            myExcelFile.DisplayAlerts = True

            'Quit from Excel
            myExcelFile.Quit()

            '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é

20 Mar 2006 8:14 PM

Armin Zingler

Show quote Hide quote

"Testguy" <test***@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

20 Mar 2006 8:25 PM

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.nospam@freenet.de> wrote:

Show quoteHide quote

>"Testguy" <test***@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

20 Mar 2006 8:48 PM

Brian Gideon

André,

Blows chunks? That sounds serious :)

Can you post the exception message and stack trace?

Brian

Testguy wrote:
Show quoteHide quote

> 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.nospam@freenet.de> wrote:
>
> >"Testguy" <test***@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

20 Mar 2006 9:31 PM

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.NullReferenceException: Object reference not set to an instance
of an object.
at System.Runtime.InteropServices.Marshal.ReleaseComObject(Object o)
at SelectFolder.Form1.xlConvert(String oldFileName, String
newFileName) in <program name> : line 219
at SelectFolder.Form1.brnConmverttoXLS_Click(object sender, EventArgs
e) in <program name>: Line 173

Line 219 is the line with
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcelFile)

Line 173 is the line that calls my function.

the variable myExcelFile is declared as follows:
Private myExcelFile As Excel.Application (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.exe!SelectFolder.Form1.xlConvert(String oldFileName =
"1000ES_fieldlengths.csv", String newFileName =
"1000ES_fieldlengths.xls") Line 193    Basic
SelectFolder.exe!SelectFolder.Form1.btnConverttoXLS_Click(Object
sender = {System.Windows.Forms.Button}, System.EventArgs e =
{System.EventArgs}) Line 173 + 0x11 bytes    Basic
[<Non-user Code>]
SelectFolder.exe!SelectFolder.Form1.Main() Line 4 + 0x1d bytes    Basic

Not sure how to get Trace information. Sorry.

André

Show quoteHide quote

On 20 Mar 2006 12:48:33 -0800, "Brian Gideon" <briangid***@yahoo.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.nospam@freenet.de> wrote:
>>
>> >"Testguy" <test***@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

21 Mar 2006 1:31 AM

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.application" variable is still a global.

But I moved the "myExcelFile = New Excel.Application" statement in the
subroutine "Form1_Load", and moved the statement "myExcelFile =
Nothing" in the subroutine "Form1_Closing" .

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 <test***@magma.ca> wrote:

Show quoteHide quote

>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.NullReferenceException: Object reference not set to an instance
>of an object.
>at System.Runtime.InteropServices.Marshal.ReleaseComObject(Object o)
>at SelectFolder.Form1.xlConvert(String oldFileName, String
>newFileName) in <program name> : line 219
>at SelectFolder.Form1.brnConmverttoXLS_Click(object sender, EventArgs
>e) in <program name>: Line 173
>
>Line 219 is the line with
>System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcelFile)
>
>Line 173 is the line that calls my function.
>
>the variable myExcelFile is declared as follows:
>Private myExcelFile As Excel.Application (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.exe!SelectFolder.Form1.xlConvert(String oldFileName =
>"1000ES_fieldlengths.csv", String newFileName =
>"1000ES_fieldlengths.xls") Line 193    Basic
>SelectFolder.exe!SelectFolder.Form1.btnConverttoXLS_Click(Object
>sender = {System.Windows.Forms.Button}, System.EventArgs e =
>{System.EventArgs}) Line 173 + 0x11 bytes    Basic
>[<Non-user Code>]
>SelectFolder.exe!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" <briangid***@yahoo.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.nospam@freenet.de> wrote:
>>>
>>> >"Testguy" <test***@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