目录
1.创建工程(文件->新建工程->标准EXE)
2.在Form1中添加部件
3.在组件界面双击进入代码界面
'--------------------
'文件选择目录 联动设定
'--------------------
' 联动Flg
Private Init_Flg As String '初期化时 各列表框不联动(0:初期化,1:非初期化)
'画面初期化
Private Sub Form_Load()
'初期化开始Flg
Init_Flg = "0"
'磁盘路径
Drive_Path = "D:"
'文件夹路径
Dir_Path = "D:"
'磁盘
Drive1.Drive = Drive_Path
'文件夹
Dir1.Path = Dir_Path
'文件
File1.Path = Dir_Path
'初期化结束Flg
Init_Flg = "1"
End Sub
'磁盘列表 选择变更
Private Sub Drive1_Change()
'非初期化时 变更的场合 联动实施
If Init_Flg = "1" Then
Drive_Path = Drive1.Drive
'文件夹列表 联动
Dir1.Path = Drive1.Drive
End If
End Sub
'文件夹列表 选择变更
Private Sub Dir1_Change()
'非初期化时 变更的场合 联动实施
If Init_Flg = "1" Then
Dir_Path = Dir1.Path
'文件列表 联动
File1.Path = Dir1.Path
End If
End Sub
'文件列表 双击
Private Sub File1_DblClick()
Call Return_File_Path
End Sub
'返回文件路径 并关闭窗口
Private Sub Return_File_Path()
'取得的文件路径 设定
Select_File_Path = Dir1.Path
If Right(Dir1.Path, 1) <> "\" Then
Select_File_Path = Select_File_Path & "\"
End If
Select_File_Path = Select_File_Path & File1.FileName
Text1.Text = Select_File_Path
End Sub
Private Sub Command1_Click()
Dim xlApp As Object
Dim xlBook As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("E:\a华天\MyExcelFile.xls")
Dim xlSheet As Object
Set xlSheet = xlBook.Worksheets("Sheet1")
Dim conn As New ADODB.Connection
conn.Open "Provider=SQLOLEDB;Data Source=数据库IP,端口;Initial Catalog=数据库名;User ID=用户名;Password=密码;"
Dim i As Integer
For i = 2 To xlSheet.UsedRange.Rows.Count
strSQL = "INSERT INTO sys_test (col1,col2,col3) VALUES ('" & xlSheet.Cells(i, 1).Value & "', '" & xlSheet.Cells(i, 2).Value & "', '" & xlSheet.Cells(i, 3).Value & "')"
conn.Execute strSQL
Next i
conn.Close
xlBook.Close False
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
MsgBox "导入成功"
End Sub
4.添加模块
选中工程->单击右键->添加->添加模块->打开
代码
'磁盘路径 全局变量
Global Drive_Path As String
'文件夹路径 全局变量
Global Dir_Path As String
'取得Excel文件路径
Global Select_File_Path As String
5.添加引用(工程->引用)
添加Microsoft Activek Data Objects *.* Library
6.运行并在数据库查看是否导入成功
7.打包成exe
文件->生成exe工程