?
Solved

open excel files with visual basic NET

Posted on 2011-02-17
9
Medium Priority
?
573 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month14 days, 1 hour left to enroll

801 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