Excel VBA工作簿更新 [英] Excel VBA Workbook Update

问题描述

我目前有一个带有宏的工作簿来更新主表上相同范围的范围。现在，我只需使用一个宏来打开主工作簿，从中复制范围，并将值粘贴到另一个工作簿中的相同范围内。

I currently have a workbook with a macro to update a range from an identical range on a master sheet. Right now, I simply use a macro to open the master workbook, copy the range from it and paste the values in the same range in the other workbook.

以下是我目前使用的代码：

Sub GetDataFromClosedWorkbook()
'Created by XXXX 5/2/2014
Application.ScreenUpdating = False ' turn off the screen updating

Dim wb As Workbook
Set wb = Workbooks.Open("LOCATION OF FILE", True, True)
' open the source workbook, read only
With ThisWorkbook.Worksheets("1")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("F8:K25").Value = wb.Worksheets("1").Range("F8:K25").Value
End With

With ThisWorkbook.Worksheets("2")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("V5:Z359").Value = wb.Worksheets("2").Range("V5:Z359").Value
End With

With ThisWorkbook.Worksheets("3")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("V5:AE238").Value = wb.Worksheets("3").Range("V5:AE238").Value
End With

With ThisWorkbook.Worksheets("4")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("V5:AB33").Value = wb.Worksheets("4").Range("V5:AB33").Value
End With

With ThisWorkbook.Worksheets("5")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("V5:Y140").Value = wb.Worksheets("5").Range("V5:Y140").Value
End With

With ThisWorkbook.Worksheets("6")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("V5:AD170").Value = wb.Worksheets("6").Range("V5:AD170").Value
End With

With ThisWorkbook.Worksheets("7")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("V5:AF579").Value = wb.Worksheets("7").Range("V5:AF579").Value
End With

With ThisWorkbook.Worksheets("8")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("Q5:AC182").Value = wb.Worksheets("8").Range("Q5:AC182").Value
End With

With ThisWorkbook.Worksheets("9")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("U5:AK120").Value = wb.Worksheets("9").Range("U5:AK120").Value
End With

With ThisWorkbook.Worksheets("10")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("V5:AC140").Value = wb.Worksheets("10").Range("V5:AC140").Value
End With

With ThisWorkbook.Worksheets("11")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("V5:AG947").Value = wb.Worksheets("11").Range("V5:AG947").Value
End With

With ThisWorkbook.Worksheets("12")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("V5:AB145").Value = wb.Worksheets("12").Range("V5:AB145").Value
End With

With ThisWorkbook.Worksheets("13")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("O5:AE10").Value = wb.Worksheets("13").Range("O5:AE10").Value
End With

With ThisWorkbook.Worksheets("14")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("V5:AA14").Value = wb.Worksheets("14").Range("V5:AA14").Value
End With

With ThisWorkbook.Worksheets("15")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("V5:AF201").Value = wb.Worksheets("15").Range("V5:AF201").Value
End With

With ThisWorkbook.Worksheets("16")
    ' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
    .Range("Q5:AB14").Value = wb.Worksheets("16").Range("Q5:AB14").Value
End With

wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating

End Sub  

如您所见，代码非常简单，并更新了工作簿中16个工作表中每个数据所在的自定义范围。

As you can see, the code is very simple and updates a custom range where data lies in each of the 16 worksheets in the workbook.

我遇到的问题是：在主工作簿，行数可以随着我添加数据而改变。通常情况下，我会通过复制整个工作表来复制整个工作表，从而每次更新时复制整个工作簿，问题是被更新的工作簿是由多个人保存的，他们在工作簿的一列中记录笔记/标记进度。为了帮助画出更好的画面，想象下面的（粗糙）矩阵是一个工作表。每个人的工作表都会得到a，b，c& d更新，但他们进入1，4，2等。我需要1，4，2等保存在它们各自的行，即使更多行a，b，c& d被添加。现在，如果我要添加一行w，x，y& z在主机上的第3行上方，4将成为该行的一部分，i，j，k& l在更新的表上将为空。

