Macro/VBA to Read ASC file

[SAFD1450]

Hello,

I have been using Excel for a long time now, but I am just starting to learn the benefits of macros/VBA and I am loving it.

I was hoping someone could help me with the code to import a .asc file into a workbook.

I would like to create a button that the user clicks that asks them what file they would like to use. From there it will import only select information. The .asc file is created by another program that we have no control over the file created. It creates a text file that is Tab select for all of the header information and comma select for all of the actual data. I would like the code to import line 2 (the name) from the header section and the fourth column of lines 16-25 from the data section. These will be the exact same every time.

I have included an example of what and where I would like the information entered and an example .asc file.

Thank you.

Scott Adams

http://dl.dropbox.com/u/98763433/Example.zip

 

[andrewkard]

Hello, try this:

   Open "file.txt" For Input As #1</SPAN></SPAN>
   Dim s As String</SPAN></SPAN>
   Line Input #1, s</SPAN></SPAN>
   MsgBox s</SPAN></SPAN>
   Close #1</SPAN></SPAN>

 

[Rick Rothstein]

Give this a try...

Sub GetASCdata()
  Dim X As Long, FileNum As Long, TotalFile As String, Lines() As String, Numbers As Variant
  FileNum = FreeFile
  Open "C:\Users\Rick\Downloads\APC - A Millspaugh 2B4  CL.0365.asc" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Lines = Split(TotalFile, vbNewLine)
  Worksheets("Data").Range("A1").Value = Mid(Lines(1), 2, Len(Lines(1)) - 2)
  ReDim Numbers(1 To 10, 1 To 1)
  For X = 1 To CLng(Lines(14))
    Numbers(X, 1) = Split(Lines(14 + X), ",")(3)
  Next
  Worksheets("Data").Range("A2:A11").Value = Numbers
End Sub

Even though you said you always wanted lines 16 to 25, I decided the 10 in line 15 actually was a count of the lines of data that followed (so that if there were more or less lines of data, code reading the file would know the count) and used that fact in my code. If that interpretation is wrong (I am willing to bet it isn't), then just replace the part of my code highlighted in red with the number 10.

 

[Jeffrey Smith]

Use andrewkard's code above: Every time you invoke the Line Input command, it brings in a new Line from the ascii file

Sub ReadASC()
  Open "J:\Excel Macros\Test ASC\test.asc" For Input As #1
  Dim s As String
  Dim X As Long
  Dim cString() As String
  
  X = 0
  Do Until EOF(1)
    Line Input #1, s
    X = X + 1
    If X = 2 Then
      MsgBox s    'This is the title
    ElseIf X > 15 And X < 26 Then
      cString() = Split(s, ",", -1)
      MsgBox cString(4)
    End If
  Loop
      
  Close #1
End Sub

 

[SAFD1450]

Brilliant Rick, works like a charm.

If you don't mind, could you give me a little breakdown of what the code is doing?

Thank you!

 

[SAFD1450]

Thank you Jeffery,

That does exactly what you say it does, but it's not quite what I need for this project. I do think it is really interesting that it brings up the results in a message box and I could see this being useful in the future.

Thank you for sharing this with me.

 

[Rick Rothstein]

If you don't mind, could you give me a little breakdown of what the code is doing?

Here is the code with comments on all the major code lines...

Sub GetASCdata()

  '  I always dimension my variables to a data type
  Dim X As Long, FileNum As Long, TotalFile As String, Lines() As String, Numbers As Variant

  '  Makes VB find the next unused file channel number to be used by the Open statement and its related statements
  FileNum = FreeFile

  '  Open a channel for the file
  Open "C:\Users\Rick\Downloads\APC - A Millspaugh 2B4  CL.0365.asc" For Binary As #FileNum

    '  Create a space buffer equal in size to the text file (LOF obtains the "Length Of File")
    TotalFile = Space(LOF(FileNum))

    '  Blast the entire file into the buffer all at once (avoids reading the file line-by-line)
    Get #FileNum, , TotalFile

  '  We have all the text, so close the channel
  Close #FileNum

  '  Windows text files use a carriage return followed by a line feed to delimit each new line in a
  '  file... vbNewLine is a predefined VB constant composed of those two characters. The Split
  '  function returns a one-dimensional array using the specified delimiter to break the text apart
  '  Note: Split always produces a zero-based array (no matter what the Option Base setting is)
  Lines = Split(TotalFile, vbNewLine)

  '  The text in the second line of your file was surrounded by quote marks which the next line removes 
  Worksheets("Data").Range("A1").Value = Mid(Lines(1), 2, Len(Lines(1)) - 2)

  '  Creat a two dimensional array whose first element is large enough to contain number from the file
  ReDim Numbers(1 To CLng(Lines(14), 1 To 1)

  '  Move the data from the (one-dimensional) Lines array to the two-dimensional Numbers array and
  '  remember, the Split function produces a zero-based array)
  For X = 1 To CLng(Lines(14))
    Numbers(X, 1) = Split(Lines(14 + X), ",")(3)
  Next

  '  Since Numbers is a two-dimensional array, it can be assigned directly to a worksheet range.
  Worksheets("Data").Range("A2:A11").Value = Numbers

End Sub

Note: Use the above code instead of the code I posted originally as I accidentally left a hard-coded 10 in the spot I highlighted in red above. The text at that location has to be the same as the text afte the "To" in the For statement below it (if you changed the text in the For statement to 10, then the ReDim text must also be set to 10).

 

[SAFD1450]

Thanks again Rick, this was exactly what I was looking for. I really appreciate the help.