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

Posted on 2011-04-27
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


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

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

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub
End Class

Open in new window

Question by:LCMSdev
    LVL 29

    Expert Comment

    by:Randy Downs
    you can turn off notifications

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

    LVL 33

    Expert Comment

    by:Todd Gerbert
    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.

    Author Comment

    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.
    LVL 33

    Accepted Solution

    You can still use an Excel file as the underlying data store, without depending on COM, by using OleDb:
    LVL 29

    Expert Comment

    by:Randy Downs

    Author Comment

    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:

                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;""")

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

            Catch ex As Exception
            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 Comment

    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.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Image capture and Deploy method is consist on two phases. In our first phase we capture the image of windows from the PC in which Windows and others softwares are already installed. In second phase we deploy the created image on new PC in which we…
    So many times I have seen the words written in a question "if only I could show you" or " I know how hard it is for you since you can't see it" in any zone. That has inspired me to write about this tool in windows 7 called "Problem Steps Recorder…
    This Micro Tutorial will give you a basic overview of Windows DVD Burner through its features and interface. This will be demonstrated using Windows 7 operating system.
    This Micro Tutorial will give you a basic overview of Windows Live Photo Gallery and show you various editing filters and touches to photos you can apply. This will be demonstrated using Windows Live Photo Gallery on Windows 7 operating system.

    734 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

    25 Experts available now in Live!

    Get 1:1 Help Now