等待CSV文件打开？ [英] Wait for CSV file to open?

问题描述

我正在Excel中使用VBA下载并打开CSV文件。当我逐步使用调试器时，我的代码工作正常，但是当我尝试正常运行它将无法正常工作，它尝试将信息从新打开的CSV文件复制到现有的.xlsm文件，但它没有找到任何东西。我已经找到了检查文件是否打开的方法，但是我需要它来继续检查，一旦确实存在，继续执行代码。

I am trying to download and open a CSV file using VBA in Excel. When I step through using the debugger my code works fine but when I try to run it normally it won't work, it is trying to copy the info from the newly opened CSV file into the existing .xlsm file but it isn't finding anything. I have found ways of checking that the file is open but I need it to keep checking and once it does exist to continue with the code.

这里是如何获取文件下载：

Here is how Im getting the file to download:

Sub getFile(address)
    On Error Resume Next
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = False
    ie.navigate address
    ie.Quit
    Set ie = Nothing
    On Error GoTo 0
End Sub

然后在sub之后调用getFile，

And then after this in the sub that calls getFile,

Do While Workbooks(Workbooks.Count).Name <> "file.csv"
Loop

Ive尝试了一些变体，调试器缓慢，否则会崩溃。有没有人知道一种方法让它继续检查并等到文件打开？它需要用户做某事，以确保file.csv是打开的。我应该把我的支票寄到sub getFile或保留它被调用后？

Ive tried a few variations that all work fine when taking it slow in the debugger but crashes otherwise. Does anyone know a way to have it keep checking and wait until the file is open? It requires the user to do something so what it to make sure that file.csv is open. Should I put my check in sub getFile or keep it after it is called?

SOULTION：
在下面的hnk的建议中，我尝试了这个工作，它的工作完美：

SOULTION: Taking hnk's advice below I tried this and it worked perfectly:

Sub getFile(address)
    On Error Resume Next
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = False
    ie.navigate address
    Do While Workbooks(Workbooks.Count).Name <> "file.csv"
        DoEvents
    Loop
    ie.Quit
    Set ie = Nothing
    On Error GoTo 0
End Sub

在关闭IE之前移动支票，并将 DoEvents 添加到我的循环中。

Moved the check to before closing IE and added DoEvents to my loop.

推荐答案

通常这种行为不是因为代码逻辑而是因为中断。根据您的环境的性质，它可能是两件事之一

Usually such behavior happens not because of the code logic but because of interrupts. Depending on the nature of your environment it could be one of two things

• 某些背景刷新过程在您的代码执行时间之间执行一段时间造成一些问题。例如在金融服务环境中，您的彭博API可能会及时执行 BlpUpdate 事件


• 您的代码阻止了大量的时间，导致意外的行为，因为您的循环中触发的事件被阻止太久等待您的代码在主线程上完成。

所以您需要尝试以下两个解决方案，其中一个应该工作

So you need to try the following two solutions, one of which should work

对于第二个问题，您的

Do While Condition
' ... your code ...
Loop

添加一行代码，检查和清除任何挂起的事件队列，使其成为...

add a line of code that checks for and clears any pending Event queues, making it...

Do While Condition
DoEvents     ' either put it at the start or at the end of your code
' ... your existing code ...
Loop

这将给系统一些呼吸空间来包装待处理的事件。您需要尝试您的 DoEvents 代码的位置。

This will give the system some 'breathing space' to wrap up pending events. You'll need to experiment with the location of your DoEvents code.

对于第一个问题，您可以尝试将您的整个循环包含在 Event Protected 代码区域中，以确保在任何操作之前完成运行。

For the first problem, you can try enclosing your entire loop within an Event Protected area of code, to ensure it finishes running before anything disturbs it.

Application.EnableEvents = False

'your loop and other code comes here

Application.EnableEvents = True

再次，您可能需要一些尝试和错误才能看到有多少代码需要投入无事件区域，然后按预期工作。

Once again, you might need a bit of trial and error to see how much of the code needs to be thrown into the Event-free zone before it works as expected.

这篇关于等待CSV文件打开？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！