Reputation: 303
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
Once the file is free below message box appears , I click on 'read-write' and my code resumes from where it stopped (Image example)
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:
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
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