Bloody impossible? or just plain difficult?!
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Bloody impossible? or just plain difficult?!

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi everyone,

    Got a bit of a tricky one here. Or at it's stumped me anyway!

    I need to create some code to import a certain file type exported from another package into Excel.

    The file is called a sequential file. It is basically a text file, and the data in it looks like this:

    1/4
    +1
    Field1
    Field2
    Field3
    Field4
    +2
    Field1
    Field2
    Field3
    Field4

    etc.

    I need to import files of this type and put the record id number (the bit following each +) into column A and then in the columns that follow, the fields in order, so each record gets one row each. I can ignore the very first line as this just descibes the number of fields.

    Of course, everything it variable. The first line will say how many fields are in the file, but instead of saying 1/4 (meaning fields are 1, 2, 3 and 4) it could say 5/8, or even 1,3,7,9 etc. Also, the files created could have anything up to 9999 fields (in which case you can't do it anyway coz Excel only has 256 columns but I'd like to be able to do it for up to this level)

    The way I have been doing this in the past is to use another program (e.g. Word) and replacing a carriage return with a tab and then tab+ with carriage return+ which creates a tab delimited file which obviously Excel likes! I'd like a more refined way of doing things though.

    I could of course just open the file in Excel and then run a macro to move the records into rows. However, the macros I've written in the past to do this take bloody ages to run if there are a lot of records.
    Also, with the file format I'm dealing with, if I had a sequential file with 30,000 records with 100 fields in each, this is in theory something that would fit in Excel once the records are one per row, but it means my sequential file actually has 3 million lines in it which I can't open in Excel in the first place!
    This is why I'd like some way of loading the file as it is opened.

    Am I making sense here?
    If I'm not being clear anywhere, please say so.

    Many many thanks in advance.
    Would be an enormously useful tool for me to have.

    Rgds
    AJ



  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    I think I know the theory now, having just found the following example in VBA help...

    Dim TextLine
    Open "TESTFILE" For Input As #1 ' Open file.
    Do While Not EOF(1) ' Loop until end of file.
    Line Input #1, TextLine ' Read line into variable.
    Debug.Print TextLine ' Print to the Immediate window.
    Loop
    Close #1 ' Close file.

    This is all well and good, but can someone tell me how to print the variable to the worksheet instead of the Immediate window?

    ****ed if I know!

    Many thanks
    AJ

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dim TextLine
    x=0
    Open "TESTFILE" For Input As #1 ' Open file.
    Do While Not EOF(1) ' Loop until end of file.
    x=x+1
    Line Input #1, TextLine ' Read line into variable.
    activesheet.cells(x,1).value=textline
    Debug.Print TextLine ' Print to the Immediate window.
    Loop
    Close #1 ' Close file.

    regards Tommy

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Doh! Of course!
    Thanks Tommy for helping a stupid bugger on a Friday afternoon!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com