VBA宏来批量更新同一位置的多个文件 [英] VBA macro to mass update multiple files in same location

问题描述

很新，所以请帮忙。我试图在静态文件夹位置大量更新文件，一个文件夹中有许多文件。

我想做的是

• 在Excel 2010中运行VBA宏以转到网络位置文件夹，


• 打开文件夹中的第一个文件。


• 取消保护工作簿和工作表，调用另一个marco来运行更改


• 然后保护工作表关闭文件


• 然后移动到文件夹中的下一个文件，直到所有文件被更正。

我已经创建了marco来进行更改，这被称为编辑

文件类型为xlsm，工作簿和工作表受密码保护如何自动运行宏转到网络位置并串行打开每个文件，取消保护，调用宏，然后重新保护文档关闭文件并移动到下一个文件，直到它们全部更新。

  Sub Auto_open_change（）
 
 Dim WrkBook As Workbook 
 Dim StrFileName As String 
 Dim FileLocnStr As String 
 Dim LAARNmeWrkbk As String 
 
 PERNmeWrkbk = ThisWorkbook.Name 
 StrFileName =* .xlsx
 FileLocnStr = ThisWorkbook.Path 
 Workbooks.Open（FileLocnStr&\& StrFile名称）
工作簿（StrFileName）.Activate 
 
 With Application.FindFile 
 SearchSubFolders = False 
 LookIn =网络位置
 Filename =*。 xlsm
如果.Execute> 0然后
 Debug.Print有&发现文件找到的文件。 
对于i = 1到PublishFiles.Count 
 WrkBook = Workbooks.Open（文件名：=。FoundFiles（i））
 WrkBook.Worksheets（1）。选择
 ThisWorkbook。工作表（1）.Cells（DestinationRange）= WrkBook.Worksheets（1）.Cells（SourceRange）.Value 
 Next i 
 Else 
 Debug.Print没有找到文件。 
 
结束如果
  

我管理取消保护文件更新并重新保护文件很好，只能从网络位置获取文件。

解决方案

我正在使用Excel 07，它不允许应用程序.FindFile，所以我不能测试这个。但是，我相信这个问题可能是您需要设置变量 Wrkbook ，而不仅仅是分配它。 p>

更改

  WrkBook = Workbooks.Open（Filename：=。FoundFiles i））
  

to

  Set WrkBook = Workbooks.Open（Filename：=。FoundFiles（i））
  

让我知道怎么回事！

Very new to this so please help. Im trying to mass update files in a static folder location, many files in one folder.

What i want to do is

• run VBA macro in Excel 2010 to goto a network location folder,
• open the first file in the folder.
• Unprotect the workbook and worksheets call another marco to run changes
• then protect the worksheet close the file
• and then move onto the next file in the folder until all files have been corrected.

I have created the marco to make the changes, this is called "Edit"

File types are xlsm and the workbook and worksheet are password protected How can i automatically run the macro to goto the network location and in series open each file, unprotect, call the macro, then re protect the document close file and move onto the next file until they are all updated.

Sub Auto_open_change()

Dim WrkBook As Workbook
Dim StrFileName As String
Dim FileLocnStr As String
Dim LAARNmeWrkbk As String

PERNmeWrkbk = ThisWorkbook.Name
StrFileName = "*.xlsx"
FileLocnStr = ThisWorkbook.Path
Workbooks.Open (FileLocnStr & "\" & StrFileName)
Workbooks(StrFileName).Activate

With Application.FindFile
SearchSubFolders = False
LookIn = "Network location"
Filename = "*.xlsm"
If .Execute > 0 Then
    Debug.Print "There were " & .FoundFiles.Count & " file(s) found."
    For i = 1 To .FoundFiles.Count
        WrkBook = Workbooks.Open(Filename:=.FoundFiles(i))
        WrkBook.Worksheets(1).Select
        ThisWorkbook.Worksheets(1).Cells(DestinationRange) = WrkBook.Worksheets(1).Cells(SourceRange).Value
    Next i
Else
    Debug.Print "There were no files found."

End If

Im managing to unprotect the file update and reprotect the file fine, just cant get the file from the network location.

解决方案

I'm using Excel 07, which doesn't allow Application.FindFile, so I can't test this. However, I believe the issue may be that you need to Set the variable Wrkbook, not just assign it.

Change

WrkBook = Workbooks.Open(Filename:=.FoundFiles(i))

to

Set WrkBook = Workbooks.Open(Filename:=.FoundFiles(i))

and let me know how that turns out!

这篇关于VBA宏来批量更新同一位置的多个文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！