将Excel 2003工作表保存到Access 2003表中 [英] Save Excel 2003 worksheet into an Access 2003 table

问题描述

我正在尝试编写宏，以将单个工作表中的一行从Excel 2003工作簿导出到Access 2003表中的新行.我是VBA的新手，我在网上找到的所有内容都指向另一种方式-从Access到Excel.我希望这是一个导出，而不是链接，并且我不在乎在导出后保持它们的同步.

I'm trying to write a macro to export a single row from a single worksheet from an Excel 2003 workbook to a new row in an Access 2003 table. I'm new to VBA, and everything I've found on the web refers to going the other way -- from Access to Excel. I want this to be an export, not a link, and I don't care about keeping them synced after the export.

推荐答案

此示例代码假设了一些事情:

This sample code assumes a few things:

1. 您要插入两个值，分别位于A2和B2中.
2. 两个值都是字符串

3. 您有一个名为TestTable的表，其中包含两列

1. You are inserting two values, which are found in A2 and B2.
2. The values are both strings

3. You have a table called TestTable, containing two columns

Public Sub ExportRecord()

On Error GoTo ErrRoutine


Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset


conn.Open "Your_DSN_Goes_Here", "userId", "pwd"


Set rs = New ADODB.Recordset
rs.Open "TestTable", conn, adOpenDynamic, adLockOptimistic, adCmdTable


rs.AddNew


rs.Fields("Col1") = CStr(Sheet1.Cells(2, 1))
rs.Fields("Col2") = CStr(Sheet1.Cells(2, 2))


rs.Update


Set rs = Nothing
Set conn = Nothing


Exit Sub

错误例程:

MsgBox Err.Description

结束子

End Sub

这篇关于将Excel 2003工作表保存到Access 2003表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！