Unwanted excel save prompt when opening with access vba

0
votes

I have an access VBA program that opens an excel file in order to retrieve some data.

Private Function getSampleDescription() As Boolean
' Assign the Excel Object
If gfcHandleErrors Then On Error GoTo errorExit:

getSampleDescription = True
Dim excelapp As Object
Set excelapp = CreateObject("excel.application")

' Assign the workbook
Dim wb As Object
Set wb = excelapp.Workbooks.Open(fileName:=fileName, ReadOnly:=True, Password:=sampleFilePassword)

'Assign the sheet
Dim sh As Object
Set sh = wb.worksheets("Sample Description")

It had been working fine but recently (not sure why), I get an excel prompt asking Save, Don't Save or Cancel.
Excel Save Prompt

This happens immediately after the Workbooks.Open statement. If I choose Save I get a prompt to save the excel table to Copy of ExcelFilename. Once I save the code then proceeds normally. If I choose Don't Save the code just proceeds normally. If I choose Cancel the prompt reappears; choosing Cancel a second time aborts the code.

Any idea what is provoking this? Could it be password protection on my workbook or worksheets. How can I make it stop!

Access and Excel 2010 versions operating on Windows 7.

Thanks for any help ...

2
excelms-accessvba
Does the workbook have any macros which fire when it's opened? You could try adding excelapp.DisplayAlerts = False before opening the workbook. – Tim Williams
does the excel sheet have any code that is trying to save the workbook? – engineersmnky
Tim - No, there are macros but none which are triggered by opening the workbook. – Mike
EngineersMnky - no save code – Mike
Tim - DisplayAlerts = false seems to have solved the problem. Thank you – Mike

2 Answers

0
votes

Simply add the following to the very top of your VBA code.

Application.DisplayAlerts = False

This essentially stops any alert windows from popping up and disrupting your code. Please note: if you don't want to save the excel file after you've retrieved data from it, this should work fine, I think.

0
votes

I know the question was asked 3 years ago, but seems the answer has not been added. Excel prompts to save the document because your code executes an open procedure and re-evaluates the file to a read only, which is effectively a change in the document itself therefore requiring a save procedure.

Application.DisplayAlerts = False
ChangeFileAccess XlFileAccess.xlReadOnly
Application.DisplayAlerts = True

As previously stated by other users the above code will suppress the prompt box but do not forget to revert it back to True after the particular code has executed as you could prevent other errors from displaying.