I need help coming up with a VBA script.

Wilsonota

New Member
Joined
May 28, 2015
Messages
4
I have a file name in column D for example 0209011.txt. There are a lot of these, approximately 16 thousand.
That .txt text represents a file name in the folder CATTEXT located on my drive. I need a VBA script that does exactly this:

1. Reads the .txt file name in column D of my spreadsheet.
2. Looks in folder CATTEXT and finds the corresponding .txt file
3. Extracts the text from the file and places it on the same row it got the .txt file from and column J.
4. Loops through until it completes all 16k.

I'm a VBA noob so I could use some help with this. Going to be googling hard until I hopefully get a reply. Thank you very much.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Welcome to the Board!

A few questions:

1. On what row in column D do these file names start?
2. Is there only one row of data in each text file?
3. What should it do if it cannot find a certain file name?
 
Upvote 0
The data starts on row 2. Each text file contains a few sentences but should be fine stuffing in one cell. Missing file names should just be skipped. I also realized that I don't need to scan a folder for each file I can go right to the file as I have the filepath for each one. Ex: C:\Program Files (x86)\xxx\xxx\CATTEXT\0209011.txt
 
Upvote 0
OK. It is important to understand the nature of these text files.
1. Are they delimited or Fixed Width?
2. If you open up the data in a Text Editor, is all the data on the first row, or are there multiple rows of data?

If you have multiple rows of data, it makes it a bit trickier, as you need to paste multiple rows of data into a single cell. So you need to decide how you are going to do that. Do you want "soft carriage returns" between each row in the cell? Or do you just want all the data smashed together?
 
Upvote 0
I'm not sure what you mean by delimited or fixed width. An example of data in one of the files looks like this:

Filter Element pack of 10 (Course) 20 ppi black

I went through a significant number of the files and it looks like all the text is on one row. Word wrap was not on so there are no soft returns. Just one line.
 
Upvote 0
This is my first reply - so here goes:

Without knowing all of your particulars, Here is a VBA code example that you can start from:

Code:
Sub ProcessTextFiles()
' You must be on the sheet that has the files you want to process (otherwise you need more code)
' Read All Text files listed in column D (row) and put all text from file into Column J (row)
  Dim iCount As Long             ' for loping through all rows of column D
  Dim sProcessFile As String     ' full filename and path
  Dim sFilesPath As String       ' file path of text files (could hardcode but this offers flexibility)
  Dim strFileContent As String   ' text file to process - this comes from extracting the name from column D & (row)
  sFilesPath = "C:\CATTEXT\"      ' put file path of the text files here
  iCount = 2  ' Start after the headings  ' if your spreadsheet has headings iCount should start at 2 otherwise start at 1
  Cells(iCount, 4).Select        ' Get the filename from the first selection
  Do While Selection.Value <> "" ' Stop processing when there are no more rows with filenames in column D
    If Trim(Selection.Value & " ") = "" Then Exit Do ' double check just in case the column D row has a space in it
    sProcessFile = sFilesPath & Trim(Selection.Value & " ") ' build the full file/path to open
    Open sProcessFile For Input As 1        ' Open the currently selected file
    Cells(iCount, 10).Value = Input$(LOF(1), 1) ' read all of the data and place it into column J of the same row
    Close #1                                   ' you are done with this file so close it
    iCount = iCount + 1                        ' increment your row pointer
    Cells(iCount, 4).Select                    ' select the new row column D
  Loop
Finished:
  MsgBox "All of the text files have been processed"
End Sub

I hope that this helps.


I have a file name in column D for example 0209011.txt. There are a lot of these, approximately 16 thousand.
That .txt text represents a file name in the folder CATTEXT located on my drive. I need a VBA script that does exactly this:

1. Reads the .txt file name in column D of my spreadsheet.
2. Looks in folder CATTEXT and finds the corresponding .txt file
3. Extracts the text from the file and places it on the same row it got the .txt file from and column J.
4. Loops through until it completes all 16k.

I'm a VBA noob so I could use some help with this. Going to be googling hard until I hopefully get a reply. Thank you very much.
 
Upvote 0
Thank you skipres. What you have there almost works for what I need. I just gotta figure out how to handle if the file doesn't exist in my directory.
Also thank you Joe4 for helping me narrow down exactly what I need.

Edit:
<code>On Error Resume Next seems to do this.</code>
 
Last edited:
Upvote 0
If you want to check if all of your files exist before processing, here is some added code and a Function (by Author: Allen Browne. Allen Browne's Database and Training June, 2006.) You do not have to "END" with the first hit, just remove the "END".

Code:
Sub ProcessTextFiles()
' You must be on the sheet that has the files you want to process (otherwise you need more code)
' Read All Text files listed in column D (row) and put all text from file into Column J (row)
  Dim iCount As Long             ' for loping through all rows of column D
  Dim sProcessFile As String     ' full filename and path
  Dim sFilesPath As String       ' file path of text files (could hardcode but this offers flexibility)
  Dim strFileContent As String   ' text file to process - this comes from extracting the name from column D & (row)
  sFilesPath = "C:\CATTEXT\"      ' put file path of the text files here
  iCount = 2  ' Start after the headings  ' if your spreadsheet has headings iCount should start at 2 otherwise start at 1
  Cells(iCount, 4).Select        ' Get the filename from the first selection
' Check all files exist before processing - uses function by Allen Browne. http://allenbrowne.com June, 2006.

  Do While Selection.Value <> "" ' Stop processing when there are no more rows with filenames in column D
    If Trim(Selection.Value & " ") = "" Then Exit Do ' double check just in case the column D row has a space in it
    sProcessFile = sFilesPath & Trim(Selection.Value & " ") ' build the full file/path to open
    If Not FileExists(sProcessFile) Then
      MsgBox "FileName " & sProcessFile & " was not found - Please fix and Restart"
      End
    End If
    iCount = iCount + 1
  Loop
  
' Restart the Starting position to proecss the Text files
  iCount = 2  ' Start after the headings  ' if your spreadsheet has headings iCount should start at 2 otherwise start at 1
  Cells(iCount, 4).Select        ' Get the filename from the first selection
  Do While Selection.Value <> "" ' Stop processing when there are no more rows with filenames in column D
    If Trim(Selection.Value & " ") = "" Then Exit Do ' double check just in case the column D row has a space in it
    sProcessFile = sFilesPath & Trim(Selection.Value & " ") ' build the full file/path to open
    If Not FileExists(sProcessFile) Then
      MsgBox "FileName " & sProcessFile & " was not found - Please fix and Restart"
      End                                   'you can end here or do some sort of process
    End If
    Open sProcessFile For Input As 1        ' Open the currently selected file
    Cells(iCount, 10).Value = Input$(LOF(1), 1) ' read all of the data and place it into column J of the same row
    Close #1                                   ' you are done with this file so close it
    iCount = iCount + 1                        ' increment your row pointer
    Cells(iCount, 4).Select                    ' select the new row column D
  Loop
Finished:
  MsgBox "All of the text files have been processed"
End Sub
Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
    'Purpose:   Return True if the file exists, even if it is hidden.
    'Arguments: strFile: File name to look for. Current directory searched if no path included.
    '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
    'Note:      Does not look inside subdirectories for the file.
    'Author:    Allen Browne. http://allenbrowne.com June, 2006.
    Dim lngAttributes As Long

    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
        'Strip any trailing slash, so Dir does not look inside the folder.
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If

    'If Dir() returns something, the file exists.
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function

Enjoy. :)
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top