[CODE]
'程序代码
Private Sub Command1_Click()
'打开EXCEL文件
Set objExcelFile = New Excel.Application
objExcelFile.DisplayAlerts = False
Set objWorkBook = objExcelFile.Workbooks.Open("d:\11.xls")
Set objImportSheet = objWorkBook.Sheets(1)
cdblLastColNum = objImportSheet.UsedRange.Columns.Count
cdblLastRowNum = objImportSheet.UsedRange.Rows.Count
'打开记录集
Call OpenCon
'循环浏览EXCEL文件并将其中的数据导入到数据库中的ASTMB表中
For intCountI = 1 To 2365
For intCountL = 1 To 63
If Trim$(objImportSheet.Cells(intCountI, intCountL).Value) = "" Then
TempString = ""
Else
TempString = objImportSheet.Cells(intCountI, intCountL).Value
Select Case intCountL
Case 1
company = TempString
Case 2
creator = TempString
Case 3
usr_group = TempString
Case 4
create_date = TempString
Case 5
modifier = TempString
Case 6
modidate = TempString
Case 7
flag = "0"
Case 8
mb001 = TempString
Case 9
mb002 = TempString
Case 10
mb003 = TempString
Case 11
mb004 = TempString
Case 12
mb005 = TempString
Case 13
mb006 = TempString
Case 14
mb007 = TempString
Case 15
mb008 = TempString
Case 16
mb009 = TempString
Case 17
mb010 = TempString
Case 18
mb011 = TempString
Case 19
mb012 = "1"
Case 20
mb013 = TempString
Case 21
If TempString = "" Then
mb014 = "0"
Else
mb014 = CDbl(TempString)
End If
Case 22
If TempString = "" Then
mb015 = "0"
Else
mb015 = CDbl(TempString)
End If
Case 23
mb016 = TempString
Case 24
mb017 = TempString
Case 25
mb018 = TempString
Case 26
If TempString = "" Then
mb019 = "0"
Else
mb019 = CDbl(TempString)
End If
Case 27
If TempString = "" Then
mb020 = "0"
Else
mb020 = CDbl(TempString)
End If
Case 28
mb021 = "0"
Case 29
mb022 = "0"
Case 30
mb023 = TempString
Case 31
mb024 = TempString
Case 32
mb025 = TempString
Case 33
mb026 = "0"
Case 34
mb027 = "0"
Case 35
mb028 = TempString
Case 36
If TempString = "" Then
mb029 = "0"
Else
mb029 = CDbl(TempString)
End If
Case 37
mb030 = TempString
Case 38
mb031 = TempString
Case 39
mb032 = TempString
Case 40
mb033 = TempString
Case 41
mb034 = "0"
Case 42
mb035 = TempString
Case 43
mb036 = TempString
Case 44
mb037 = TempString
Case 45
mb038 = TempString
Case 46
mb039 = TempString
Case 47
mb040 = TempString
Case 48
mb041 = "0"
Case 49
mb042 = TempString
Case 50
mb043 = TempString
Case 51
mb044 = TempString
Case 52
mb045 = TempString
Case 53
mb046 = TempString
Case 54
mb047 = TempString
Case 55
mb048 = TempString
Case 56
mb049 = "0"
Case 57
mb050 = TempString
Case 58
mb051 = TempString
Case 59
mb052 = "0"
Case 60
mb053 = "0"
Case 61
mb054 = TempString
Case 62
mb055 = TempString
Case 63
mb056 = "0"
End Select
End If
Next intCountL
'插入到SQL数据库中
Call OpenRes("insert into ASTMB Values('','','','','','','" + flag + "','" + mb001 + "','" + mb002 + "','" + mb003 + "','" + mb004 + "','" + mb005 + "','" + mb006 + "','" + mb007 + "','" + mb008 + "','" + mb009 + "','" + mb010 + "','" + mb011 + "','" + mb012 + "','" + mb013 + "','" + mb014 + "','" + mb015 + "','" + mb016 + "','" + mb017 + "','" + mb018 + "','" + mb019 + "','" + mb020 + "','" + mb021 + "','" + mb022 + "','" + mb023 + "','" + mb024 + "','" + mb025 + "','" + mb026 + "','" + mb027 + "','" + mb028 + "','" + mb029 + "','" + mb030 + "','" + mb031 + "','" + mb032 + "','" + mb033 + "','" + mb034 + "','" + mb035 + "','" + mb036 + "','" + mb037 + "','" + mb038 + "','" + mb039 + "','" + mb040 + "','" + mb041 + "','" + mb042 + "','" + mb043 + "','" + mb044 + "','" + mb045 + "','" + mb046 + "','" + mb047 + "','" + mb048 + "','" + mb049 + "','" + mb050 + "','" + mb051 + "','" + mb052 + "','" + mb053 + "','" + mb054 + "','" + mb055 + "','" + mb056 + "')")
Next intCountI
'关闭打开的对象
mycon.Close
objExcelFile.Quit
Set objWorkBook = Nothing
Set objImportSheet = Nothing
Set objExcelFile = Nothing
MsgBox ("导入ASTMB表成功!")
End Sub
[/CODE]
[CODE]
‘模块代码
Public mycon As New ADODB.Connection
Public myres As New ADODB.Recordset
Public strsql As String
Public Function OpenCon()
If mycon.State <> adStateOpen Then
'如果mycon没有打开 就
mycon.Open "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=fingu;Data Source=TAN"
End If
End Function
Public Function OpenRes(ByVal strsql As String)
If myres.State = adStateOpen Then
myres.Close
'如果打开了就关掉
End If
With myres
.CursorLocation = adUseClient '在客户端上运行
.CursorType = adOpenDynamic '动态游标
.Open strsql, mycon, , , adCmdText '用SQL语句打开记录集
End With
End Function
Public Function Tra(ByVal strsql As String)
mycon.BeginTrans
mycon.Execute (strsql)
mycon.CommitTrans
End Function
[/CODE]
[此贴子已经被作者于2006-6-7 11:42:18编辑过]