• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

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

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

  • 3
  • 2
  • 2
1 Solution
Randy DownsOWNERCommented:
you can turn off notifications

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

Todd GerbertIT ConsultantCommented:
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.


LCMSdevAuthor 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.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Todd GerbertIT ConsultantCommented:
You can still use an Excel file as the underlying data store, without depending on COM, by using OleDb: http://support.microsoft.com/kb/316934
LCMSdevAuthor 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:

            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?
LCMSdevAuthor 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now