delphi programming forums mysql charset mget recursive synonimos
free ventrilo servers hosting cs javascript delay python find in list
Back Forum New

Rank: 1
1# Jump to »
M@dMaNPost at 2011-3-11 12:16 | Show This Author's Posts Only
abstract:

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.


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.

TOP

Rank: 1
2#
M@dMaNPost at 2011-3-11 12:16 | Show This Author's Posts Only
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.

TOP

Rank: 1
3#
OnslaughtPost at 2011-3-11 12:16 | Show This Author's Posts Only
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.
Code:
  1. Private Sub
  2. test()
  3. Dim
  4. theSheet
  5. As
  6. Worksheet
  7. Dim
  8. iFile
  9. As Integer
  10. Dim
  11. sLine
  12. As String
  13. Dim
  14. bRecord
  15. As Boolean
  16. Dim
  17. lRowCntr
  18. As Long
  19. 'set theSheet equal to the sheet
  20. Set
  21. theSheet = Application.ActiveWorkbook.Sheets(1)
  22. 'get the next free file handle
  23.     iFile = FreeFile
  24.     lRowCntr = 1
  25. 'open the file
  26. Open
  27. "
  28. yourfile.txt
  29. "
  30. For Input As
  31. #iFile
  32. While Not
  33. EOF(iFile)
  34. Line Input
  35. #iFile, sLine
  36. If
  37. Trim(sLine) = "
  38. your phrase
  39. "
  40. Then
  41. bRecord =
  42. True
  43. If
  44. bRecord
  45. Then
  46.             theSheet.Cells(lRowCntr, 1) = Trim(sLine)
  47.             lRowCntr = lRowCntr + 1
  48. End If
  49.     Wend
  50.     Close
  51. #iFile
  52. End Sub
Copy Code




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.

TOP

Back Forum