EXCEL 将数据保存到不同的工作簿 - VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23132044/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EXCEL Save data into different workbook - VBA
提问by nadz
Good day,
再会，
I have a sheet that requests a user to enter data and then click a button to save the data into the database. The database is currently located in the same workbook "Different worksheet".
我有一张表格，要求用户输入数据，然后单击按钮将数据保存到数据库中。数据库当前位于同一工作簿“不同的工作表”中。
What i basically need is for this data to be saved on a different worksheet that i call "Consolidated.xlsx" which is available in a different folder "C:/reports/consolidates.xlsx" so that only I can access this data and no one else
我基本上需要的是将此数据保存在我称之为“Consolidated.xlsx”的不同工作表上，该工作表位于不同的文件夹“C:/reports/consolidates.xlsx”中，以便只有我可以访问这些数据，而没有另一个
Please let me know if you can help
如果你能帮忙，请告诉我
Document currently available under the following link: www.dropbox.com/s/3wea245lmek8hef/FormSheet.xls
当前可通过以下链接获取文档：www.dropbox.com/s/3wea245lmek8hef/FormSheet.xls
Thanks
谢谢
回答by Dan Wagner
EDIT 4/19: the code below has been updated to write to the local "PartsData" sheet as well as the consolidated target... You still need to make sure there is a sheet on your "consolidated" file that is named "PartsData":
编辑 4/19：下面的代码已更新为写入本地“PartsData”表以及合并目标......您仍然需要确保“合并”文件中有一个名为“PartsData”的表”：
Option Explicit
Sub UpdateLogWorksheet()
Dim historyWks As Worksheet, localWks As Worksheet, _
inputWks As Worksheet, indexWks As Worksheet
Dim historyWb As Workbook
Dim MyWorksheets As New Collection
Dim nextRow As Long, oCol As Long
Dim myRng As Range, myCell As Range
Dim myCopy As String
'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D7,D9,D11,D13"
'assign variables for easy reference
Set inputWks = ThisWorkbook.Worksheets("Input")
Set localWks = ThisWorkbook.Worksheets("PartsData")
Set historyWb = Workbooks.Open("C:\reports\consolidated.xlsx")
Set historyWks = historyWb.Worksheets("PartsData")
'put both target worksheets into a collection for an easy loop
MyWorksheets.Add Item:=localWks
MyWorksheets.Add Item:=historyWks
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
Set myRng = .Range(myCopy)
If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
'write results of form to both local parts data and consolidated parts data
For Each indexWks In MyWorksheets
With indexWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
indexWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
Next indexWks
historyWb.Save '<~ save and close the target workbook
historyWb.Close SaveChanges:=False
'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End Sub
