Solved

open excel files with visual basic NET

Posted on 2011-02-17
9
556 Views
Last Modified: 2012-05-11
First of all, thanks for your help

This is an amazing site!!!

Ok this is my problem

I have this code (THANKS TO YOU GUYS!!)

With that code I change the content of the excel file "plantilla1.xls" and I use the classic "Save As" file dialog window to save it where ever I want.
 
Dim oSheet As Object
        Dim xlTemp As Application
        xlTemp = New Application
        xlTemp.Workbooks.Open(System.Windows.Forms.Application.StartupPath & "\plantillas\plantilla1.xls")
        oSheet = xlTemp.Worksheets(1)
        oSheet.Range("A1").Value = "Last Name"
        oSheet.Range("B1").Value = "First Name"
        oSheet.Range("A1:B1").Font.Bold = True
        oSheet.Range("A2").Value = "Doe"
        oSheet.Range("B2").Value = "John"
        Dim sv As New SaveFileDialog
        sv.AddExtension = True
        sv.CheckPathExists = True
        sv.DefaultExt = ".xls"
        sv.Filter = "Excel Files|*.xls"
        sv.OverwritePrompt = True
        sv.Title = "Save Excel File As..."
        If sv.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            xlTemp.SaveWorkspace(sv.FileName)
        End If
        sv.Dispose()
        oSheet = Nothing
        xlTemp.Quit()
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each p As Process In pro
            p.Kill()
        Next

Open in new window


But After the new file is save it I have problems with the file when I try to open It

I made this example: I save a new file with the name test.xls, when I try to open the file the excel show me the following error:
 image1.jpg

After I push the "ok" button the file is open, but as you can see in the following image It is open with the name of the original file "plantilla1" not with the new name "test"
 image2.jpg

Do you know what is happend?? why I cannot open the new file normaly?????

Best regards
0
Comment
Question by:altariamx2003
  • 4
  • 3
  • 2
9 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 34922963
The first thing that I can see if that you shouldn't kill the Excel process, you should let it close naturally.  COM uses reference counting, and .NET doesn't.  The COM library will not unload until all the reference counts are decremented to zero.   There are implicit and explicit references, and you need to release them all.  The System.Runtime.InteropServices.Marshal class has the ReleaseComObject, which takes care of that for you.

        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each p As Process In pro
            p.Kill()
        Next 

Open in new window


The second thing is that you should probably translate that error message, since I can't quite make out what it is saying...
0
 

Author Comment

by:altariamx2003
ID: 34923519
the file that you want to open, 'test.xls', has diferente format than the file
extension. Check if the file is not damage
0
 

Author Comment

by:altariamx2003
ID: 34923524
ReleaseComObject???

how can I use it???
0
 

Author Comment

by:altariamx2003
ID: 34923638
I put this code
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each p As Process In pro
            p.Kill()
        Next

Because after the new file is saved the Excell process remains in memory
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 96

Expert Comment

by:Bob Learned
ID: 34926593
Proof-of-concept:

Excel Interop – killing Excel.exe
http://www.craigmurphy.com/blog/?p=82

C#:

using System;
using System.Reflection;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

public class ExcelServices
{
    private Application excelApp = null;
    private Workbook excelWorkbook = null;
    private Sheets excelSheets = null;
    private Worksheet excelWorksheet = null;
    private Workbooks excelWorkbooks = null;

    public ExcelServices()
    {

        excelApp = new ApplicationClass();
        excelApp.Visible = true;
        excelWorkbooks = excelApp.Workbooks;
        excelWorkbook = excelWorkbooks.Add(System.Reflection.Missing.Value);
        excelSheets = excelWorkbook.Worksheets;
        excelWorksheet = (Worksheet) excelSheets[1];
    }

    ~ExcelServices()
    {

        excelWorkbook.Close(false, Missing.Value, Missing.Value);

        excelWorkbooks.Close();
        excelApp.Quit();

        Marshal.ReleaseComObject(excelWorksheet);
        Marshal.ReleaseComObject(excelSheets);
        Marshal.ReleaseComObject(excelWorkbooks);
        Marshal.ReleaseComObject(excelWorkbook);
        Marshal.ReleaseComObject(excelApp);

        excelWorksheet = null;
        excelSheets = null;
        excelWorkbooks = null;
        excelWorkbook = null;
        excelApp = null;

        GC.GetTotalMemory(false);
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.GetTotalMemory(true);
    }

}

Open in new window


VB.NET:

Imports System.Reflection
Imports System.Runtime.InteropServices
Imports Microsoft.Office.Interop.Excel

