vb app opens xls file - how to close?

[chapm4]

Hi,
I have an app that opens an existing excel file for viewing. The user can close excel with the x, but I get "do you want to save your changes". I have the file set to read only, but i still get the question. Any suggestions?

 

[hermanlaksko]

have a look at this thread: thread692-90756

 

[kevinclark]

If I understood correctly you are opening the file through a VB app but allowing the end-user to close the workbook via the Excel application (outside your VB app)

If you are only talking about opening and closing a few of the same Excel files repeatedly you could just simply modify the Excel workbooks BeforeClose events

Sample1

In the Excel Workbooks ThisWorkBook module add

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Saved = True
End Sub


If on the other hand you are allowing the user to open any number of undetermined Workbooks and likewise closing them via the Excel application (Again not through your VB app) then maybe you can modify your VB Application to write directly to the Workbooks module. In other words this would do the same thing as Sample1 but it would do it programmatically through your VB App (When a file is being opened) and would not save changes to the Excel code module.

Sample2

This is a tweaked version of what I have used. If interested then you will need to set the following references in your VB app

Microsoft Visual Basic For Applications Extensibility 5.3
Microsoft Excel 9.0 Object Library


You should be able to test this code by pointing it to an existing Excel file. Run the code (check the code module) modify the Workbook and then close the Excel file via Excel.

Sub Modify_ThisWorkBook_Module()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlCM As CodeModule

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open("C:\Sample.xls&quot
Set xlCM = xlWB.VBProject.VBComponents("ThisWorkBook&quot.CodeModule

xlApp.Visible = True

If Not xlCM Is Nothing Then
With xlCM
.InsertLines .CountOfLines + 1, "Private Sub Workbook_BeforeClose(Cancel As Boolean)" & Chr(13)
.InsertLines .CountOfLines + 1, "ActiveWorkbook.Saved = True" & Chr(13)
.InsertLines .CountOfLines + 1, "End Sub" & Chr(13)
End With
Set xlCM = Nothing
End If
End Sub


Private Sub Command1_Click()
Modify_ThisWorkBook_Module
End Sub

 

[chapm4]

Thanks guy's. I think both of you have helped me alot.