在VBA中读取文本文件并根据Excel中的用户输入修改特定单元格 [英] Reading a text file in vba and modifying a certain cell depending on the user input in excel

问题描述

我今天第一次开始在vba中编码.我的代码应首先要求用户输入，然后再从文本文件中读取.如果用户输入与文本文件中的100号匹配，则应使用100号旁边的内容填充某个单元格，直到工作表中的-.如果是GHF897-HTU71L，则应填充该单元直到GHF897.

I started coding in vba today for the first time. My code should ask for a user input first and then read from a text file. If the user input matches the 100 number in the text file it should then populate a certain cell with whatever is next to the 100 number, until the - in the worksheet. If it is GHF897-HTU71L then it should populate the cell until GHF897.

这是文本文件

HGJDJS UFHFHFHB HHGG 0001    100000896765    GHF897-HTU71L-7811-YTHIN            U1P903678    GHF897_0000000016       000001    |||||||    W69      000001    |||||||---    X72    BAL TAG    000002    |||||||    X75    CONFIG    000001    |||||||

 GFHRTE 0001    100657853125   FGD867-YTURGL-TT55-YTHU/NH7            U1P903679    HFJDJH1_0000000015   

---

Sub readFile()
    Dim myValue As Variant

    myValue = InputBox("Please Enter/Scan the order number", "Order Number")

    Dim arr() As String
    Dim i As Integer

    Const strFileName As String = "C:\text2.txt"

    FileNum = FreeFile

    Open strFileName For Input As #FileNum

    Line Input #FileNum, StrBuffer

    i = 0

    Do Until EOF(1)
        arr = Split(StrBuffer, vbTab)

        If arr(i) = myValue Then Range("D4").Value = arr(i + 1)

        i = i + 1

        'End If
    Loop

    Close #1
End Sub

应使用arr(i + 1)的值填充单元格"d4"

It should populate the cell "d4" with the value of arr(i+1)

推荐答案

如果您的"100数字"在诸如"GHF897-"(字母，字母，字母，数字，数字，数字，破折号)之类的字符串中使用，可以使用RegEx查找它.下面的函数在输入字符串中搜索匹配项并将其返回.您应该能够操纵输出字符串以适合您的需求.有很多RegEx教程，可以轻松地在网上找到它们，也可以在StackOverflow上搜索更多示例和想法.

If your "100 number" is slways within a string such as 'GHF897-' (letter, letter, letter, number, number, number, dash) you can use RegEx to look for it. The function below searches input string for a match and returns it. You should be able to manipulate the output string to suit your needs. There is a multitude of RegEx tutorial which can be easily found online or you can search StackOverflow for more examples and ideas.

Function MatchNumber(str As String) As String

    Dim intNo As Integer
    Dim RegEx As Object
    Dim obj As Object

    Set RegEx = CreateObject("vbscript.regexp")

    RegEx.Pattern = "[A-Z][A-Z][A-Z][0-9][0-9][0-9]-"

    Set obj = RegEx.Execute(str)

    MatchNumber = obj(0)

End Function

这篇关于在VBA中读取文本文件并根据Excel中的用户输入修改特定单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！