使用ADO在Excel中从.txt文件复制文本会忽略第一行 [英] Copying text from .txt file in Excel using ADO ignores first row

问题描述

我已经使用一些代码使用ADO将文本文件导入Excel，如下所示：

I have used some code to import a text file into Excel using ADO as shown below:

    Sub ImportTextFile()
    'Imports text file into Excel workbook using ADO.
    'If the number of records exceeds 65536 then it splits it over more than one sheet.

        Dim strFilePath As String, strFilename As String, strFullPath As String
        Dim lngCounter As Long
        Dim oConn As Object, oRS As Object, oFSObj As Object

        'Get a text file name
        strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select text file...")

        If strFullPath = "False" Then Exit Sub  'User pressed Cancel on the open file dialog

        'This gives us a full path name e.g. C:\temp\folder\file.txt
        'We need to split this into path and file name
        Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")

        strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path
        strFilename = oFSObj.GetFile(strFullPath).Name

        'Open an ADO connection to the folder specified
        Set oConn = CreateObject("ADODB.CONNECTION")
        oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & strFilePath & ";" & _
                   "Extended Properties=""text;HDR=Yes;FMT=Delimited"""

        Set oRS = CreateObject("ADODB.RECORDSET")

        'Now actually open the text file and import into Excel
        oRS.Open "SELECT * FROM [" & strFilename & "]", oConn, 3, 1, 1
        While Not oRS.EOF
            Sheets("Sheet1").Select
             ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536
            Range("A1").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Copy
            Range("C1").Select
            ActiveSheet.Paste
            Range("E1").Select
            ActiveSheet.Paste
            .
            .
            .
            Range("CW1").Select
            ActiveSheet.Paste
            Range("CY1").Select
            ActiveSheet.Paste

            Range("A1").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Copy
            Sheets("Recorder Log").Select
            Range("A9").Select
            ActiveSheet.Paste

            Range("C8").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Application.CutCopyMode = False
            Selection.Copy
            Range("C9").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Range(Selection, Selection.End(xlDown)).Select
            ActiveSheet.Paste

            Sheets("Sheet1").Select
            Cells.Select
            Selection.Delete Shift:=xlUp
            Sheets("Recorder Log").Select
            Range("C9").Select
        Wend

        oRS.Close
        oConn.Close

    End Sub

我认为代码运行正常，但随后注意到第一行文本我导入的文件没有复制到Excel中。

I thought the code was working perfectly but then noticed that the first line of text in the files I was importing was not getting copied into Excel.

是否有任何原因会导致这种情况发生，并且有一种解决方案来避免必须使用

Is there any reason why this would happen and is there a solution to avoid having to preformat the text files with a blank row at the start?

预先感谢。

CV

推荐答案

您已经通过连接属性设置了 HDR =是，要使其表现出色，意味着第一行是包含列的标题名称。由于您没有标题列，因此应该设置 HDR = No 。

You have set, through the connection properties, HDR=Yes, which to excel means that the first row is an header that contains column names. Since you have no header columns, you should set HDR=No instead.

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & strFilePath & ";" & _
               "Extended Properties=""text;HDR=No;FMT=Delimited"""

这篇关于使用ADO在Excel中从.txt文件复制文本会忽略第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！