我在excel中使用宏来保存访问数据库中的数据 [英] I am using a macro in excel to save data in access database

问题描述

我在excel中使用宏来保存访问数据库中的数据，excel（名为DMI-Form-505-ToExcel.xlsm）并且它可以轻松保存在访问数据库中（例如C：/ Me / Project / DMI-Form- 505-ToExcel.accdb）。但是，我想把这个给别人，它不会在他们的计算机上工作，因为他们将有不同的路径保存在那里系统等。我怎样才能让宏在其他系统（全局路径）中工作？ />




这是我的代码： -

I am using a macro in excel to save data in access database,excel(named DMI-Form-505-ToExcel.xlsm) and it easily save in access datbase(e.g. C:/Me/Project/DMI-Form-505-ToExcel.accdb). However, I want to give this to someone else and it will not work on their computer as they will have different path which is save in there system etc. How can I get the macro to work in other system (global path)?


this is my code:-

Sub Mail_workbook_Outlook_2()
Dim newcon As ADODB.Connection
Set newcon = New ADODB.Connection
Dim Recordset As ADODB.Recordset

Set Recordset = New ADODB.Recordset
newcon.Open "provider=microsoft.ace.oledb.12.0;Data source= C:\Users\Reva 006\Desktop\New folder (3)\DMI-Form-505-ToAccess.accdb"
Recordset.Open "Sheet1", newcon, adOpenDynamic, adLockOptimistic


Recordset.AddNew

Recordset.Fields(0).Value = Range("c9").Value 'name
Recordset.Fields(1).Value = Range("C10").Value 'department
Recordset.Fields(2).Value = Range("C11").Value 'reason of request
Recordset.Fields(3).Value = Range("I9").Value 'ext
Recordset.Fields(4).Value = Range("I10").Value 'date
Recordset.Fields(5).Value = Range("C15").Value 'status
Recordset.Fields(6).Value = Range("A15").Value 'rev
Recordset.Fields(7).Value = Range("B15").Value ' document
Recordset.Fields(8).Value = Range("I15").Value 'Additional information
Recordset.Fields(9).Value = Range("F15").Value 'project
Recordset.Fields(10).Value = Range("G15").Value 'EWo
Recordset.Fields(11).Value = Range("H15").Value 'Autocad


Recordset.Update

Recordset.Close

End Sub

推荐答案

如果我理解你的话......



您需要做的就是将路径传递给ms Access数据库作为参数：

If i understand you well...

All what you need to do is to pass path to ms Access database as an argument:

Sub Mail_workbook_Outlook_2(ByVal sDatabaseFile As String)
'
newcon.Open "provider=microsoft.ace.oledb.12.0;Data source= " & sDatabaseFile

'
End Sub

用法：

Usage:

Sub Whatever
Dim vFileName As String
    
vFileName = Application.GetOpenFileName("Access database files (*.accdb), *.accdb")
If CStr(vFile) = CStr(False) Then Exit Sub

Mail_workbook_Outlook_2 CStr vFileName

End Sub

如需了解更多信息，请参阅：

子程序 [ ^ ]

将参数传递给程序 [ ^ ]

调用子程序和函数程序 [ ^ ]

Application.GetOpenFilename Method（Excel） [ ^ ]

以编程方式选择Excel for Windows for Excel和Excel for Mac [ ^ ]

For further information, please see:
Sub Procedures[^]
Passing Arguments to Procedures[^]
Calling Sub and Function Procedures[^]
Application.GetOpenFilename Method (Excel)[^]
Programmatically Selecting Files in Excel for Windows and Excel for the Mac[^]

这篇关于我在excel中使用宏来保存访问数据库中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！