Macro/VBA to Read ASC file

SAFD1450

New Member
Joined
Sep 10, 2012
Messages
4
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

andrewkard

Active Member
Joined
Apr 6, 2012
Messages
455
Hello, try this:
Code:
   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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,026
Office Version
2010
Platform
Windows
Give this a try...

Rich (BB code):
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

Well-known Member
Joined
Feb 11, 2005
Messages
795
Use andrewkard's code above: Every time you invoke the Line Input command, it brings in a new Line from the ascii file

Code:
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

New Member
Joined
Sep 10, 2012
Messages
4
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

New Member
Joined
Sep 10, 2012
Messages
4
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,026
Office Version
2010
Platform
Windows
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...

Rich (BB code):
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

New Member
Joined
Sep 10, 2012
Messages
4
Thanks again Rick, this was exactly what I was looking for. I really appreciate the help.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,810
Messages
5,483,045
Members
407,375
Latest member
achusp

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top