Private Sub cmdOut_Click()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlCells As Excel.Range
Dim i As Integer
Set xlApp = CreateObject("Excel.Application")
Dim myText As String
Set xlBook = xlApp.Workbooks.Open(mstrDataFile)
Set xlSheet = xlBook.ActiveSheet
Set xlCells = xlSheet.Cells
i = 4
i = i + 1
myText = ""
myText = CStr(xlCells(i, 2).Value)
myText = CStr(xlCells(i, 3).Value)
myText = CStr(xlCells(i, 5).Value)
myText = CStr(xlCells(i, 6).Value)
Set xlCells = Nothing
Set xlSheet = Nothing
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub只能读取到EXCEL文件里的一行数据,我要想取多条应该怎么样修改.
解决方案 »
- vb引用ado2.7对象,在win7系统下编译,程序在xp中无法运行
- 项目级条件编译常数“DEBUG;TRACE : CONFIG="Debug" : DEBUG=-1 : TRACE=-1 ”无效: 字符无效。
- [原创]快速删除数组中对应索引项
- 有没有办法在datagrid的某列选中的时候执行一个事件?
- 关于combo控件与数据源的绑定问题!
- 請問在VB中可以寫重載函數嗎?如果可以,那又該怎麼申明函數呢?
- 在form的最下面放了一个label,怎么样才能让label跟form等宽
- 怎么用代码设置DBGrid的属性?
- 请教关于拨号API方面的问题,请高手给指点指点...急急急急十万火急
- foolishtiger(吴文智) and uguess(uguess) 我只好在此还分了.那边确实出了问题.奇怪.
- vb 数据库
- 标准表达式中数据类型不匹配的问题
可以把 Excel 当作数据库来处理,每个 Sheet 就是一张表。网上搜一下,一搜一大把。
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlCells As Excel.Range
Dim i As Integer
Set xlApp = CreateObject("Excel.Application")
Dim myText(1 To 30, 1 To 4) As String
Set xlBook = xlApp.Workbooks.Open(mstrDataFile)
Set xlSheet = xlBook.ActiveSheet
Set xlCells = xlSheet.Cells
For i = 5 To 34
myText = ""
myText(i, 1) = CStr(xlCells(i, 2).Value)
myText(i, 2) = CStr(xlCells(i, 3).Value)
myText(i, 3) = CStr(xlCells(i, 5).Value)
myText(i, 4) = CStr(xlCells(i, 6).Value)
Next i
Set xlCells = Nothing
Set xlSheet = Nothing
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
如果要更新的情况下应该怎么做.
条件是:变数 =TRUE时候更新
strSql = "SELECT "
strSql = strSql & " FROM "
strSql = strSql & " TMJoBA "
strSql = strSql & " WHERE "
strSql = strSql & " SOKOCD = EXCEL里取得的SOKOCD "
strSql = strSql & " AND SHOHINCD = EXCEL里取得的SHOHINCD"
麻烦告诉一下.谢谢!
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlCells As Excel.Range
Dim i As Integer
Set xlApp = CreateObject("Excel.Application")
Dim myText(1 To 30, 1 To 4) As String
Set xlBook = xlApp.Workbooks.Open(mstrDataFile)
Set xlSheet = xlBook.ActiveSheet
Set xlCells = xlSheet.Cells
For i = 5 To 34
myText(i, 1) = i & "tt1" '随便写的
myText(i, 2) = i & "tt2"
myText(i, 3) = i & "tt3"
myText(i, 4) = i & "tt4"
Next i
For i = 5 To 34
xlCells(i, 2).Value = myText(i, 1)
xlCells(i, 3).Value = myText(i, 2)
xlCells(i, 5).Value = myText(i, 3)
xlCells(i, 6).Value = myText(i, 4)
Next i Set xlCells = Nothing
Set xlSheet = Nothing
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
strSql = strSql & " FROM "
strSql = strSql & " TMJoBA "
strSql = strSql & " WHERE "
strSql = strSql & " SOKOCD = '" & xlCells(2, 2).Value & "' " EXCEL里 2, 2是SOKOCD
strSql = strSql & " AND SHOHINCD = '" & xlCells(2, 3).Value & "' " EXCEL里 2, 3是SHOHINCD "
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlCells As Excel.Range
Dim i As Integer
Dim OraDyn As OraDynaset
Dim flg As Integer
Dim strSql As String
Set xlApp = CreateObject("Excel.Application")
Dim myText(1 To 30, 1 To 4) As String
Set xlBook = xlApp.Workbooks.Open(mstrDataFile)
Set xlSheet = xlBook.ActiveSheet
Set xlCells = xlSheet.Cells
For i = 5 To 30
myText(i, 1) = CStr(xlCells(i, 2).Value)
myText(i, 2) = CStr(xlCells(i, 3).Value)
myText(i, 3) = CStr(xlCells(i, 5).Value)
myText(i, 4) = CStr(xlCells(i, 6).Value)
strSql = "SELECT "
strSql = strSql & " SOUKOCD, "
strSql = strSql & " HINCD , "
strSql = strSql & " HACHUTEN, "
strSql = strSql & " TEKIZAISU "
strSql = strSql & " FROM "
strSql = strSql & " TMJ0BA "
strSql = strSql & " WHERE "
strSql = strSql & " SOUKOCD = '" & xlCells(i, 2).Value & "' "
strSql = strSql & " AND HINCD ='" & xlCells(i, 3).Value & "' "
Set OraDyn = OraDB.CreateDynaset(strSql, ORADYN_READONLY)
If OraDyn.EOF = True Then
xlCells(i, 7) = "kkkkkkkkkk。"
Else
xlCells(i, 5) = Format(Change_Null_0(OraDyn.Fields("HACHUTEN")), "##,###,###,###")
xlCells(i, 6) = Format(Change_Null_0(OraDyn.Fields("TEKIZAISU")), "##,###,###,###")
End If
Next i
Set xlCells = Nothing
Set xlSheet = Nothing
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub程序基本上没有问题了,只是 Dim myText(1 To 30, 1 To 4) As String
里30可不可以用另的函数自动取得有几条.这里For i = 5 To 30也是一样.
还有就是EXCEL数据变化了,现在提示要不要保存.这句话能不能不要出现.谢谢
Dim a() As String
Dim intM As Integer
Dim intN As Integer
Dim i As Integer
Dim j As Integer
intM = 5
intN = 3
ReDim a(1 To intM, 1 To intN)
For i = 1 To intM
For j = 1 To intN
a(i, j) = i * 100 + j
Print a(i, j)
Next j
Next i
End Sub
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlCells As Excel.Range
Dim i As Integer
Dim OraDyn As OraDynaset
Dim flg As Integer
Dim strSql As String
Dim myText() As String
Dim intR As Integer
Dim intC As Integer
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(mstrDataFile)
Set xlSheet = xlBook.ActiveSheet
Set xlCells = xlSheet.Cells
intR = 30
intC = 4
ReDim myText(1 To intR, 1 To intC)
For i = 1 To intR
myText(i, 1) = CStr(xlCells(i, 2).Value)
myText(i, 2) = CStr(xlCells(i, 3).Value)
myText(i, 3) = CStr(xlCells(i, 5).Value)
myText(i, 4) = CStr(xlCells(i, 6).Value)
strSql = "SELECT "
strSql = strSql & " SOUKOCD, "
strSql = strSql & " HINCD , "
strSql = strSql & " HACHUTEN, "
strSql = strSql & " TEKIZAISU "
strSql = strSql & " FROM "
strSql = strSql & " TMJ0BA "
strSql = strSql & " WHERE "
strSql = strSql & " SOUKOCD = '" & xlCells(i, 2).Value & "' "
strSql = strSql & " AND HINCD ='" & xlCells(i, 3).Value & "' "
Set OraDyn = OraDB.CreateDynaset(strSql, ORADYN_READONLY)
If OraDyn.EOF = True Then
xlCells(i, 7) = "kkkkkkkkkk。"
Else
xlCells(i, 5) = Format(Change_Null_0(OraDyn.Fields("HACHUTEN")), "##,###,###,###")
xlCells(i, 6) = Format(Change_Null_0(OraDyn.Fields("TEKIZAISU")), "##,###,###,###")
End If
Next i
Set xlCells = Nothing
Set xlSheet = Nothing
xlBook.Close SaveChanges:=True
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlCells As Excel.Range
Dim i As Integer
Dim OraDyn As OraDynaset
Dim flg As Integer
Dim strSql As String
Dim myText() As String
Dim intR As Integer
Dim intC As Integer
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(mstrDataFile)
Set xlSheet = xlBook.ActiveSheet
Set xlCells = xlSheet.Cells
intR = xlSheet.Cells.SpecialCells(xlCellTypeLastCell).Row - 4
intC = 4
ReDim myText(1 To intR, 1 To intC)
For i = 1 To intR
myText(i, 1) = CStr(xlCells(i + 4, 2).Value)
myText(i, 2) = CStr(xlCells(i + 4, 3).Value)
myText(i, 3) = CStr(xlCells(i + 4, 5).Value)
myText(i, 4) = CStr(xlCells(i + 4, 6).Value)
strSql = "SELECT "
strSql = strSql & " SOUKOCD, "
strSql = strSql & " HINCD , "
strSql = strSql & " HACHUTEN, "
strSql = strSql & " TEKIZAISU "
strSql = strSql & " FROM "
strSql = strSql & " TMJ0BA "
strSql = strSql & " WHERE "
strSql = strSql & " SOUKOCD = '" & xlCells(i, 2).Value & "' "
strSql = strSql & " AND HINCD ='" & xlCells(i, 3).Value & "' "
Set OraDyn = OraDB.CreateDynaset(strSql, ORADYN_READONLY)
If OraDyn.EOF = True Then
xlCells(i, 7) = "kkkkkkkkkk。"
Else
xlCells(i, 5) = Format(Change_Null_0(OraDyn.Fields("HACHUTEN")), "##,###,###,###")
xlCells(i, 6) = Format(Change_Null_0(OraDyn.Fields("TEKIZAISU")), "##,###,###,###")
End If
Next i
Set xlCells = Nothing
Set xlSheet = Nothing
xlBook.Close SaveChanges:=True
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
for i=1 to 1000000
if CStr(xlCells(i, 2).Value)="" and CStr(xlCells(i, 3).Value) ="" then exit for