title: How To Initialize An Array Using Multiple Text Files - VBA
body: <p><strong>Trying to create a loop to open each .txt file in the active workbook folder, to be able to pull data out of each .txt file.</strong> </p>
  
  <p>(Data from .txt files will be used for initializing an array)</p>
  
  <hr>
  
  <p>Current code I created gives Compile error <code>Invalid Use of Property</code> for line of code:<br> <code>folder2 = ActiveWorkbook.path</code></p>
  
  <p>I know that <code>folder2</code> is defined as a <code>Folder</code> data type, and thus assigning the string <code>ActiveWorkbook.path</code> variable (to find current working folder location) is most likely causing the problem. </p>
  
  <p><strong>What would be the correct method of going through all the .txt files in the active workbook folder (or correct method of referencing the workbook folder path)?</strong></p>
  
  <hr>
  
  <p>Reference:
  <a href="http://www.mrexcel.com/forum/excel-questions/649666-how-import-all-text-files-column-wise-folder.html" rel="nofollow">How to import all text files from a folder</a> <br></p>
  
  <hr>
  
  <p>Code snippet with problems:</p>
  
  <pre><code>' Loop thru all files in the folder
      folder = ActiveWorkbook.path
  
      path = folder &amp; "\*.txt"
  
      Filename = Dir(path)
  
      ' Get a FileSystem object
      Set fso = New FileSystemObject
  
      ' get the directory you want
      folder2 = ActiveWorkbook.path
  
      For Each file In folder.Files
  
          Set FileText = file.OpenAsTextStream(ForReading)
  </code></pre>
  
  <p>Full Code:</p>
  
  <pre><code>Option Explicit
  
  Sub Initialize_barcode_lookup_Array()
  
   Dim fso As FileSystemObject
   Dim folder As String, path As String, count_txt_files As Long, Filename As String
   Dim folder2 As folder
   Dim file As file
   Dim FileText As TextStream
   Dim TextLine As String
   Dim Items() As String
   Dim i As Long, j As Long, k As Long
   Dim cl As Range
  
   Dim shipping_plan As Long      'Number of shipping plans text files imported
   Dim barcode_lookup() As String
   Dim lastRow As Long
   Dim longest_lastRow As Long
   Dim counter As Long
   Dim FNSKU_Input As String
  
  
      'Count the number of files in working directory ( - 1, for the Excel spreadsheet)
          folder = ActiveWorkbook.path
  
          path = folder &amp; "\*.txt"
  
          Filename = Dir(path)
  
          Do While Filename &lt;&gt; ""
              count_txt_files = count_txt_files + 1
                  Filename = Dir()
          Loop
  
          'Range("Q8").Value = count
          MsgBox count_txt_files &amp; " : files found in folder"
  
  
      'Define longest_lastRow
      longest_lastRow = 0
  
      'Define i
      i = 0
  
      ' Loop thru all files in the folder
      folder = ActiveWorkbook.path
  
      path = folder &amp; "\*.txt"
  
      Filename = Dir(path)
  
      ' Get a FileSystem object
      Set fso = New FileSystemObject
  
      ' get the directory you want
      folder2 = ActiveWorkbook.path 'fso.GetFolder("D:\YourDirectory\")
  
      For Each file In folder.Files
  
          Set FileText = file.OpenAsTextStream(ForReading)
  
          'Define lastRow
          lastRow = Range("A1").End(xlDown).Row   'Last row of the data set
  
             'Make sure longest_lastRow is the largest value found of lastRow within all _
             'shipping plan .txt files
             If lastRow &gt; longest_lastRow Then longest_lastRow = lastRow
  
          'Redimension Array barcode_lookup()
          ReDim barcode_lookup(count_txt_files - 1, longest_lastRow, 9)
  
          ' Read the file one line at a time
          Do While Not FileText.AtEndOfStream
              TextLine = FileText.ReadLine
              cl = TextLine
  
              'Initialize Array
                  For j = 0 To (lastRow - 1) 'UBound(barcode_lookup(lastRow - 1))
                      For k = 0 To 9
                          barcode_lookup(i, j, k) = cl
                          cl = cl.Offset(0, k + 1).Value
                      Next k
                      'Set cl one row down, and set column back to 0
                      cl = cl.Offset(j + 1, k - 9)
                  Next j
          Loop
  
          ' Clean up
          FileText.Close
  
          i = i + 1
      Next file
  
      Set FileText = Nothing
      Set file = Nothing
      Set folder2 = Nothing
      Set fso = Nothing
  
  End Sub
  </code></pre>