VB导入Excel到数据库软件（持续更新中。）
1.选择Excel文件版本
电脑上用的 Ｏffice2010
引用：Mircosoft Excel 14.0 Object Library
2.选择Excel文件
1 '选择文件公共变量 2 Public Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long 3 Public Type OPENFILENAME 4 lStructSize As Long 5 hwndOwner As Long 6 hInstance As Long 7 lpstrFilter As String 8 lpstrCustomFilter As String 9 nMaxCustFilter As Long 10 nFilterIndex As Long 11 lpstrFile As String 12 nMaxFile As Long 13 lpstrFileTitle As String 14 nMaxFileTitle As Long 15 lpstrInitialDir As String 16 lpstrTitle As String 17 flags As Long 18 nFileOffset As Integer 19 nFileExtension As Integer 20 lpstrDefExt As String 21 lCustData As Long 22 lpfnHook As Long 23 lpTemplateName As String 24 End Type 25 '---------------------------------- 26 '选择文件函数 27 Public Function selectFile() As String 28 Dim OFName As OPENFILENAME 29 OFName.lStructSize = Len(OFName) 30 'OFName.hwndOwner = Me.hWnd 31 OFName.hInstance = App.hInstance 32 OFName.lpstrFilter = "Excel文件xls(*.xls)" & Chr$(0) & "*.xls" & Chr$(0) & "所有文件(*.*)" & Chr$(0) & "*.*" & Chr$(0) 33 OFName.lpstrFile = Space(254) 34 OFName.nMaxFile = 255 35 OFName.lpstrFileTitle = Space(254) 36 OFName.nMaxFileTitle = 255 37 OFName.lpstrInitialDir = App.path '起始目录 38 OFName.lpstrTitle = "打开文件" '标题 39 OFName.flags = 6148 40 If GetOpenFileName(OFName) >= 1 Then 41 selectFile = OFName.lpstrFile 42 Else 43 selectFile = "未选择文件" 44 End If 45 End Function
1 Private Sub Command1_Click() 2 '选择文件 3 Text1.Text = selectFile 4 End Sub
3.遍历Excel工作表
1 Private Sub Command2_Click() 2 '遍历Excel工作表 3 List1.Clear 4 Dim excelFile As String 5 excelFile = Text1.Text 6 If excelFile = "" Or excelFile = "未选择文件" Then Exit Sub 7 '定义Excel对象 8 Dim XlsObj As Excel.Application 9 Dim XlsBook As Excel.Workbook 10 Dim XlsSheet As Excel.Worksheet 11 12 '打开Excel文件 13 Set XlsObj = New Excel.Application 14 XlsObj.Visible = False 15 16 Set XlsBook = XlsObj.Workbooks.Open(excelFile) 17 For i = 1 To XlsBook.Worksheets.Count 18 '遍历Excel表名 19 List1.AddItem XlsBook.Sheets(i).Name 20 Next 21 XlsObj.Quit 22 End Sub
4.选择Excel工作表进行预览，前几行，或全部有效数据，显示总数据条数。
5.选择标题列所在行
6.选择数据类型
7.选择新建数据表或现有表
8.选择Excel标题对应数据表的列
9.导入数据
