excel：从文本文件导入文本到电子表格上的下一个可用行？

I am using the following VBA code to import text from my text file into columns in excel, it's working great apart from the fact it is always inserting my text on row 1 and overwitting whatever text was previously in that row. I need a way for the code to insert the text onto the next available row on my spreadsheet?

我正在使用以下VBA代码将我的文本文件中的文本导入到excel中的列中,除了它总是在第1行插入我的文本并覆盖之前该行中的任何文本之外,它工作得很好。我需要一种方法让代码将文本插入电子表格中的下一个可用行?

Here's my code:

这是我的代码:

    Sub ImportFile()
    Close #1
    Open "Y:\Incident Logs\INSC89JH.txt" For Input As #1
    A = 1
     Do While Not EOF(1)
            Line Input #1, TextLine
            Cells(A) = TextLine
            A = A + 1
        Loop
    Close #1
End Sub

Please can someone show me where I am going wrong? Thanks

请有人能告诉我哪里出错了吗?谢谢

2 个解决方案

#1

Try changing Cells(A) = TextLine

尝试更改单元格(A)= TextLine

To Range("A" & A).Value = TextLine

至范围(“A”和A).Value = TextLine

This should work putting the text file which has new lines in, into a new row each loop

这应该可以将包含新行的文本文件放入每个循环的新行中

EDIT. Ignore the top part of my answer. Left it in if anyone finds it usefull.

编辑。忽略我的答案的顶部。如果有人发现它有用,请留下它。

You will need to get a check to see what range was used.

您需要检查以查看使用的范围。

Try this

Sub ImportFile()

    Dim rowCount As Long

    rowCount = ActiveSheet.UsedRange.Rows.Count + 1

    If Cells(1, 1).Value = "" Then rowCount = 1


    Close #1
    Open "Y:\Incident Logs\INSC89JH.txt" For Input As #1
    A = 1
     Do While Not EOF(1)
            Line Input #1, TextLine
            Cells(rowCount, A) = TextLine
            A = A + 1
        Loop
    Close #1
End Sub

#2

Change

Cells(A) = TextLine

单元格(A)= TextLine

Cells(A, 1) = TextLine

单元格(A,1)= TextLine

where the 1 in Cells(A, 1)stands for the number of the column (in this case column A) you want to insert the text to. So if you want it in column C, change it to Cells(A, 3).

其中单元格(A,1)中的1代表要插入文本的列数(在本例中为A列)。因此,如果您想在C列中将其更改为单元格(A,3)。

#1