How to have VB Script read multiple files to extract data

[R John]

I am currently using the VB code below to read a single file (.cfg format) and extract required data and have this data placed into separate columns using headings: DESCRIPTION | SPEED | SERVICE NUM. Sample format of this output in Excel is also shown.

Private Sub CommandButton1_Click()
Dim myFile As String, find1 As String, i As Integer, und As String, speed2 As Integer, text As String, Desc As String, r As Long, dashpos As Long, m As Long, textline As String, strLeft As String, strFind As String, strRight As String, strMid As String, speed As String
Dim regex As Object

'place source location of text file her
myFile = "C:\Users\username\Desktop\rand\1009.cfg"

Open myFile For Input As #1

Do Until EOF(1)
    Line Input #1, textline
    text = text & textline & vbCrLf
Loop

Close #1
Set regex = CreateObject("VBScript.RegExp")

    Range("A1").Value = "Description"
    Range("B1").Value = "Speed"
    Range("c1").Value = "Service Num"

    With regex
        .Global = True
        .MultiLine = False
        .IgnoreCase = False
        .Pattern = "description (.*?)[_ ](\d+M)[ _]((WDC)?\d{7})"
    End With
    Set Matches = regex.Execute(text)
    
    For Each mtch In Matches
        Range("A2").Offset(Idx) = mtch.submatches(0)
        Range("A2").Offset(Idx, 1) = mtch.submatches(1)
        Range("A2").Offset(Idx, 2) = mtch.submatches(2)
        Idx = Idx + 1
    Next

    Set regex = Nothing
   
    End Sub

Sample output of Excel after running script:

DESCRIPTION	SPEED	SERVICE NUM
COMPANY_NAME1	30M	5554454
COMPANY_NAME2	20M	3333433

The issue I am having is that there are multiple .cfg files that I have to extract data from, so having to run the VB code manually for EACH file is really tedious. Is there a way to run the script in Excel and have ALL the .cfg files in the directory be read and have the required data extracted and placed into a SINGLE Excel sheet in one go after running the VB script once?

The .cfg files can be renamed consecutively and placed into a single directory, for example. 1.cfg, 2.cfg, 3.cfg. etc...

 

[BobBridges]

You can name the files as you suggest, but I don't think it's necessary. You can use the FileSystemObject to go to a particular folder, pick out all the files named <something>.cfg, and process just those. That might look something like this (but I haven't tested this code; you'll have to debug it if you want to use it):

' fnp is a string with the path name.  You can set it as a Const, or
' get it in an InputBox from the user, or use the OpenFolder dialogue.
Set ofs = CreateObject(Scripting.FileSystemObject)
If Not ofs.FolderExists(fnp) then 'check whether the folder exists; if not, abend with error message
Set ofo = ofs.GetFolder(fnp) 'get the folder object
For Each ofi in ofo.Files 'ofi will be an object representing each file object in the folder in turn
  fnn = ofi.Name
  If Right(fnn, 4) <> ".cfg" Then Goto SkipFile
  ' Here you do whatever it is you want to do with this file.
SkipFile:
  Next ofi

 

[Logit]

.
Untested here :

Sub LoopThroughFiles()
    Dim StrFile As String
    StrFile = Dir("C:\Users\username\Desktop\rand\*.cfg*")
    
Do While Len(StrFile) > 0
    
    'at this point either a call to your macro ...
        CommandButton1_Click

    'or ... place all of your macro code here with the macro name at top


        StrFile = Dir
    Loop

End Sub