Problem extracting data from large .txt file

[CrishFergus]

Hi all,

Looking for some help with extracting specific text from a large .txt file. (The files in question are .txt exports of nfo files)

I currently have things set up to select a folder containing the files, pull a bit of info from the file names themselves, and then cycle through the files extracting the required information, which is working for some things.

I'm able to get the first three pieces of info (the OS, the Processor, the RAM) fine, but not the display resolution, which is appearing blank.

Here's my current code:

Private Sub CommandButton1_Click()


    Dim OS As Integer
    Dim Processor As Integer
    Dim RAM As Integer
    Dim Resolution As Integer
    Dim Version As Integer
    Dim BIOS As Integer
    Dim Total As Integer
    Dim Bits As Integer
    Dim cline As Integer
   
   Dim MyFolder As String
   Dim MyFile As String
   

   Worksheets("nfo Info").Select
   
   Cells.ClearContents
   
   
   Range("A1").Value = "File Name"
   Range("B1").Value = "Test Centre ID"
   Range("C1").Value = "OS Name"
   Range("D1").Value = "Processor"
   Range("E1").Value = "RAM"
   Range("F1").Value = "Display Resolution"

   
   cline = 2
   
   
On Error Resume Next

Application.ScreenUpdating = False
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please select a folder"
        .Show
        .AllowMultiSelect = False
    If .SelectedItems.Count = 0 Then
        MsgBox "You did not select a folder"
      Exit Sub
    End If
    MyFolder = .SelectedItems(1) & "\"
End With

MyFile = Dir(MyFolder)

ChDir MyFolder


Do While MyFile <> ""

    Open MyFile For Input As #1
    ActiveSheet.Cells(cline, 1) = MyFile
    ActiveSheet.Cells(cline, 2) = Left(MyFile, 6)
    
 Do Until EOF(1)
    Line Input #1, textline
    Text = Text & textline
 Loop
    
    Close #1

OS = InStr(Text, "OS Name")
Processor = InStr(Text, "Processor")
RAM = InStr(Text, "Installed Physical Memory (RAM)")
Resolution = InStr(Text, "Resolution")
Version = InStr(Text, "Version")
BIOS = InStr(Text, "BIOS Version/Date")
Total = InStr(Text, "Total Physical Memory")
Bits = InStr(Text, "Bits/Pixel") 


    ActiveSheet.Cells(cline, 3) = Mid(Text, OS + 8, Version - (OS + 8))
    ActiveSheet.Cells(cline, 4) = Mid(Text, Processor + 10, BIOS - (Processor + 10))
    ActiveSheet.Cells(cline, 5) = Mid(Text, RAM + 32, Total - (RAM + 32))
    ActiveSheet.Cells(cline, 6) = Mid(Text, Resolution + 11, Bits - (Resolution + 11))
    

    
  cline = cline + 1
  Text = 0

MyFile = Dir

Loop

Application.ScreenUpdating = True

Columns("A:E").EntireColumn.AutoFit
End Sub

I can provide a wetransfer link or similar to an example of one of the .txt nfo files if needed, but it should work on any nfo txt export (the "Test Centre ID" relates to a specific file naming convention being used in this case, so isn't important).

My current thoughts on why this isn't working is either due to 'Resolution' appearing multiple times in each file (although I'm only interested in the first one, so assumed this wouldn't be an issue, as I believe InStr only returns a value for the first relevant string?) or I'm hitting a string length limit?

Any pointers on where I've gone wrong would be greatly appreciated, and let me know if there's any extra info needed that I've missed needed to answer the question.

Thanks all!

 

[DanteAmor]

Hello and welcome to MrExcel.
If you could upload to google drive, the txt file with problems.
Also upload an excel file with the desired result.
Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[CrishFergus]

Hello and welcome to MrExcel.
If you could upload to google drive, the txt file with problems.
Also upload an excel file with the desired result.
Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Thanks Dante!

Google drive link here: Excel nfo reader - Google Drive

I've saved a blank excel file with my current code to select a folder and pull the data, another with the desired result and a folder of the txt files which can be selected to test. The files in there are all identical apart from the RAM, which I changed just to make sure the code was cycling through the files, not repeating one.

 

[DanteAmor]

The error is due to overflow.
Because you have the variable declared as Integer, that allows you a value of up to 32,767 and the result in your variable "resolution" is 34,406.
You could have noticed but you have this instruction: On Error Resume Next.
It is recommended NOT to use On Error and try to solve possible problems with code.
Change the data type from Integer to Long:

  Dim OS As Long
  Dim Processor As Long
  Dim RAM As Long
  Dim Resolution As Long
  Dim Version As Long
  Dim BIOS As Long
  Dim Total As Long
  Dim Bits As Long
  Dim cline As Long

I tested the code with the change and it works fine.

 

[CrishFergus]

The error is due to overflow.
Because you have the variable declared as Integer, that allows you a value of up to 32,767 and the result in your variable "resolution" is 34,406.
You could have noticed but you have this instruction: On Error Resume Next.
It is recommended NOT to use On Error and try to solve possible problems with code.
Change the data type from Integer to Long:

  Dim OS As Long
  Dim Processor As Long
  Dim RAM As Long
  Dim Resolution As Long
  Dim Version As Long
  Dim BIOS As Long
  Dim Total As Long
  Dim Bits As Long
  Dim cline As Long

I tested the code with the change and it works fine.

Thank you, Dante!

Yes, that's done it. Thanks for your help here, and yes, I'll make sure to remember to take On Error out when I'm looking for problems in the future.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.