用于从外部工作表更新工作簿的 Excel vba 代码

Excel vba code for updating workbook from external worksheet

提问by halcyonics

Yet another worksheet copying problem! This is a simple problem that has got me stumped. I want the click of a command button (in action.xlsm) to repopulate the values in a range ("stock" - 2 cols & maybe 100 rows - this is the master inventory records) in a separate excel file (inventory.xlsx), from a named range ("newInventory" - same size as other named range) in the active worksheet (in action.xlsm) that has had the original "stock" values reduced by the values of items taken out of stock. The calculations are OK I just can't get the master inventory file to update. I have checked heaps of forums and tried two approaches to no avail. I have tried:

另一个工作表复制问题！这是一个让我难倒的简单问题。我希望单击命令按钮（在 action.xlsm 中）在单独的 excel 文件 (inventory.xlsx) 中重新填充范围内的值（“库存” - 2 列 & 可能 100 行 - 这是主库存记录） ，来自活动工作表（在 action.xlsm 中）中的命名范围（“newInventory” - 与其他命名范围相同的大小），该范围的原始“库存”值已减去缺货商品的值。计算没问题，我只是无法更新主库存文件。我检查了大量论坛并尝试了两种方法都无济于事。我试过了：

Private Sub CommandButton1_Click()
Dim InventoryFileName As String
InventoryFileName = "C:\Users\david\Documents\inventory.xlsx"
Workbooks(InventoryFileName).Worksheets("Sheet1").Range("stock") = ThisWorkbook.Worksheets("inventory").Range("newInventory").Value
Workbooks(InventoryFileName).Save
End Sub 

Throws a "Run-time error '9': Subscript out of range" on line 4. I have also tried:

在第 4 行抛出“运行时错误 '9'：下标超出范围”。我也试过：

Private Sub CommandButton1_Click()
Dim wbTarget As Workbook 'workbook where the data is to be pasted
Dim wsTarget As Worksheet
Dim wbThis   As Workbook 'workbook from where the data is to copied
Dim wsThis As Worksheet
Dim strName  As String   'name of the source sheet/ target workbook

'set to the current active workbook (the source book)
Set wbThis = ActiveWorkbook
Set wsThis = ActiveSheet

'get the active sheetname of the book
strName = wsThis.Name

'open a workbook that has same name as the sheet name
Set wbTarget = Workbooks.Open("C:\Users\david\Documents\" & strName & ".xlsx")
Set wsTarget = wbTarget.Worksheets("Sheet1")

'select cell A1 on the target book
wbTarget.wsTarget.Range("A1").Select

'clear existing values form target book
wbTarget.wsTarget.Range("A1:B10").ClearContents

'activate the source book
wbThis.Activate

'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False

'copy the range from source book
wbThis.wsThis.Range("A1:B10").Copy

'paste the data on the target book
wbTarget.wsTarget.Range("A1").PasteSpecial Paste:=xlPasteValues

'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False

'save the target book
wbTarget.Save

'close the workbook
wbTarget.Close

'activate the source book again
wbThis.Activate

'clear memory
Set wbTarget = Nothing
Set wbThis = Nothing

End Sub

This throws a "Run-time error '438': Object doesn't support this property or method" on line wbTarget.wsTarget.Range("A1").Select

这会在线抛出“运行时错误‘438’：对象不支持此属性或方法” wbTarget.wsTarget.Range("A1").Select

What have I got wrong? Any suggestions?

我做错了什么？有什么建议？

采纳答案by Floris

Replace

代替

wbTarget.wsTarget.Range("A1").Select

wbTarget.wsTarget.Range("A1").Select

with just

只是

wsTarget.Range("A1").Select

wsTarget.Range("A1").Select

The workbook is already implied from the way you defined wsTarget. I suspect that will do it. If you run the code in the debugger, then when you do a "watch" on the variable you can see exactly what does and doesn't work..

您定义的方式已经暗示了工作簿wsTarget。我怀疑这会做到。如果您在调试器中运行代码，那么当您对变量进行“观察”时，您可以准确地看到什么起作用，什么不起作用。

回答by artis_meditari

Firstly you have 2 commandbutton1. Secondly you must have a reference for a Range like:

首先你有2个commandbutton1。其次，您必须有一个范围的参考，例如：

Workbooks(InventoryFileName).Worksheets("Sheet1").Range("A3:B21") = ThisWorkbook.Worksheets("inventory").Range("A10:B12").Value

or

或者

stock="A3:B21"

newInventory="A10:B12"