Suppress right-click file open in SaveFileDialog

[snuv]

I need to prevent users from right clicking on a file and selecting Open in the SaveFileDialog

Does anyone have any suggestions on how I can do that, short of cloning the SaveFileDialog class

Thanks in advance
Snuv

"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary

 

[Aptitude]

Just out of interest, why do you want to do that?

 

[snuv]

Aptitude

We want to allow users to select an excel file which we will then manipulate silently.

If the user opens an excel file by right-clicking and selecting open, the first instance of exel we open (silently) closes the workbook they opened and loses any changes they have made without prompting them to save.

It would be better if that didnt happen but the only solution we could come up with was disabling the open if possible.

Cheers
Snuv

"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary

 

[dglienna]

What about the start menu? That would start Excel as well

-David
2006 & 2007 Microsoft Most Valuable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[ZmrAbdulla]

Why not a read only excel file?

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[snuv]

The user selects an excel file, we have no control over whether its read-only.
When they click on ok, we open the file in excel silently to read a few things out of it such as details of any named ranges which loses any changes they made without warning them.

"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary

 

[dglienna]

Copy and edit the original, so that they can keep opening it. That way, you could detect if it's open when you try to write it back.

-David
2006 & 2007 Microsoft Most Valuable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[Aptitude]

Given that there a a huge number of ways the excel file could be open before your open file dialog is run I don't think blocking the open menu item is going to solve your headaches. The best thing to do is to check if the file is already open when you open it and display a dialog to the user asking them to close it again. If my memory serves me correctly I believe that the excel library allows you to open the file in some kind of exclusive mode.

 

[snuv]

Thanks Aptitude, that may solve the problem

The problem only seems to be with files that are opened through the right click menu

If a user rights click on file A.xls and selects open
Then they select B.xls and click on save

When we open excel to read data from the file Excel/VB.Net closes A.xls with no warning and uses that instance of excel to open B.xls

"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary

 

[Aptitude]

It sound's like that shouldn't be happening. You should be able to open two separate Excel files at the same time and not have them interfere with each other. How exactly are you opening and closing the files? Would it be possible for you to give us a couple of short code snippets?

 

[PRPhx]

I'm confused here. Are you wanting your users to SAVE an excel spreadsheet or just SELECT an Exccel spreadsheet that you will manipulate behind the scenes?

If you simply want to allow your users to SELECT a sheet, why not put the availble sheets into a listbox. Clicking on a file then would send the selected file to Excel, and then Excel could do it's thing.

 

[snuv]

PRPhx
We generate a table or tables of data entirely unrelated to excel. We offer the option in our software to save those tables, with all their formatting, to an excel workbook.

We want to give a range of options to users in a single dialog so that they can choose where to save data in a preformatted spreadsheet.

To do that, we need to read in some data such as named ranges and sheet names which means opening the workbook.

If the workbook doesnt exist then the user gets standard options

Aptitude
We just open the file if it exists when the save file dialog returns OK.I dont have the code here but I will try to post some when I get back to work. We didnt write the code behind the savefile dialog, That one is all microsoft.

Thanks for your time on this one
Cheers
Snuv


"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary

 

[snuv]

Private m_Book As Excel.Workbook
Private m_Books As Excel.Workbooks
Private m_ExcelApp As Excel.Application

Private Sub InitializeGUI()
	m_currentLocale = System.Globalization.CultureInfo.CurrentCulture()
	System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
	m_ExcelApp = New Excel.Application
	m_ExcelApp.DisplayAlerts = False
	'used to kill the process so must be unique GUID
	m_ExcelApp.Caption = System.Guid.NewGuid.ToString
End Sub

Private Sub m_butBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles m_btnBrowse.Click
	m_strWorkbookOld = Me.m_tbWorkbookName.Text
	Dim dlg As New SaveFileDialog
	dlg.InitialDirectory = GetReportOutputDir()
	dlg.DefaultExt = "xls"
	dlg.Filter = "Microsoft Excel Worksheets (*.xls)|*.xls"
	dlg.OverwritePrompt = False

	If Not dlg.ShowDialog() = DialogResult.Cancel Then
		Dim strFile As String = dlg.FileName
		Me.m_tbWorkbookName.Text = strFile
	End If

End Sub


Private Sub m_tbWorkbookName_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles m_tbWorkbookName.TextChanged
	If Not File.Exists(m_tbWorkbookName.Text) Then
		SetDefaults()
	Else
		Try
			m_Books = m_ExcelApp.Workbooks
			m_Book = m_Books.Open(m_tbWorkbookName.Text)
			m_ExcelApp.Visible = False

			Me.LoadWorksheetNames()
			Me.LoadNamedRanges()
			Me.chkReplaceExistingWorkbook.Enabled = True
			Me.ReplaceWorkbookCheckedHandler(chkReplaceExistingWorkbook, New System.EventArgs)

		Catch ex As System.Runtime.InteropServices.COMException
			If ex.ErrorCode = COM_ERR_CANNOT_ACCESS_READONLY_DOCUMENT Then
				TimedStatusMessage("Can't open password protected spreadsheet")
				If Not m_strWorkbookOld.Equals(m_tbWorkbookName.Text) Then
					Me.m_tbWorkbookName.Text = Me.m_strWorkbookOld
				End If
			Else
				Throw ex
			End If

		Catch ex As Exception
			Throw ex
		Finally
			If Not m_Book Is Nothing Then
				m_Book.Close(False)
			End If
		End Try
	End If
End Sub


Private Sub ExcelGUI_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
	ExcelHelper.ReleaseExcelObject(m_Book)
	ExcelHelper.ReleaseExcelObject(m_Books)

	Try
		m_ExcelApp.Quit()
	Catch ex As System.Runtime.InteropServices.COMException
		'prevent error being thrown if book invoked has disconnected from its client
	Finally
		ExcelHelper.TerminateExcel(m_ExcelApp)
	End Try

	System.Threading.Thread.CurrentThread.CurrentCulture = Me.m_currentLocale

End Sub

"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary

 

[Aptitude]

What is ExcelHelper? I am guessing the the bug is in its TerminateExcel function. I am going to guess this is uesd to get around the issue with COM Reference counting. For a full explanation take a look at this Microsoft Knoledge Base Article Office application does not quit after automation from Visual Studio .NET client

 

[snuv]

ExcelHelper is one of our classes.
It contains shared functions that we use in various places for playing with Excel such as removing worksheet names by index number, converting named ranges to cell references and so on. It includes the code to kill off excel

I think I have worked out what the problem is though.

We open Excel but not a workbook when the dialog is initialised.
When a user opens an workbook, the apparently unused instance of Excel that is already running is used to open it.
When we open a workbook it closes the one the user opened because we are using the same instance of Excel.
The solution is probably to be a bit smarter about when we open Excel or to open a dummy file immediately we after we open Excel.


"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary