Elixir
Elixir

Reputation: 303

Access/Excel VBA - Remove display alerts and messages

I have a macro/vba in access that saves several excel workbooks after editing. However sometimes other users might be using one of the excel files in read/write mode.

The below message box appears, what i do is keep clicking no till the user has finished using the Excel file

enter image description here

Once the file is free below message box appears , I click on 'read-write' and my code resumes from where it stopped (Image example)

enter image description here

Question - How do I get Access VBA or Excel VBA to click 'No' for me?

Note: I've used Application.DisplayAlerts and DoCmd.SetWarnings both default was Yes. (Or it might be me not implementing them correctly).

Code:

Access/Excel VBA - Time delay

Function RefreshExcelTables()

On Error GoTo Error

Dim ExcelApp As Object
Set ExcelApp = CreateObject("Excel.Application")

ExcelApp.workbooks.Open "c:\test\Test_Sheet1.xlsb"
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook.Save
ExcelApp.ActiveWindow.Close


ExcelApp.workbooks.Open "c:\test\Test_Sheet2.xlsb"
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook.Save
ExcelApp.ActiveWindow.Close


ExcelApp.workbooks.Open "c:\test\Test_Sheet3.xlsb"
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook.Save
ExcelApp.ActiveWindow.Close

Error:

If Err.Number = 1004 Then

call pause(5)

Resume

End If

Set ExcelApp = Nothing


End Function



Public Function Pause(intSeconds As Integer)

Dim dblStart As Double

If intSeconds > 0 Then

dblStart = Timer()

Do While Timer < dblStart + intSeconds

Loop

End If

End Function

Upvotes: 0

Views: 10932

Answers (2)

ikervb
ikervb

Reputation: 11

You can try with Docmd.setwarnings False

Upvotes: 0

user4039065
user4039065

Reputation:

You can start with the following option adjustment.

DoCmd.SetWarnings False

More at: DoCmd.SetWarnings Method (Access) and DoCmd.SetWarnings Method (Access Developer Reference).

For the Excel.Application object you may have to use that instance's equivalent.

ExcelApp.DisplayAlerts = false

The reference documentation for Excel's DisplayAlerts is at Application.DisplayAlerts Property.

Upvotes: 2

Related Questions