Excel VBA ADO剪切小数（ADO中可能的错误？） [英] Excel VBA ADO Cutting Decimals (Possible Bug in ADO?)

问题描述

有人可以帮我吗？

问题：
我在有效的模块中包含以下代码完全正常，但是当我在此示例文件上运行时，我注意到

The problem: I have the below code in a module which works perfectly usually, but when i run it on this sample file, I noticed that the decimals in the "Upper Limit" (column D) are cut.

我发现的一个临时解决方案是我必须在样本文件在第26行的上限从1到1.0（以便在末尾添加 .0），使其看起来像一个十进制数字。此后，将小数部分正确导入到工作表中。但是，这不是解决方案。

A temporary solution i found was that i had to modify in the sample file the upper limit from 1 to 1.0 (so to add ".0" at the end) at line 26 to make it look like a decimal number. After this the decimals were imported correctly on the sheet. But, this is not a solution.

我正在使用：
Excel 2013（15.0.5111.1000）32位（ Office软件包是：MS Office Standard 2013），并且启用了以下库

I'm using: Excel 2013 (15.0.5111.1000) 32-bit (office package is: MS Office Standard 2013) and the following libraries enabled

如何复制：

1. 将以下代码放入模块中并在示例文件上运行它。


2. 注意第D列。从第6行开始，所有小数都被删减。您将只看到
   。


3. 现在，打开示例文件（例如在Notepad ++中），然后将第26行的
   上限值修改为 1设为 1.0，然后保存。
   


4. 运行以下命令在修改后的示例文件上再次编码，并注意
   D列（上限）中从第6行开始的所有小数点
   都出现。

1. Put the below code in a module and run it on the sample file
2. Notice column D. Starting from line 6 all decimals are cut. You will see only integers.
3. Now, open the sample file (in Notepad++ for example), and modify the upper limit value in line 26 from "1" to "1.0", then save it.
4. Run the below code again on the modified sample file and notice in column D (Upper Limit) that starting from line 6 all decimals appear.

知道为什么会这样吗？

Sub ADODB_Import_CSV()

ThisWorkbook.Sheets(1).Activate
ThisWorkbook.Sheets(1).Cells.Clear

Dim Connection As New ADODB.Connection
Dim Recordset As New ADODB.Recordset

On Error Resume Next
ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path
On Error GoTo 0

ImportedFileFullPath = Application.GetOpenFilename
If ImportedFileFullPath = False Then Exit Sub
ImportedFileDirPath = Mid(ImportedFileFullPath, 1, InStrRev(ImportedFileFullPath, "\"))
ImportedFileName = Mid(ImportedFileFullPath, InStrRev(ImportedFileFullPath, "\") + 1, Len(ImportedFileFullPath))

Provider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ImportedFileDirPath & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"""
strSQL = "SELECT Trim(TestCodeDescription)," & _
                    "Cdbl(NumbericMeasure) AS Measurements," & _
                    "Cdbl(LowerLimit) AS LowerLimit," & _
                    "Cdbl(UpperLimit) AS UpperLimit" & _
             " " & _
             "FROM [" & ImportedFileName & "]" & _
             " " & _
             "WHERE (IsNumeric(LowerLimit) AND IsNumeric(UpperLimit) AND IsNumeric(NumbericMeasure) AND LowerLimit<>UpperLimit AND IsDate(EventDateTime1))"

Connection.Open Provider
Recordset.Open strSQL, Connection

For i = 0 To Recordset.Fields.Count - 1
    Cells(1, i + 1).Value = Recordset.Fields(i).Name
Next i

With ActiveSheet
    .Range("A2").CopyFromRecordset Recordset
    .Range("A1").AutoFilter
    .Columns.AutoFit
End With
Recordset.Close: Set Recordset = Nothing: Connection.Close: Set Connection = Nothing

End Sub

推荐答案

感谢大家解决了这个问题。

Thanks everybody this solved the problem.

所以原来，解决方案是在与ADO处理的我的CSV文件相同的文件夹中创建一个 schema.ini文件。

So it turned out that the solution is to create a "schema.ini" file in the same folder as my CSV file which is processed by ADO.

我创建了ini文件与以下几行，问题现在已经消失。

I created the ini file with the following lines and the issue is now gone.

[sample file for analysis.csv]
Format=CSVDelimited
Col1=SerialNumberCustomer Text
Col2=EventDateTime1 DateTime
Col3=EquipmentName Text
Col4=TestCodeDescription Text
Col5=NumbericMeasure Double
Col6=LowerLimit Double
Col7=UpperLimit Double

这篇关于Excel VBA ADO剪切小数（ADO中可能的错误？）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！