0

I need to save information from a text file into an array. But I dont know what the specific syntax is.

The information from the text file is about 2000 lines, which obviously you cant store within the vba script. The text looks like the below in one 

35SLFR0006350
35SLFR0026350
35SLFR0106350
BARSQR1306000
C280BD1016000
C280BD1016000_mitre
C280BD1016000_square
C280FR0006000
C280MU0006000
C280MU0026000
C280SH0006000
C280SH0006000_outer frame
C305BD0006000
C305BD0006000_mitre
C305BD0006000_square
C305BD0016000
C305BD0016000_mitre
C305BD0016000_square
C305BD2006000
C305BD2006000_mitre
C305BD2006000_square
C305FR0006000
C305MU0006000
C305MU0026000
C305MU0046000
C305SH0006000
C305SH0006000_Un E frame
C340BD1006000_mitre
C340BD1006000_Right,Left,Horizontal
C340BD1006000_Right,Left,Vertical
C340BD1006000_square
C340FR00060000
C340MU0006000
C340MU0026000
C340SH0006000
JvdV
  • 70,606
  • 8
  • 39
  • 70

1 Answers1

2

If you want to save your input file as an array you can first read the whole file and save it as one whole string. You can then use the Split function with the delimiter \n to return an array, where every element corresponds to one line of the file.

Const file As String = "<pathToFile>"
Dim ResultArray() As String
Dim tempString As String
Dim fn As Integer

fn = FreeFile()

Open file For Input As fn
While Not EOF(fn)
   Line Input #fn, LineString
   tempString = tempString & LineString & "\n"
Wend

ResultArray = Split(tempString, "\n")
Emil Hovhannisyan
  • 566
  • 1
  • 6
  • 17
  • Sub data() Const file As String = "C:\Users\dperks\Desktop\PROFILES.txt" Dim ResultArray() As String Dim tempString As String Dim fn As Integer fn = FreeFile() Open file For Input As fn While Not EOF(fn) Line Input #fn, LineString tempString = tempString & LineString & "\n" Wend ResultArray = Split(tempString, "\n") End Sub –  May 27 '19 at 12:28
  • Problem is its coming with this error: Constant expression required, so its not recognizing file within the program –  May 27 '19 at 12:30
  • It works for me, I dont know why it doesnt for you... Try this code: `Sub data() Dim file As String Dim ResultArray() As String Dim tempString As String Dim fn As Integer file = "" fn = FreeFile() Open file For Input As fn While Not EOF(fn) Line Input #fn, LineString tempString = tempString & LineString & "\n" Wend ResultArray = Split(tempString, "\n") End Sub` – Emil Hovhannisyan May 27 '19 at 12:50
  • Thanks, but its still saying the same error. See below. Is there any way I can store a variable into an array? Sub TextFile_PullData() Dim TextFile As Integer Dim FilePath As String Dim FileContent As String 'File Path of Text File FilePath = "C:\Users\chris\Desktop\MyFile.txt" 'Determine the next file number available for use by the FileOpen function TextFile = FreeFile 'Open the text file Open FilePath For Input As TextFile 'Store file content inside a variable FileContent = Input(LOF(TextFile), TextFile) 'Close Text File Close TextFile End Sub –  May 27 '19 at 13:12
  • Please tell me some information: Which Microsoft application are you using? What is the exact error message? In which line of the code does the error occur? – Emil Hovhannisyan May 27 '19 at 13:16
  • Its vba. Excel. The error occurs on Open file For Input As fn. The error is Constant expression required –  May 27 '19 at 13:23
  • 1
    Fixed. I edited the line to say Open "PROFILES.txt" For Input As fn. Thanks for the help –  May 27 '19 at 14:21