The issue I'm running into is this: on the master workbook, the # of rows can change as I add data to it. Normally, I would get around this by just copying the entire worksheet and thus replicating the entire workbook each time it updates, the problem is that the workbooks being updated are saved by multiple people, who take notes/mark progress in a column in their workbooks. To help paint a better picture, imagine the (crude) matrix below is a worksheet. Each person's worksheet is getting a, b, c & d updated, but THEY enter in 1, 4, 2, etc. I need 1, 4, 2 etc to be preserved in each of their respective rows even as more rows of a, b, c & d are added. Right now, if I were to add a row of w, x, y & z above the 3rd row on the master, 4 would then become part of that row and i, j, k & l would be blank on the updated sheet.

a b c 1 d  
e f g   h                                   
i j k 4 l   
m n o   p  
q r s 2 t   
u v w 6 x

对不起，以前没有这样做。你们是对的，链接工作簿是正确的方法 - 问题是这里的服务器安全不会让这种情况发生。因此，我需要从打开的工作簿中复制：（

Sorry for not making this clear earlier. You guys are right, linking workbooks is the right way to do it- the problem is that the server security here won't let that happen. Thus, I need to copy from an open workbook :(

推荐答案

您不需要Macro或VBA来执行此操作。 br>
尝试这样：（注 - 从Excel 2010生成的屏幕截图）

You don't need Macro or VBA to do this.
Try this: (Note - Screen Shots generated from Excel 2010)

1. 从数据选项卡，选择现有连接。
   
   

1. From Data Tab, choose Existing Connections.
   

然后选择浏览更多...

Then choose Browse for more...

然后选择您的Excel文件或您的主工作簿。

Then select your Excel File or your master workbook.

您将被问到如下所示的工作表。

You will be asked what Sheet as seen below.

然后，您将被问及如何反射和目标范围只要按OK。

Then you will be asked how you want it reflected and the destination range. Just press ok.

还有你的主工作簿sheet1（如果你选择sheet1）副本。

要更新其他用户在主工作簿中做的任何事情，只需点击刷新在数据选项卡 现有连接之前的。

And there you have your master workbook sheet1(if you select sheet1) replica.
To update anything that other users did in the master workbook, just click Refresh All in Data Tab beside Existing Connections.

Edit1：您也可以尝试删除重复工作簿中的所有工作表，然后从主工作簿中复制所有工作表。见下文 VBA 代码：

You can also try deleting all the sheets in your duplicate workbook and then copying all worksheets from the Master Workbook. See below VBA code:

Sub Test()
Dim wbM As Workbook, wbR As Workbook, ws As Worksheet
Dim fpath As String
Dim mysheets

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

fpath = "Location of File"
Set wbR = ThisWorkbook
Set wbM = Workbooks.Open(fpath, True, True)
'~~> Create a dummy sheet
'~~> This is needed, Excel won't allow deleting all sheets
wbR.Sheets.Add(wbR.Sheets(1)).Name = "Temp"

'~~> Delete all sheets except the dummy
For Each ws In wbR.Worksheets
    If ws.Name <> "Temp" Then ws.Delete
Next

'~~> Generate the array of sheet names
For Each ws In wbM.Worksheets
    If IsEmpty(mysheets) Then
        mysheets = Array(ws.Name)
    ElseIf IsArray(mysheets) Then
        ReDim Preserve mysheets(UBound(mysheets) + 1)
        mysheets(UBound(mysheets)) = ws.Name
    End If
Next

'~~> Copy the sheets from Master workbook
wbM.Sheets(mysheets).Copy after:=wbR.Sheets(1)
'~~> Clean up, delete the dummy sheet
wbR.Sheets("Temp").Delete
wbM.Close False

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

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