Function WithSqlReturnExcelRecordSet(Sql As String, InputFileName) As ADODB.Recordset
Dim Rst As New ADODB.Recordset
Set Cnn = New ADODB.Connection
Cnn.Open "Provider = MicroSoft.Jet.OLEDB.4.0; Extended Properties = 'Excel 8.0;imex=1'; Data Source = " & InputFileName
Rst.Open Sql, Cnn, adOpenStatic
Set WithSqlReturnExcelRecordSet = Rst
End Function
在上面功能环境中,不能使用select case 语句真让人烦?
在数据源的某一列的数据组成是RF+RF或FM+M或T+G
现在要分离采用方法是iif功能+select UNION工作方法. t1 = "iif(InStr(a2, '+')>0,Left(a2,InStr(Trim(a2), '+') - 1),trim(a2))"
t2 = "iif(InStr(a2, '+')>0,Right(a2,len(trim(a2))-InStr(Trim(a2), '+') ) )"
Sql = "Select a1," & t1
Sql = Sql & " from [Sheet1$] "
Sql = Sql & " union ALL "
Sql = Sql & "Select a1," & t2
Sql = Sql & " from [Sheet1$] " 如果在上面SQL环境中,能用select case就好了.具有此功能只有SQL server
请问各位大侠你们能有什么更好的方法.
Dim Rst As New ADODB.Recordset
Set Cnn = New ADODB.Connection
Cnn.Open "Provider = MicroSoft.Jet.OLEDB.4.0; Extended Properties = 'Excel 8.0;imex=1'; Data Source = " & InputFileName
Rst.Open Sql, Cnn, adOpenStatic
Set WithSqlReturnExcelRecordSet = Rst
End Function
在上面功能环境中,不能使用select case 语句真让人烦?
在数据源的某一列的数据组成是RF+RF或FM+M或T+G
现在要分离采用方法是iif功能+select UNION工作方法. t1 = "iif(InStr(a2, '+')>0,Left(a2,InStr(Trim(a2), '+') - 1),trim(a2))"
t2 = "iif(InStr(a2, '+')>0,Right(a2,len(trim(a2))-InStr(Trim(a2), '+') ) )"
Sql = "Select a1," & t1
Sql = Sql & " from [Sheet1$] "
Sql = Sql & " union ALL "
Sql = Sql & "Select a1," & t2
Sql = Sql & " from [Sheet1$] " 如果在上面SQL环境中,能用select case就好了.具有此功能只有SQL server
请问各位大侠你们能有什么更好的方法.
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货