修剪引用文本发布在Excel VBA编辑器 [英] Trim ref text posted in Excel VBA editor

问题描述

在这里输入代码我有以下代码在处理过程中有一点点，所以我试图从.txt文件中获取文本以显示在Excel的单元格中，代码将是

enter code hereI have my following code a bit short in the process of working on it, so I am trying to get text from a .txt file to be displayed in a cell on Excel, the code will be

Sub citi()
Dim c As Range
Open "C:\Users\alvaradod\Desktop\citi macro\Import File.txt" For Input As #1
R = 0
Dim i As Range
Dim num As Integer
Dim arrData() As String
the_value = Sheets("Prog").Range("A1")
Do Until EOF(1)
Line Input #1, Data
If Not Left(Data, 1) = "" Then
'import this row
R = R + 1
Cells(R, 1).Value = Data
'Mid(the_value, 3, 5)
'Left(Data, Len(Data) - 3)).Value
End If
Loop
For Each i In Range("A1")
i.Select
ActiveCell.Rows("1:1").Mid(Data(i), 49, 5).Select
'ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Sheets("Import").Range("A1").End(xlUp).Offset(num, 0).PasteSpecial
ActiveCell.Rows.Delete
 num = num + 1
Next i

End Sub

第11行将从第一行开始.TXT文件到EXCEL，在此功能之后，我需要在EXCEL表中显示相同的文本以显示第一个5个字符

" LINE 11 WILL PAST THE TEXT FROM LINE ONE ON .TXT FILE TO EXCEL, AFTER THIS FUNCTION I NEED TO TRIM THIS SAME TEXT IN THE EXCEL SHEET TO SHOW THE FIRST 5 CHARACTERS"

推荐答案

你的问题不是很清楚，但也许是这样的吗？

Your question isn't very clear, but perhaps something like this?

Sub citi()

    Dim oFSO As Object
    Dim arrData() As String
    Dim arrImport1(1 To 65000) As String
    Dim arrImport2(1 To 65000) As String
    Dim i As Long, j As Long

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    arrData = Split(oFSO.OpenTextFile("C:\Test\test.txt").ReadAll, vbCrLf)

    For i = LBound(arrData) To UBound(arrData)
        If Len(arrData(i)) > 0 Then
            j = j + 1
            arrImport1(j) = Mid(arrData(i), 3, 5)
            arrImport2(j) = Mid(arrData(i), 49, 5)
        End If
    Next i

    If j > 0 Then
        Sheets("Sheet1").Range("A1").Resize(j).Value = Application.Transpose(arrImport1)
        Sheets("Sheet2").Range("A1").Resize(j).Value = Application.Transpose(arrImport2)
    End If

    Set oFSO = Nothing
    Erase arrData
    Erase arrImport

End Sub

这篇关于修剪引用文本发布在Excel VBA编辑器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！