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
Solved

open excel files with visual basic NET

Posted on 2011-02-17
9
564 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
No Data for DropDown List 2 28
Visual Studios 1 76
VB.net capture  result of a SQL query in variables 3 21
VS 2015 is giving [No relevant source lines] 2 27
This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

839 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