访问/ Excel的VBA - 延时 [英] Access/Excel VBA - Time delay

问题描述

请注意：

1. 在Excel中刷新表链接到Access数据库

2. 在Excel表格需要以刷新如Test_Sheet1，Test_Sheet2，Test_Sheet3

3. Excel文件是由多个用户访问

问题

在访问VBA中，如果一个Excel文件正在使用（只读），我怎样才能实现在Access VBA code延迟等待文件被读/写，以便它可以继续code（刷新表，保存/关闭文件）。请注意，该Excel文件确实需要才能被刷新。

我没有实现与时间延迟的错误处理，因此如果错误号= 1004，然后按X.延迟 这并没有真正做的工作。

定时在VBA 延迟

 功能RefreshExcelTables（）


昏暗ExcelApp作为对象
设置ExcelApp =的CreateObject（Excel.Application）

ExcelApp.workbooks.OpenC：\测试\ Test_Sheet1.xlsb
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook.Save
ExcelApp.ActiveWindow.Close


ExcelApp.workbooks.OpenC：\测试\ Test_Sheet2.xlsb
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook.Save
ExcelApp.ActiveWindow.Close


ExcelApp.workbooks.OpenC：\测试\ Test_Sheet3.xlsb
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook.Save
ExcelApp.ActiveWindow.Close



设置ExcelApp =什么


端功能
 

弹出消息（以下图片）

更新

 功能RefreshExcelTables（）

对错误转到错误

昏暗ExcelApp作为对象
设置ExcelApp =的CreateObject（Excel.Application）

ExcelApp.workbooks.OpenC：\测试\ Test_Sheet1.xlsb
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook.Save
ExcelApp.ActiveWindow.Close


ExcelApp.workbooks.OpenC：\测试\ Test_Sheet2.xlsb
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook.Save
ExcelApp.ActiveWindow.Close


ExcelApp.workbooks.OpenC：\测试\ Test_Sheet3.xlsb
ExcelApp.ActiveWorkbook.refreshall
ExcelApp.ActiveWorkbook.Save
ExcelApp.ActiveWindow.Close

错误：

如果Err.Number的= 1004然后

呼叫暂停（5）

恢复

结束如果

设置ExcelApp =什么


端功能



公共功能暂停（intSeconds为整数）

昏暗dblStart作为双

如果intSeconds＆GT; 0然后

dblStart =定时器（）

难道当定时器＆LT; dblStart + intSeconds

循环

结束如果

端功能
 

解决方案

我用这对暂停code处理：

公共功能暂停（intSeconds为整数）     昏暗dblStart作为双     如果intSeconds＆GT; 0然后         dblStart =定时器（）         难道当定时器＆LT; dblStart + intSeconds             摆弄大拇指         循环     结束如果 端功能

所以，你只想：呼叫暂停（1）只要您需要暂停时，它会等待一秒钟。

效果很好，如果你只需要推迟全额秒的增量。我还有一个更强大的一家拥有code，可用于更小的增量，如果你想它来代替。

Note:

1. Refresh tables in Excel that are linked to an Access database

2. Tables in Excel need to be refreshed in order e.g Test_Sheet1, Test_Sheet2, Test_Sheet3

3. 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.

Timing Delays in VBA

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 - 延时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！