如何优化打开和关闭excel工作簿来提取数据运行速度更快 [英] How to optimize opening and closing excel workbooks to extract data to run faster

问题描述

我现在知道打开和关闭excel工作簿的两种方法来从他们中提取数据以在单个工作簿中进行总结。

第一种方法如下： / p>

  Dim wbDataSheet As Workbook 
 For FNum = LBound（MyFiles）To UBound（MyFiles）
设置wbDataSheet = Workbooks.Open（MyPath& MyFiles（FNum），0，True）
 
'捕获数据
 
 wbDataSheet.Close（False）
 Next FNum 
  

第二个是这样的：

 code> Dim XL As New Excel.Application 
 For FNum = LBound（MyFiles）To UBound（MyFiles）
 With XL 
 .Workbooks.Open FileName：= MyPath& MyFiles（FNum），ReadOnly：= True 
 .Visible = False 
结束
 
'捕获数据
 
 XL.ActiveWorkbook.Close False 
 Next FNum 
  

我的困境是这样的：

方法1更快（约为.35秒/文件为1052个文件），但它会打开我的任务栏，因为它打开新的工作簿（使得几乎不可能选择一个不同的excel工作簿），由于某些原因，我的代码可以很容易通过按下移动不止一次或全部按下，打破了我的迫使我启动代码。

方法2明显较慢（约.56秒/文件为1052个文件），但是由于excel应用程序被隐藏，我的任务栏仍然可用，并且按shift不会打破码。

我想知道的是，有没有办法运行方法1而不会破坏任务栏或转移停止代码？或者，有没有办法加快方法2的速度接近方法1？

*注意：我已经在使用以下代码来优化我的速度并限制vba /工作簿交互。除了访问工作簿的方法外，每种情况下的其他代码都是一样的。

 应用程序
。计算= xlCalculationManual 
 .ScreenUpdating = False 
 .EnableEvents = False 
 .DisplayAlerts = False 
结束
  

解决方案

使用ExecuteExcel14Macro调用Get值函数的示例代码

  Sub test（）
 Dim p As String，f As String，s as String，a as String 
 
p =C：\Users\User\\ \\文件\
f =Example.xlsx
s =Sheet1'这是文件中的工作表的名称
a =D56'你想要的值的范围'不知道你是否可以拉多于单元的值
 
 ThisWorkbook.Worksheets（Sheet2）。Range（F21）= GetValue（p，f，s，a）'Transfer值
 End Sub 
  

以下是您的功能将需要

 函数GetValue（路径，文件，工作表，参考）
'从关闭的工作簿中检索一个值
 Dim arg As String 
'确保文件存在
如果Dir（Path&文件）=然后
 GetValue =找不到文件
退出函数
结束如果
'创建参数
 arg ='&路径& [&档案& ]&片材& ！ &安培; _ 
范围（ref）.Range（A1）。地址（，，xlR1C1）
'执行XLM宏
 GetValue = ExecuteExcel4Macro（arg）
结束函数
  

I currently know of two methods to open and close excel workbooks to extract data from them to summarize in a single workbook.

The first method is as follows:

Dim wbDataSheet As Workbook
For FNum = LBound(MyFiles) To UBound(MyFiles)
    Set wbDataSheet = Workbooks.Open(MyPath & MyFiles(FNum), 0, True)

     'Capture data

    wbDataSheet.Close (False)
Next FNum

The second is this:

Dim XL As New Excel.Application
For FNum = LBound(MyFiles) To UBound(MyFiles)
    With XL
        .Workbooks.Open FileName:=MyPath & MyFiles(FNum), ReadOnly:=True
        .Visible = False
    End With

    'Capture Data

    XL.ActiveWorkbook.Close False
Next FNum

My dilemma is this:

Method 1 is faster (about .35 seconds/file for 1052 files) but it explodes my task bar as it opens new workbooks (making it near impossible to select a different excel workbook) and for some reason my code can be easily broken by pressing shift more than once or holding it at all, forcing me to start the code over.

Method 2 is noticeably slower (about .56 seconds/file for 1052 files) but since the excel application is hidden my taskbar remains usable and pressing shift doesn't break the code.

What I want to know is, is there a way to run method 1 without ruining my taskbar or having shift stop the code? Alternatively, is there a way to speed up method two to speeds near method 1?

*Note: I am already using the code below to optimize my speed and limiting vba/workbook interaction. Besides the methods to access the workbooks, the rest of the code in each scenario is identical.

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With

解决方案

Example code for calling the Get value function which uses ExecuteExcel14Macro

Sub test()
Dim p As String, f As String, s as String, a as String

   p = "C:\Users\User\Documents\"
   f = "Example.xlsx"
   s = "Sheet1"  'This is the name of the Sheet in your file
   a = "D56"     'Range of the value you want I'm not sure if you can pull more than cell's value or not

   ThisWorkbook.Worksheets("Sheet2").Range("F21") = GetValue(p, f, s, a)  'Transfer the value
End Sub

Below is the Function that you will need

Function GetValue(Path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Dir(Path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
'   Create the argument
    arg = "'" & Path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

这篇关于如何优化打开和关闭excel工作簿来提取数据运行速度更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！