由于oracle数据库sql中in语句最多参数为1000条,如果我们有大量的数据需要用in语句查询,那把这些数据组装就成个一个很痛苦的问题,希望以下的宏程序能帮助到你
Sub substr() '截取前1000个单元格的数据并转化为sql格式 '数据1','数据2'
Dim row As Long, rowNum As Integer, column As Integer, str As String
Set thisws = ActiveSheet
row = thisws.UsedRange.Rows.count
num = 1
column = 2
str = "'"
For i = 1 To row
If (i Mod 1000) <> 0 Then
'If (i Mod 500) <> 0 Then '截取前500
If IsEmpty(Cells(i + 1, 1)) Then
str = str + "'" + Cells(i, 1) + "'" '默认第一行第一列,如果需要修改列需要把Cells(i, 1)中的1,修改为你所需要的列
Else
str = str + "'" + Cells(i, 1) + "',"
End If
Cells(i, 2) = ""
Else
str = str + "'" + Cells(i, 1) + "'"
Cells(num, column) = str
num = num + 1
str = "'"
End If
If IsEmpty(Cells(i + 1, 1)) Then
Cells(num, column) = str
Exit For
End If
Next
End Sub