Public Class ExcelServices
	Private excelApp As Application = Nothing
	Private excelWorkbook As Workbook = Nothing
	Private excelSheets As Sheets = Nothing
	Private excelWorksheet As Worksheet = Nothing
	Private excelWorkbooks As Workbooks = Nothing

	Public Sub New()

		excelApp = New ApplicationClass()
		excelApp.Visible = True
		excelWorkbooks = excelApp.Workbooks
		excelWorkbook = excelWorkbooks.Add(System.Reflection.Missing.Value)
		excelSheets = excelWorkbook.Worksheets
		excelWorksheet = DirectCast(excelSheets(1), Worksheet)
	End Sub

	Protected Overrides Sub Finalize()
		Try

			excelWorkbook.Close(False, Missing.Value, Missing.Value)

			excelWorkbooks.Close()
			excelApp.Quit()

			Marshal.ReleaseComObject(excelWorksheet)
			Marshal.ReleaseComObject(excelSheets)
			Marshal.ReleaseComObject(excelWorkbooks)
			Marshal.ReleaseComObject(excelWorkbook)
			Marshal.ReleaseComObject(excelApp)

			excelWorksheet = Nothing
			excelSheets = Nothing
			excelWorkbooks = Nothing
			excelWorkbook = Nothing
			excelApp = Nothing

			GC.GetTotalMemory(False)
			GC.Collect()
			GC.WaitForPendingFinalizers()
			GC.Collect()
			GC.GetTotalMemory(True)
		Finally
			MyBase.Finalize()
		End Try
	End Sub

End Class

Open in new window

0
 
LVL 12

Accepted Solution

by:
omegaomega earned 500 total points
ID: 34929389
Hello, altariamx2003,

The LearnedOne is quite right about dropping the Process.Kill section of your code.  The "Quit" should be all that you need.

But I think that your real problem here is with the "SaveWorkSpace" method.  You probably want to just use the Workbook's "Save" method here.  I have included a slight modification of your code in the attached snippet that I think will do what you want.  (Changed sections are marked with "'<<<<"

Cheers,
Randy


' ''Dim oSheet As Object                    '<<<<
        ' ''xlTemp = New Application                
        ' ''xlTemp.Workbooks.Open(System.Windows.Forms.Application.StartupPath & "\plantillas\plantilla1.xls")
        Dim oSheet As Excel.Worksheet
        Dim xlTemp As New Excel.Application         '<<<<
        Dim wbkTemp As Excel.Workbook = xlTemp.Workbooks.Open(System.Windows.Forms.Application.StartupPath & "\plantillas\plantilla1.xls")
        oSheet = xlTemp.Worksheets(1)
        oSheet.Range("A1").Value = "Last Name"
        oSheet.Range("B1").Value = "First Name"
        oSheet.Range("A1:B1").Font.Bold = True
        oSheet.Range("A2").Value = "Doe"
        oSheet.Range("B2").Value = "John"
        Dim sv As New SaveFileDialog
        sv.AddExtension = True
        sv.CheckPathExists = True
        sv.DefaultExt = ".xls"
        sv.Filter = "Excel Files|*.xls"
        sv.OverwritePrompt = True
        sv.Title = "Save Excel File As..."
        If sv.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            ' ''xlTemp.SaveWorkspace(sv.FileName)   '<<<<
            wbkTemp.SaveAs(sv.FileName)             '<<<<
        End If
        sv.Dispose()
        wbkTemp.Close(SaveChanges:=False)           '<<<<
        oSheet = Nothing
        xlTemp.Quit()
        ' ''Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")   '<<<<
        ' ''For Each p As Process In pro
        ' ''    p.Kill()
        ' ''Next

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 34931462
Using Quit by itself will not decrement the reference counts, and will leave Excel open...
0
 
LVL 12

Assisted Solution

by:omegaomega
omegaomega earned 500 total points
ID: 34931643
Hello, altariamx2003,

Yes, as TheLearnedOne says...  when you are quitting Excel you should follow the pattern he has shown.  Otherwise, Excel will remain active until your application quits.  Replace the lines following sv.Dispose() with those shown in the snippet.

Cheers,
Randy

wbkTemp.Close(SaveChanges:=False)         
        xlTemp.Quit()

        Marshal.ReleaseComObject(oSheet)
        Marshal.ReleaseComObject(wbkTemp)
        Marshal.ReleaseComObject(xlTemp)

        oSheet = Nothing
        wbkTemp = Nothing
        xlTemp = Nothing

        GC.GetTotalMemory(False)
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.GetTotalMemory(True)

Open in new window

0
 

Author Comment

by:altariamx2003
ID: 34940804
OMEGAOMEGA FOR PRESIDENT!!!!

Thanks bro!!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now