Excel文件循环 [英] excel files loop

问题描述

在我的Excel工作表中，我使用的是引用外部工作表的公式.我要编写一个宏来打开所有外部工作表，而在 E7 E7 中的值(我在其中插入外部工作表的名称)>并复制生成的值.

In my excel sheet I'm using formulas which referring to external sheets. Instead of manually changing the value in cell E7 (where I insert name of external sheet), I want to write an macro to open all of external sheets, change reference value in E7 and copy the generated values.

不幸的是，我的代码无法正常工作-Excel无法查看"外部工作表中的值.我应该改变什么?

Unfortunately my code does't work - Excel can't "see" the values in the external sheet. What should I change?

Sub lista_plik()
    Dim oExcel As Excel.Application
    Dim oWB As Workbook
    Set oExcel = New Excel.Application
    folder = "path_here"
    folder2 = folder & "*.xlsx"

    arkusz = Dir(folder2)

    Do While arkusz <> ""
        x = x + 1
        Range("E7").Value = Replace(arkusz, ".xlsx", "")
        arkusz = folder & arkusz

        Set oWB = oExcel.Workbooks.Open(arkusz)
''''''''''''''''''''
HERE CODE TO COPY VALUES
'''''''''''''''''''''''
        oWB.Close
        arkusz = Dir
    Loop
End Sub

推荐答案

我同意brettdj，很难知道您要达到的目标.

I agree with brettdj, it is difficult to know what you are trying to achieve.

Chris已修复了您的两个错误，但没有解释我怀疑是问题原因的第二次更正.

Chris has fixed two of your errors but without explaining the second correction which I suspect is the cause of the problem.

在代码中，您使用 Range("E7").Value .这是指活动工作簿的活动工作表内的单元格E7.克里斯将其更改为 oWS.Range("E7").Value ，这是一个改进，但是oWS尚未初始化，因此还不够.

In your code you use Range("E7").Value. This refers to cell E7 within the active sheet of the active workbook. Chris has changed this to oWS.Range("E7").Value, which is an improvement, but oWS has not been initialised so its not enough.

我使用多个工作簿的经验是，您必须保持完全的控制权.我怀疑您的错误是Excel在查看错误的E7.以下步骤将消除该错误和任何类似的错误.

My experience of working with multiple workbooks is that you must keep total control. I suspect your error is that Excel is looking at the wrong E7. The steps below will eliminate that and any similar errors.

第1步

通过检查 WorkBooks.Count = 1 ，确保一开始只打开一个工作簿.如果允许打开两个或更多工作簿，则将需要代码来检查活动工作簿是否是您想要的工作簿.像这样:

Make sure there is only one workbook open at the beginning by checking that WorkBooks.Count = 1. If you permit two or more workbooks being open, you will need code to check that the active workbook is the one you want. So something like:

If WorkBooks.Count > 1 Then
  Call MsgBox("Please close other workbooks then try again.", vbYesOnly)
  Exit Sub
End If

第2步

在打开任何其他工作簿之前，请记录为主工作簿.

Before you open any other workbook, record which is the master workbook.

Dim WBkMaster As WorkBook

WBkMaster = ActiveWorkBook

第3步

始终明确引用工作簿和工作表.例如:

Always refer to workbooks and worksheets explicitly. For example:

With WBkMaster
  With Sheets("x1")
    .Range("E7").Value = ...
    ' Extract data from Sheet x1 to variables here
  End With
  With Sheets("x2")
    ' Extract data from Sheet x2 to variables here
  End With
End With

Set WBkDestination = Application.Workbooks.Open(arkusz)

With WBkDestination
  With Sheets("x3")
    ' Save data for Sheet x3 from variables here
  End With
  With Sheets("x4")
    ' Save data for Sheet x4 from variables here
  End With
  . Close
End With
oWB = Nothing   ' Ensure no reference to closed workbook

还有其他在工作表和工作簿之间复制数据的方法，但是您没有提供足够的信息来推荐哪种方法最合适.关键问题是，您必须对要处理的工作表和工作簿一目了然.

There are other approaches to copying data between worksheets and workbooks but you do not give enough information to recommend which approach would be the most appropriate. The key issue is that you must be totally explicit about which sheet and which workbook you want addressed.

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