xls批量导入sql server

 Option Compare Database

Type ServerInfo
    Name As String
    UID As String
    PWD As String
End Type
Type DBInfo
    SVInfo As ServerInfo
    DBName As String
End Type

'-----------------------------------------------
'功能:批量导入目录下所有xls文件到sql server
'DBInfo:数据库连接信息
'xlsPath:xls文件路径
'-----------------------------------------------
Sub ExcelImportSqlServerInPath(DBInfo As DBInfo, xlsPath As String)
    If Right(xlsPath, 1) <> "/" Then
        xlsPath = xlsPath + "/"
    End If
   
    '打开当前目录
    ChDir xlsPath
   
    '导入目录下所有文件
    Dim f_name$
    f_name = Dir(xlsPath + "*.xls")
    While f_name <> ""
        Dim TableName As String
        TableName = Trim(Left(f_name, Len(f_name) - 4))
        ExcelImportSqlServer f_name, TableName, DBInfo, TableName
       
        f_name = Dir()
    Wend
End Sub

'调用代码：

Sub ImportExcelBatch()

    Dim database As DBInfo
    database.DBName = "database name"
    database.SVInfo.Name = "pc name"
    database.SVInfo.UID = "sa"
    database.SVInfo.PWD = "123456"
   
    ExcelImportSqlServerInPath database, "c:/"
End Sub