Solved

open excel files with visual basic NET

Posted on 2011-02-17
9
546 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
Comment Utility
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
Comment Utility
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
Comment Utility
ReleaseComObject???

how can I use it???
0
 

Author Comment

by:altariamx2003
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
OMEGAOMEGA FOR PRESIDENT!!!!

Thanks bro!!!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

16 Experts available now in Live!

Get 1:1 Help Now