0

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
Community
  • 1
  • 1
Elixir
  • 303
  • 3
  • 9
  • 26

2 Answers2

2

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.

  • 1
    Plus there should be a test on whether the file is open by somefine else, I guess that was OP's main concern. – user3819867 May 13 '15 at 20:49
  • @user3819867 - Good point. That is going to require a bit more code than a single line. Knowing if Excel 2013 with its special way of handing application instances and application windows would be helpful on that front. –  May 13 '15 at 20:56
  • I'm just monitoring as it's going to be a helluva `While ... Wend` loop. – user3819867 May 13 '15 at 20:58
0

You can try with Docmd.setwarnings False

ikervb
  • 11
  • 1
  • 2