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

 

[Armin Zingler]

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é

 

[Brian Gideon]

André,

Blows chunks? That sounds serious

Can you post the exception message and stack trace?

Brian

 

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

 

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

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é

André,

Blows chunks? That sounds serious

Can you post the exception message and stack trace?

Brian