vba 将用户表单数据提交到另一个工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23648128/
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
Submit User Form Data to another workbook
提问by cazzzac
I have created a User Form in VBA so that call centre staff can submit their numbers to our tracking spreadsheet at the end of each day. In its current design, the form successfully submits data to another sheet in the workbook. As I discovered, macro-enabled spreadsheets can't be shared (each staff member will submit at roughly 4pm), so I am looking at making a copy of the user form spreadsheet for each staff member (around 15) and directing it to submit to a shared spreadsheet every day.
我在 VBA 中创建了一个用户表单，以便呼叫中心工作人员可以在每天结束时将他们的号码提交到我们的跟踪电子表格中。在其当前设计中，表单成功地将数据提交到工作簿中的另一个工作表。正如我发现的那样，无法共享启用宏的电子表格（每个工作人员将在大约下午 4 点提交），所以我正在考虑为每个工作人员（大约 15 名）制作一份用户表单电子表格的副本并指示它提交每天共享一个电子表格。
i.e. 15 or so staff members use "User Form.xlsm" to submit to "Tracking Spreadsheet.xlsx" all around 4pm each day.
即每天下午 4 点左右，大约有 15 名员工使用“User Form.xlsm”提交到“Tracking Spreadsheet.xlsx”。
Q1: Do I need to make the "Tracking Spreadsheet.xlsx" a Shared workbook in case more than one staff member submits their end of day form at once? Q2: Do I need to insert VBA code in "User Form.xlsm" that actively opens "Tracking Spreadhseet.xlsx" or can I just reference "Tracking Spreadhseet.xlsx"?
问题 1：我是否需要将“Tracking Spreadsheet.xlsx”设为共享工作簿，以防有多名员工同时提交他们的一天结束表格？Q2：我需要在主动打开“Tracking Spreadhseet.xlsx”的“User Form.xlsm”中插入VBA代码还是我可以只引用“Tracking Spreadhseet.xlsx”？
Q3: Where have I gone wrong in the code below? I'm new to VBA. I have structured my code for the submission button as follows, but it just adds data to the Daily_Tracking_Dataset sheet in the current workbook, rather than the new one: First, I tried to change the workbook, then i make the relevant sheet in the workbook active, then I determine the first empty row, then I transfer the informationfrom the form's textboxes to the new workbook.
Q3：我在下面的代码中哪里出错了？我是 VBA 的新手。我已将提交按钮的代码结构如下，但它只是将数据添加到当前工作簿中的 Daily_Tracking_Dataset 工作表，而不是新工作簿：首先，我尝试更改工作簿，然后在工作簿中制作相关工作表active，然后我确定第一个空行，然后我将信息从表单的文本框传输到新工作簿。
Private Sub Button_Submit_Click()
'Change Workbook
Dim nwb As Workbook
Set nwb = Workbooks.Open("G:\Tracking Spreadsheet.xlsx")
Dim emptyRow As Long
'Make Daily_Tracking_Dataset active
Daily_Tracking_Dataset.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer Information
Cells(emptyRow, 1).Value = TextBox1.Value
Cells(emptyRow, 2).Value = lstName.Value
Cells(emptyRow, 3).Value = txtROIT.Value
Cells(emptyRow, 4).Value = txtROISub.Value
Cells(emptyRow, 5).Value = txtRefsT.Value
Cells(emptyRow, 6).Value = txtRefsC.Value
Cells(emptyRow, 7).Value = txtRefsSub.Value
Cells(emptyRow, 8).Value = txtReSubT.Value
Cells(emptyRow, 9).Value = txtReSubSub.Value
End Sub
采纳答案by cazzzac
Thanks for the help. I ended up using the emptyrow method below: 'Begin Transfer Information and Change Workbook Dim nwb As Workbook Set nwb = Workbooks.Open("G:\Time To Complete Dataset.xlsx")
谢谢您的帮助。我最终使用了下面的 emptyrow 方法：'Begin Transfer Information and Change Workbook Dim nwb As Workbook Set nwb = Workbooks.Open("G:\Time To Complete Dataset.xlsx")
'Determine emptyRow
Dim emptyRow As Long
emptyRow = WorksheetFunction.CountA(nwb.Sheets("daily_tracking_dataset").Range("A:A")) + 1
'Transfer Information
With nwb.Sheets("daily_tracking_dataset")
'Datebox
.Cells(emptyRow, 1).Value = CDate(txtDate.Text)
'Listbox
.Cells(emptyRow, 2).Value = lbName.List(lbName.ListIndex)
'Textbox
.Cells(emptyRow, 3).Value = txtROT.Value
End With
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
回答by JOSE
Try this sample below:
试试下面的这个示例：
Private Sub TextBox1_afterupdate()
Dim pro As Workbook
Set pro = Workbooks.Open("F:\DOCUMENTS\Proration.xlsm")
Workbooks("proration").Sheets("sheet1").Range("i20").End(xlUp).Offset(1, 0).Value = UserForm1.TextBox1.Value
pro.Save
pro.Close True
End Sub
回答by MP24
Regarding Q1/Q2: yes, you will need to add code to open the worksheet, and it may be better to open it Shared, at least if you do not save and close the tracking spreadsheet file immediately after inserting the data.
关于 Q1/Q2：是的，您将需要添加代码来打开工作表，并且最好打开它共享，至少如果您在插入数据后不立即保存并关闭跟踪电子表格文件。
Did you try an Access database or something similar, where you can more easily add the required information and do not need to worry about concurrent accesses to the data "sheet"?
您是否尝试过 Access 数据库或类似的东西，在那里您可以更轻松地添加所需的信息，而无需担心对数据“表”的并发访问？
Regarding Q3: you did not state what is going wrong with your code at the moment.
关于 Q3：您目前没有说明您的代码出了什么问题。
Edit:Regarding Q3: Try using something like nwb.Sheets( "daily_tracking_dataset" ).Cells(emptyRow, 1).Value = TextBox1.Valueand be aware that emptyRowalso needs to be determined using nwb, e.g. using a combination of Offsetand Move(xlDown)(see Excel: Move selection to next blank row in specific column, and enumerate selection by date and type).
编辑：关于Q3：尝试使用类似nwb.Sheets( "daily_tracking_dataset" ).Cells(emptyRow, 1).Value = TextBox1.Value并意识到，emptyRow也需要使用来确定nwb例如使用的组合，Offset和Move(xlDown)（见的Excel：按照日期和类型选择移到下一个空白行中特定的列，并列举选择）。
