英文:
SQL Query executed in VBA is missing decimals when pasted
问题
我正在通过VBA宏运行SQL查询。我的查询中的一个输出列（相当于Excel工作表中的列K）包含数字。当结果粘贴到Excel工作表时，小数部分消失了。例如，140000.35粘贴为140000。我不想将列格式更改为文本，因为我需要对这些金额进行操作。关于发生了什么以及如何解决这个问题有任何想法吗？
这是我的VBA代码：
Sub RunQueryAllocation()
' 优化禁用功能
With Application
.ScreenUpdating = False '禁用屏幕更新
.Calculation = xlCalculationManual '禁用自动计算
.EnableEvents = False '禁用事件
End With
'清除目标工作表
ThisWorkbook.Sheets("1.Asignacion colateral").Range("B3:J6000").ClearContents
' 设置数据库连接
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim i As Long
Dim Fecha1 As Date
Dim Fecha_Cierre As String
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=SPRSQPC-IW-P01.bmesp.com;" _
& "Initial Catalog=master;Integrated Security=SSPI;"
conn.Open
' 设置SQL查询
strSQL = "" '我的查询字符串
' 执行SQL查询
rs.Open strSQL, conn
' 将输出粘贴到Excel工作表
With ThisWorkbook.Sheets("Sheet1")
For i = 0 To rs.Fields.Count - 1
.Cells(2, i + 1).Value = rs.Fields(i).Name
Next
Range("A3").CopyFromRecordset rs
End With
ThisWorkbook.Sheets("Sheet1").Columns.AutoFit
' 清理
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
' 重新激活功能
With Application
.ScreenUpdating = True '启用屏幕更新
.Calculation = xlCalculationAutomatic '启用自动计算
.EnableEvents = True '启用事件
End With
End Sub
如果需要进一步的帮助，请告诉我。
英文:
I'm running a SQL query through a VBA macro. One of the output columns (equivalent to column K in the excel worksheet) from my query contains numbers. When the results are pasted into an excel sheet, decimals dissapear. For example, 140000.35 is pasted as 140000. I don't want to change the column format to text because I need to operate with these amounts.
Any ideas on what's happening and how to solve it?
This is my VBA code:
Sub RunQueryAllocation()
' Optimization disabling features
With Application
.ScreenUpdating = False 'Disable screen updating
.Calculation = xlCalculationManual 'Disable automatic calculation
.EnableEvents = False 'Disable events
End With
'Clear destination worksheet
ThisWorkbook.Sheets("1.Asignacion colateral").Range("B3:J6000").ClearContents
' Set up database connection
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim i As Long
Dim Fecha1 As Date
Dim Fecha_Cierre As String
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=SPRSQPC-IW-P01.bmesp.com;" _
& "Initial Catalog=master;Integrated Security=SSPI;"
conn.Open
' Set up SQL query
strSQL = "" ' my query string
' Execute SQL query
rs.Open strSQL, conn
' Paste output into Excel worksheet
With ThisWorkbook.Sheets("Sheet1")
For i = 0 To rs.Fields.Count - 1
.Cells(2, i + 1).Value = rs.Fields(i).Name
Next
Range("A3").CopyFromRecordset rs
End With
ThisWorkbook.Sheets("Sheet1").Columns.AutoFit
' Clean up
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
' Reactivate features
With Application
.ScreenUpdating = True 'Enable screen updating
.Calculation = xlCalculationAutomatic 'Enable automatic calculation
.EnableEvents = True 'Enable events
End With
End Sub
答案1
得分: 0
你可以将数据类型更改为十进制，然后再尝试一次。在下面的链接中，你可以找到更多信息。
英文:
Can you change the data type to decimal and try it again. In the link below you can find some more info.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论