Copying multiple text files from a folder into excel

Are there any other files in the folder?

Will each text file go into a separate worksheet in the workbook? or will they all go into the same sheet?

Are the text files formatted the same? Are they .txt files or .csv files?

It is possible, but as you can surmise from my questions, your information is to vague to tell you how or supply a valid solution.

The main issue I have is trying to loop the entry of text documents into the single worksheet, and this is because they are all named differently.

Do you have some code that you have started and can be analyzed rather than start from scratch?

Here is a sample of the text file that I am using:

Company Name: Pears Galore
Revenue: $500,000

The code

Sub GetInfoFromText()
  Dim X As Long, FilePathAndName As String, TotalFile As String, FileNum As Long, Arr() As String
  Dim CompanyName As String, Revenue As String
    
  ' Get path and filename by whatever means you do now
  FilePathAndName = "C:\Users\revenue.txt"
  
  ' Read entire file into TotalFile variable
  FileNum = FreeFile
  Open FilePathAndName For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  ' Get Company Name
  Arr = Split(TotalFile, "Company Name: ")
  CompanyName = Split(Arr(1), vbCrLf)(0)
  
  ' Get Revenue
  Arr = Split(TotalFile, "Revenue: ")
  Revenue = Split(Arr(1), vbCrLf)(0)
         
    ActiveCell = CompanyName
    ActiveCell.Offset(1) = Revenue
         
End Sub

I took this code off someone else and edited to make it mine. However, I am not sure what can be done to make it loop for multiple text files, and insert the revenue figures into a column.
Is it also possible to automatically input the file path and name? or would this have to be done manually?