在 Excel 中转换为文本格式
我正在使用 Excel 宏从 AS400 中提取一些数据。在 AS400 中，这个特定的列（参考）显示 20100729000078154，但当我将其提取到 Excel 时，它将是 2.01007E+16。我需要 hv 20100729000078154 作为我的最终输出。这是我用来从 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
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您只想将列显示为文本，则可以在撇号前面加上前缀，例如（假设单个撇号文字可以在 iSeries SQL 中表示为 ''）...
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)...