问题描述
注意：
-
在链接到Access数据库的Excel中刷新表
-
Excel中的表需要按顺序刷新，例如Test_Sheet1，Test_Sheet2，Test_Sheet3
-
Excel文件由多个用户访问
问题
在Access vba中，如果是excel文件正在使用（只读），如何在Access vba代码中实现延迟等待文件被读/写，以便它可以继续执行代码（刷新表，保存/关闭文件）。请注意，excel文件确实需要按顺序刷新。
我做了一个延迟时间的错误句柄，所以如果错误号= 1004，那么延迟X。
这没有真正做到这个Job。
函数RefreshExcelTables（）
Dim ExcelApp As Object
设置ExcelApp = CreateObject（Excel.Application）
ExcelApp.workbooks.Openc：\test\Test_Sheet1.xlsb
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook。保存
ExcelApp.ActiveWindow.Close
ExcelApp.workbooks.Openc：\test\Test_Sheet2.xlsb
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook.Save
ExcelApp.ActiveWindow.Close
ExcelApp.workbooks.Openc：\test\Test_Sheet3.xlsb
ExcelApp .ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook.Save
ExcelApp.ActiveWindow.Close
设置ExcelApp =没有
结束功能
弹出消息（下图）
函数RefreshExcelTables（）
错误GoTo错误
Dim ExcelApp As Object
设置ExcelApp = CreateObject（Excel.Application ）
ExcelApp.workbooks.Openc：\test\Test_Sheet1.xlsb
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook.Save
ExcelApp .ActiveWindow.Close
ExcelApp.workbooks.Openc：\test\Test_Sheet2.xlsb
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook。保存
ExcelApp.ActiveWindow.Close
ExcelApp.workbooks.Openc：\test\Test_Sheet3.xlsb
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook.Save
ExcelApp.ActiveWindow.Close
错误：
如果Err.Number = 1004然后
调用暂停（5）
简历
结束如果
设置ExcelApp =没有
结束函数
公共功能Pause（intSeconds As Integer）
Dim dblStart As Double
如果intSeconds> 0然后
dblStart = Timer（）
做定时器& dblStart + intSeconds
循环
结束如果
结束函数
我以前用于暂停代码处理：
public Function Pause（intSeconds As Integer）
Dim dblStart As Double
如果intSeconds> 0然后
dblStart = Timer（）
做定时器& dblStart + intSeconds
'Twiddle thumbs
循环
如果
结束函数
所以你只需要：拨打暂停（1）无论你需要暂停，它将等待一秒钟。
如果您只需要延迟全秒秒的增量，效果不错。我有另一个更强大的一个更多的代码，如果你想要更多的代码可以使用更小的增量。
Note:
Refresh tables in Excel that are linked to an Access database
Tables in Excel need to be refreshed in order e.g Test_Sheet1, Test_Sheet2, Test_Sheet3
Excel files are accessed by multiple users
Question
In Access vba, If an excel file is in use (Read only), How can I implement a delay in the Access vba code to wait for the file to be Read/write so that it can continue with the code (refresh tables , save/close file). Please note that The excel files do need to be refreshed in order.
I did Implement a Error handle with time delay, so if error number = 1004 then delay by X.This didn't really do the Job.
Function RefreshExcelTables()
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
Set ExcelApp = Nothing
End Function
Popup messages (images below)
Update
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
I used to use this for pausing code processing:
Public Function Pause(intSeconds As Integer)
Dim dblStart As Double
If intSeconds > 0 Then
dblStart = Timer()
Do While Timer < dblStart + intSeconds
' Twiddle thumbs
Loop
End If
End Function
So you would just: Call Pause(1) wherever you need the pause at and it will wait for a second.
Works well if you only need to delay in full second increments. I have another more robust one with more code that can be used for much smaller increments if you want it instead.
这篇关于访问/ Excel VBA - 延时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！