用vba编写一个程序,程序名字叫做“数据转换”,要求生成两个按钮,一个为“选取文件”,用于选取任意路径下的excel表格文件,将选择的文件数据导出到access,并保存相同名称的mdb文件到相同的目录;另一个为“退出”,用于退出程序
时间: 2023-12-04 17:05:04 浏览: 109
VBA数据转换
以下是 VBA 代码,可以实现你所需的功能:
```
Private Sub CommandButton1_Click()
Dim excelFilePath As String
Dim accessFilePath As String
Dim accessFileName As String
Dim accessDB As DAO.Database
Dim excelApp As Excel.Application
Dim excelWorkbook As Excel.Workbook
Dim excelWorksheet As Excel.Worksheet
Dim excelRange As Excel.Range
Dim accessTable As DAO.TableDef
Dim accessField As DAO.Field
Dim accessRecordset As DAO.Recordset
Dim i As Long, j As Long
'选取 Excel 文件
excelFilePath = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
If excelFilePath = "False" Then
MsgBox "请选择一个 Excel 文件!", vbExclamation, "数据转换"
Exit Sub
End If
'打开 Excel 文件
Set excelApp = New Excel.Application
Set excelWorkbook = excelApp.Workbooks.Open(excelFilePath)
Set excelWorksheet = excelWorkbook.Sheets(1)
Set excelRange = excelWorksheet.UsedRange
'生成 Access 文件路径和文件名
accessFilePath = Left(excelFilePath, InStrRev(excelFilePath, "\"))
accessFileName = Replace(excelWorkbook.Name, ".xlsx", ".mdb")
'创建 Access 数据库
Set accessDB = DBEngine.CreateDatabase(accessFilePath & accessFileName, dbLangGeneral)
'创建 Access 表格
Set accessTable = accessDB.CreateTableDef("Sheet1")
For j = 1 To excelRange.Columns.Count
Set accessField = accessTable.CreateField(excelRange.Cells(1, j).Value, dbText)
accessTable.Fields.Append accessField
Next j
accessDB.TableDefs.Append accessTable
'导入 Excel 数据到 Access 表格
Set accessRecordset = accessDB.OpenRecordset("Sheet1")
For i = 2 To excelRange.Rows.Count
accessRecordset.AddNew
For j = 1 To excelRange.Columns.Count
accessRecordset.Fields(j - 1).Value = excelRange.Cells(i, j).Value
Next j
accessRecordset.Update
Next i
'关闭对象
accessRecordset.Close
Set accessRecordset = Nothing
Set accessTable = Nothing
accessDB.Close
Set accessDB = Nothing
excelWorkbook.Close
excelApp.Quit
Set excelRange = Nothing
Set excelWorksheet = Nothing
Set excelWorkbook = Nothing
Set excelApp = Nothing
'提示保存成功
MsgBox "数据已经成功导出到 " & accessFilePath & accessFileName & "!", vbInformation, "数据转换"
End Sub
Private Sub CommandButton2_Click()
'退出程序
Unload Me
End Sub
```
使用方法:
1. 在 Excel 中,按下 Alt + F11 打开 VBA 编辑器;
2. 在 VBA 编辑器中,点击菜单栏的“插入” -> “用户窗体” -> “用户窗体”;
3. 在窗体设计器中,添加两个命令按钮控件,并分别设置 Caption 和名称 为“选取文件”和“退出”;
4. 双击“选取文件”按钮,进入代码编辑器,并将上面的 CommandButton1_Click 代码复制粘贴到其中;
5. 双击“退出”按钮,进入代码编辑器,并将上面的 CommandButton2_Click 代码复制粘贴到其中;
6. 保存窗体,回到 Excel 工作表,右键单击窗体,选择“查看代码”,将以下代码复制粘贴到窗体模块中:
```
Private Sub UserForm_Initialize()
'设置窗体标题和大小
Me.Caption = "数据转换"
Me.Width = 280
Me.Height = 100
End Sub
```
7. 按下 F5 执行程序,即可看到生成的窗体,并可以通过“选取文件”按钮选择 Excel 文件并进行数据转换。
阅读全文