有没有一种方法可以在中央数据库中编译/保存多个用户使用的excel应用程序中的数据

Question

我正在开发一个使用MS Excel和VBA处理数据并可视化结果的应用程序。此应用程序将由多个用户发布和使用。excel文件不是共享工作簿，相反，用户可以使用自己的数据独立使用应用程序。

我的问题是：我如何将每个用户处理的数据编译并保存到中央数据库中，以便用于以后的分析，例如一个特定用户使用应用程序处理了多少记录，或者计算所有用户处理的所有数据的特定特征的统计摘要。

我试着写一个VBA，这样当用户点击"；保存"；在Excel中，它会将数据转储到保存在共享驱动器中的MS Access中，只有当用户连接到公司VPN时才能访问该驱动器。因此，问题是，如果没有连接VPN，数据就不会转储到数据库中。此外，该应用程序还将发布给无法访问本地共享驱动器的离岸团队，因此我如何从他们那里获取数据？

有没有更好的方法用VBA或Python来完成这项工作？谢谢

Answer 1

当然可以。您可以将数据从Excel导出到MS Access，但正如您所经历和描述的，这不是一个好的选择。您有权访问SQL Server吗？如果是这样，这里有两个选项供您考虑。

Sub UpdateTable()
Dim cnn As Object
Dim wbkOpen As Workbook
Dim objfl As Variant
Dim rngName As Range
Workbooks.Open "C:you_path_hereexcelfile.xls"
Set wbkOpen = ActiveWorkbook
Sheets("Sheet1").Select
Set rngName = Range(Range("A1"), Range("A1").End(xlToLeft).End(xlDown))
rngName.Name = "TempRange"
strFileName = wbkOpen.FullName
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"
nSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=your_server_name;Database=[Northwind].[dbo].[TBL]]"
nJOIN = " SELECT * from [TempRange]"
cnn.Execute nSQL & nJOIN
MsgBox "Uploaded Successfully"
wbkOpen.Close
Set wbkOpen = Nothing

End Sub
Sub InsertInto()
'Declare some variables
Dim cnn As adodb.Connection
Dim cmd As adodb.Command
Dim strSQL As String
'Create a new Connection object
Set cnn = New adodb.Connection
'Set the connection string
cnn.ConnectionString = "your_server_name;Database=your_db_name;Trusted_Connection=True;"
'Create a new Command object
Set cmd = New adodb.Command
'Open the connection
cnn.Open
'Associate the command with the connection
cmd.ActiveConnection = cnn
'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText
'Create the SQL
strSQL = "UPDATE TBL SET some_date = 2021-01-01 WHERE employeeID = 1"
'Pass the SQL to the Command object
cmd.CommandText = strSQL
'Open the Connection to the database
cnn.Open
'Execute the bit of SQL to update the database
cmd.Execute
'Close the connection again
cnn.Close
'Remove the objects
Set cmd = Nothing
Set cnn = Nothing
End Sub