We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

VB.NET - get rid of Save dialog when closing Excel file in Windows 7

LCMSdev
LCMSdev asked
on
Medium Priority
481 Views
Last Modified: 2012-05-11
Using VB.NET in Visual Studio 2008, I'm trying to use an Excel spreadsheet as data storage for a simple program. The entire application code is attached below.

In Windows XP, this works exactly as intended. However, in Windows 7, when the application goes to save the data to the spreadsheet, I get a Save dialog prompting me to save the spreadsheet as a copy.

I want the Excel portion to be transparent to the user, as it is when the program is run under XP. Does anyone have any suggestions for a quick fix?
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button1.Click
        Dim x As Integer = 0
        If Not (txtFirstName.Text = "" Or txtLastName.Text = "" Or txtCompany.Text = "" Or txtEmail.Text = "") Then
            Dim xcApp As Excel.Application
            Dim xcWB As Excel.Workbook
            Dim xcWS As Excel.Worksheet
            Dim LastRow As Integer = 2
            Dim FirstName, LastName, Email, Company, Title, Phone As String

            FirstName = txtFirstName.Text
            LastName = txtLastName.Text
            Email = txtEmail.Text
            Company = txtCompany.Text
            Title = txtTitle.Text
            Phone = txtPhone.Text

            xcApp = New Excel.ApplicationClass
            xcWB = xcApp.Workbooks.Open("C:\Data.xlsx")
            xcWS = xcWB.Worksheets("sheet1")

            LastRow = xcWS.Cells(1, 10).value
            xcWS.Cells(LastRow, 1).value = FirstName
            xcWS.Cells(LastRow, 2).value = LastName
            xcWS.Cells(LastRow, 3).value = Email
            xcWS.Cells(LastRow, 4).value = Company
            xcWS.Cells(LastRow, 5).value = Title
            xcWS.Cells(LastRow, 6).value = Phone
            LastRow = LastRow + 1
            xcWS.Cells(1, 10).value = LastRow
            xcWB.Close(True)
            xcApp.Quit()

            releaseObject(xcApp)
            releaseObject(xcWB)
            releaseObject(xcWS)

            txtFirstName.Text = ""
            txtLastName.Text = ""
            txtEmail.Text = ""
            txtCompany.Text = ""
            txtTitle.Text = ""
            txtPhone.Text = ""

        Else
            x = MsgBox("Please fill out all required fields.", vbOKOnly, "Required fields empty")
        End If

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

Open in new window

Comment
Watch Question

CERTIFIED EXPERT

Commented:
you can turn off notifications

Application.DisplayAlerts = False
The warningmessages will remain turned off until you enable them again with this command:

Todd GerbertSenior Engineer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Using COM InterOp with Office applications, like Excel, can be a little tricky to progam and tends to be error-prone (as you've discovered).  

This isn't quite a quick-fix, but a good idea nonetheless: since you're only storing some simple values I would consider writing them to an XML file (which could be done very easily; using an actual database (e.g. SQL Compact embedded database or an Access database file); or at the very least use OleDb to more directly read/write the contents of the Excel file (without requiring COM).  All of these methods have the advantage of being more reliable, running faster, consuming fewer resources, and don't require Microsoft Office be installed on the target computer.

http://vb.net-informations.com/excel-2007/vb.net_excel_oledb.htm

http://support.microsoft.com/kb/316730

Author

Commented:
Number-1: Thanks, but it still pops the save dialog, even when I set DisplayAlerts to False.

tgerbert - I figured XML or databases would come up, and I do appreciate the advice, but I'm committed to using Excel for various reasons I won't go into. It's only going to be run once, on one machine, that happens to be using Windows 7. As I said, the program works as intended on XP systems; I just need to get past having it pop a Save dialog at me on 7.
Senior Engineer
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT

Commented:

Author

Commented:
Ok, tgertbert, you talked me into it. :)

So, I've got the connection working through OleDb. But, the kb article instructs you to reference cells by name, which doesn't seem to work. The code I'm using to test this is:

Try
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim MyCommand As New System.Data.OleDb.OleDbCommand
            MyConnection = New System.Data.OleDb.OleDbConnection _
            ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Data.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;""")

            MyConnection.Open()
            MyCommand.Connection = MyConnection
            MyCommand.CommandText = "INSERT INTO [Sheet1$] (B2, C2) values ('Test1','Test2')"
            MyCommand.ExecuteNonQuery()
            MyConnection.Close()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

...and when I run this, I get the following error: "The INSERT INTO statement contains the following unknown field name: 'B2'. Make sure you have typed the name correctly, and try the operation again."

Any idea what I'm doing wrong?

Author

Commented:
Never mind, got it. Apparently HDR=YES imports my column headers as range names, so instead of specifying a range I just use ([First Name],[Last Name]). That's convenient.

You're right, tgerbert, this is a better way to do it. Thanks for the advice.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.