在Excel中转换为文本格式 [英] Convert to Text Format In Excel
本文介绍了在Excel中转换为文本格式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Sub Extract()
Dim StrSQl As String
FromA =格式(Sheet1.Range(B3))
FromB =格式(Sheet1.Range(B4))
FromC =格式(Sheet1.Range(B5))
FromD =格式(Sheet1.Range(B6))
StrSQl =select Cno,Itno,Ref from test
StrSQ1 = StrSQ1& Cno =&来自而Itno喜欢&来自B& 和
StrSQl = StrSQl& Ref> =& FromC& 和Ref< =&&来自
StrSQl = StrSQl& Cno命令
con =Provider = IBMDA400; Data Source = xxx.xxx.xxx.xxx; User Id = yyyyy; Password = zzzzz
设置Db = CreateObject(ADODB.Connection)
设置rs = CreateObject(ADODB.recordset)
Db.ConnectionString = con
Db.Open
rs.Open StrSQl,Db,3,3
Sheet2.Cells(1,1).CopyFromRecordset rs
rs.Close
set rs = Nothing
Set cn = Nothing
End Sub
解决方案
如果您只是想将列显示为文本,可以使用撇号前缀,例如(假设单个撇号文字可以在iSeries SQL中表示为...)
StrSQl =选择Cno,Itno,CONCAT(''',Ref)作为参考从测试
I'm extracting some data from AS400 using excel macro. In the AS400, this particular column (Ref), shows 20100729000078154 but when I extracted it to excel, it will be 2.01007E+16. I need to hv 20100729000078154 as my final output. This is the macro that I used to extract the info from AS400 :-
Sub Extract()
Dim StrSQl As String
FromA = Format(Sheet1.Range("B3"))
FromB = Format(Sheet1.Range("B4"))
FromC = Format(Sheet1.Range("B5"))
FromD = Format(Sheet1.Range("B6"))
StrSQl = "select Cno,Itno,Ref from test "
StrSQl = StrSQl & " where Cno= " & FromA & " and Itno like " & FromB & " and "
StrSQl = StrSQl & " Ref >= " & FromC & " and Ref <= " & FromD & " "
StrSQl = StrSQl & " order by Cno "
con = "Provider=IBMDA400;Data Source=xxx.xxx.xxx.xxx;User Id=yyyyy;Password=zzzzz"
Set Db = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.recordset")
Db.ConnectionString = con
Db.Open
rs.Open StrSQl, Db, 3, 3
Sheet2.Cells(1, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set cn = Nothing
End Sub
解决方案
You can prefix an apostrophe if you're just wanting the column to display as text, something like (assuming a single apostrophe literal can be expressed as '' in iSeries SQL)...
StrSQl = "select Cno,Itno,CONCAT('''',Ref) as Ref from test "
这篇关于在Excel中转换为文本格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文