VBA EXCEL更新另一个工作簿中的单元格而无需打开该工作簿 [英] VBA EXCEL update cell in another workbook without opening the workbook

问题描述

我有一个包含工作簿列表的目录，我想遍历它们而不打开它们并更新某个工作表中的单元格

I have a directory with list of Workbooks, I want to loop through them withouth opening them and update a Cell in a certain Sheet

我尝试使用

Dim wb As Workbook
Set wb = Workbooks("Z:\dir\bla.xls") 'THIS WILL COME TRHOUGH WHEN I LOOP 
Set ws2 = wb.Sheets("TestSheet") 'SHEET NAME

Set CurCell_2 = ws2.Range("A1")
CurCell_2.Value = 5

问题来了，它仅在我已经打开工作簿时才起作用.我可以使用:

The Problem comes it only works when I have the Workbook already open. I can use:

Workbooks.Open

但是随后它在后台打开，需要很长时间才能遍历它们.

But then It opens up in the background and takes to long to run through them all.

任何人都可以帮忙

推荐答案

如果不打开工作簿，则无法执行此操作.但是，我发现使用 Application.EnableEvents 并将其设置为false可以大大加快该过程，因为我们在工作簿打开事件上具有宏.

You cannot do that without opening the workbooks. However, I have found in my case that using Application.EnableEvents and setting it to false sped up greatly the process because we have macros on workbook open event.

这篇关于VBA EXCEL更新另一个工作簿中的单元格而无需打开该工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！