Solved

open excel files with visual basic NET

Posted on 2011-02-17
9
565 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Expression Evaluater 3 46
vb.net and creating a class 5 31
VB.Net Determine if a mapped network drive exists 2 55
VB.Net  Exporting Excel Woorkbook to PDF 7 60
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 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