用VB如何将Excel里的数据转换到已建好的Aceess数据库中
用VB如何将Excel里的数据转换到已建好的Aceess数据库中 --------------------编程问答-------------------- 表是否已经存在?Excel 的 Sheet 是否已命名? --------------------编程问答-------------------- 恰好最近在写了一个模块,给予参考一下好了。这是转入到已存在的Access中间'选择Excel,取得Excel表名
Private Sub cmdChoose_Click()
cboWorksheets.Clear
On Error GoTo err
With dlgX
.InitDir = App.Path
.CancelError = True
.ShowOpen
End With
txtField.Text = dlgX.FileName
objExcel.Workbooks.Open dlgX.FileName
Set objWorkBook = objExcel.Workbooks(Me.dlgX.FileTitle)
For Each objWorksheet In objWorkBook.Worksheets
cboWorksheets.AddItem objWorksheet.Name
Next
cboWorksheets.ListIndex = 0
objExcel.ActiveWorkbook.Close
objExcel.Quit
Set objExcel = Nothing
err:
Exit Sub
End Sub
'导入已存在的Access表中
Private Sub cmdImport_Click()
Dim intRows As Integer
Dim intCols As Integer
Dim intCnt As Integer
Dim i As Integer
Dim strExcelFileName As String
Dim strWorksheetName As String
Dim strTableName As String
If Me.cboAccess.Text = "" Then
MsgBox "请选择需要转入数据表!", vbInformation, "提示"
Exit Sub
End If
' 取得 Excel 文件名称。
strExcelFileName = txtField.Text
' 取得指定 Worksheet 的名称。
strWorksheetName = cboWorksheets.Text
objExcel.Workbooks.Open strExcelFileName
' 取得工作表名称。
Set objWorksheet = objExcel.Worksheets(strWorksheetName)
With objWorksheet
.Select
intRows = .UsedRange.Rows.Count
intCols = .UsedRange.Columns.Count
End With
'取得要转入的Access表名
strTableName = GetTableName(Me.cboAccess.Text)
If Conn.State <> adStateClosed Then Conn.Close
MakeConn
If Rs.State <> adStateClosed Then Rs.Close
Rs.Open strTableName, Conn, adOpenStatic, adLockOptimistic
'将Excel写入Access
Screen.MousePointer = vbHourglass
For intCnt = 2 To intRows
With Rs
.AddNew
For i = 1 To Rs.Fields.Count - 1
.Fields(i) = objWorksheet.Cells(intCnt, i)
Next
.Update
End With
Next
Screen.MousePointer = vbDefault
MsgBox Me.cboAccess.Text & "导入数据库完毕!", vbOKOnly, "提示"
objExcel.ActiveWorkbook.Close
objExcel.Quit
Set objExcel = Nothing
End Sub
--------------------编程问答-------------------- select sheet1$
insert to access --------------------编程问答-------------------- 谢谢各位啦,小弟先试试 要是还有上面问题的话再麻烦各位~~~~!!! --------------------编程问答-------------------- 我运行的时候提示 GetTableName () 这个函数未定义,能不能把这个函数的定义代码发给我啊?谢谢~~!!!! --------------------编程问答-------------------- 我运行的时候提示 GetTableName () 这个函数未定义,能不能把这个函数的定义代码发给我啊?谢谢~~!!!!
补充:VB , 基础类