asked on

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

Randy Downs

you can turn off notifications

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

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.

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

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

LCMSdev

ASKER

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.

ASKER CERTIFIED SOLUTION

Todd Gerbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Randy Downs

try this - http://www.terminalfusion.com/blog/index.cfm/2009/1/30/Disable-Save-As-in-Excel-with-VBA

LCMSdev

ASKER

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?

LCMSdev

ASKER

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.