How to use VB to realize import and export each other between the Access and Excel-CodePudding

because of my job, you need to do a lot of ACCESS and Excel conversion between, import ACCESS again after the modification of Excel, and then use the ACCESS to generate CAD figure, a lot of work, is there anyone great god help, give a solution, how to realize the rapid and transformation between the ACCESS and Excel, Excel file name and ACCESS the table name to inside, I don't know anything about programming, the great god, please help!

CodePudding user response:

External database to the Excel as a Jet Engine operation, all operations in SQL statements,

CodePudding user response:

reference 1st floor of123 response:

the Excel as a Jet Engine to manipulate the external database, all operations in SQL statements,

Specific how to do, or code? I am small white one,

CodePudding user response:

The Internet search, a lot of connection string sample and SQL statements example,

CodePudding user response:

Private Sub Excel2MDB (ExcelPath As String, SheetNm As String, AccessPath As String, AccessTable As String)
Conn. CursorLocation=adUseClient
If Conn. State=adStateOpen Then Conn. Close
Conn. Open the Provider="Microsoft. Jet. The OLEDB. 4.0; Persist Security Info=False; Data Source="& amp; ExcelPath & amp; "; Extended Properties='Excel 8.0; HDR=Yes; IMEX=1 '"
Call Conn. Execute (" Delete From [the database="& amp; AccessPath & amp;"]. "& amp; AccessTable) 'table has been in existence must clean the data in table
Call Conn. Execute (" Insert into [the database="& amp; AccessPath & amp;"]. "& amp; AccessTable & amp; "Select * FROM [" & amp; SheetNm & amp;" $] ")
MsgBox "data import MDB success!" VbInformation, "Excel import MDB table
"End Sub

Public Sub MDB2Excel (MdbNm As String, MdbTable As String, ExcelNm As String)
On the Error Resume Next
Set acApp=GetObject (MdbNm, Access. "Application")
AcApp. DoCmd. OutputTo acOutputTable MdbTable, "Microsoft Excel (*. XLS)", ExcelNm
AcApp. CloseCurrentDatabase
The Set of acApp=Nothing
End Sub

CodePudding user response:

reference 4 floor cbm666 response:

Private Sub Excel2MDB (ExcelPath As String, SheetNm As String, AccessPath As String, AccessTable As String)
Conn. CursorLocation=adUseClient
If Conn. State=adStateOpen Then Conn. Close
Conn. Open the Provider="Microsoft. Jet. The OLEDB. 4.0; Persist Security Info=False; Data Source="& amp; ExcelPath & amp; "; Extended Properties='Excel 8.0; HDR=Yes; IMEX=1 '"
Call Conn. Execute (" Delete From [the database="& amp; AccessPath & amp;"]. "& amp; AccessTable) 'table has been in existence must clean the data in table
Call Conn. Execute (" Insert into [the database="& amp; AccessPath & amp;"]. "& amp; AccessTable & amp; "Select * FROM [" & amp; SheetNm & amp;" $] ")
MsgBox "data import MDB success!" VbInformation, "Excel import MDB table
"End Sub

Public Sub MDB2Excel (MdbNm As String, MdbTable As String, ExcelNm As String)
On the Error Resume Next
Set acApp=GetObject (MdbNm, Access. "Application")
AcApp. DoCmd. OutputTo acOutputTable MdbTable, "Microsoft Excel (*. XLS)", ExcelNm
AcApp. CloseCurrentDatabase
The Set of acApp=Nothing
End Sub

Positive solution!
Use access engine directly import and export excel is the fastest. Faster than SQL or other means at least one order of magnitude.

CodePudding user response:

reference 4 floor cbm666 response:

Private Sub Excel2MDB (ExcelPath As String, SheetNm As String, AccessPath As String, AccessTable As String)
Conn. CursorLocation=adUseClient
If Conn. State=adStateOpen Then Conn. Close
Conn. Open the Provider="Microsoft. Jet. The OLEDB. 4.0; Persist Security Info=False; Data Source="& amp; ExcelPath & amp; "; Extended Properties='Excel 8.0; HDR=Yes; IMEX=1 '"
Call Conn. Execute (" Delete From [the database="& amp; AccessPath & amp;"]. "& amp; AccessTable) 'table has been in existence must clean the data in table
Call Conn. Execute (" Insert into [the database="& amp; AccessPath & amp;"]. "& amp; AccessTable & amp; "Select * FROM [" & amp; SheetNm & amp;" $] ")
MsgBox "data import MDB success!" VbInformation, "Excel import MDB table
"End Sub

Public Sub MDB2Excel (MdbNm As String, MdbTable As String, ExcelNm As String)
On the Error Resume Next
Set acApp=GetObject (MdbNm, Access. "Application")
AcApp. DoCmd. OutputTo acOutputTable MdbTable, "Microsoft Excel (*. XLS)", ExcelNm
AcApp. CloseCurrentDatabase
The Set of acApp=Nothing
End Sub

Hello, could you tell me how to use these code? I am small white one, can you give a detailed operation method, be obliged!

CodePudding user response:

Hello, could you tell me how to use these code? I am small white one, can you give a detailed operation method, be obliged! [/quotewa]
Completely don't have to modify a little code basically no, unless specially do for you, but your specific needs and not clear, so you have to code according to the great god give it ourselves

CodePudding user response:

Excel2MDB function can be directly copied and calls, but want to be in your project, ADO object reference is added to the

For example, choose Project in VB - References... , found in the pop-up window and check the Microsoft ActiveX Data Objects (2) x Library, sure,

In engineering form paste Excel2MDB function code,

Call, ExcelPath for XLS file path and file name, use single quotes, SheetNm for the name of the table, such as' $Sheet1, AccessPath for MDB file path and file name, AccessTable for Access database table name,

CodePudding user response:

Note that the function of importing new data, completely remove the original data, this is in order to apply to different fields of the structure of the table, if you have a table structure is the same, can delete statements note, sustainable import new data,