1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Rep Power

    Post Programming in Excel

    Hello all,
    I have a JCAMP file(basically like a text document). Somewhere down the file is a long list of numbers divided into four columns. I need to be able to organize all of those numbers into one column (left to right across the row, and then down to the next row, in that order) in an Excel worksheet. Can anyone help me out? Thanks.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Rep Power
    I don't need anyone to take me step by step to do the program. I just need to know how to write from the file to the excel worksheet in a specific cell number. Also how to pick out where to start reading from (its after a certain word). Thanks.
  4. #3
  5. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Somewhere in the great unknown
    Rep Power
    Assuming you know how to open up your vba editor so I'll skip that part.
    Create a subroutine and call it whatever you want, we'll call it test for a visual explanation.
    Then create a variable to attach to the sheet that you want, lets say the first sheet.
    Then you can access the cells of the worksheet through the Cells(row, column) attribute.

    To open a file for reading use the open keyword.
    Use line input to get read the file line by line and once you encounter the word that you are looking for start recording the contents of the file into the worksheet. The text that is in red is that which will depend on you. For the most part, this is the basic logic that you need for the code.
    Private Sub test()
        Dim theSheet As Worksheet
        Dim iFile As Integer
        Dim sLine As String
        Dim bRecord As Boolean
        Dim lRowCntr As Long
        'set theSheet equal to the sheet
        Set theSheet = Application.ActiveWorkbook.Sheets(1)
        'get the next free file handle
        iFile = FreeFile
        lRowCntr = 1
        'open the file
        Open "yourfile.txt" For Input As #iFile
        While Not EOF(iFile)
            Line Input #iFile, sLine
            If Trim(sLine) = "your phrase" Then bRecord = True
            If bRecord Then
                theSheet.Cells(lRowCntr, 1) = Trim(sLine)
                lRowCntr = lRowCntr + 1
            End If
        Close #iFile
    End Sub

IMN logo majestic logo threadwatch logo seochat tools logo