AS400 to Excel download
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: AS400 to Excel download

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I've downloaded a txt file, with delimiters (";"), from AS/400 using FTP. Is there any way to convert this text file into excel sheet by executing a macro on command line or by any other means?

  2. #2
    New Member
    Join Date
    Feb 2002
    Location
    Benicia, CA
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My boss and I did this at our work. Sorry, I don't have the code for it right now because it is at work. We had it so that when you hit Conrol Shift Z it would grab the file off the desktop (the file had to be named z.txt) and bring it into Excel. It would go through the wizard thing for the Text Delimited. The first time you do this, you will need to set up where you want the columns to be. Sorry this isn't much help. I will be able to get the code tomorrow from my boss if you can't figure it out.

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Benicia, CA
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Sub GL()

    ChDir "C:WINDOWSDesktop"
    Workbooks.OpenText Filename:="C:WINDOWSDesktopz.txt", Origin:=xlWindows _
    , StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array( _
    10, 1), Array(46, 1), Array(61, 1), Array(68, 1), Array(83, 1))
    Columns("C:E").Select
    Selection.Style = "Comma"
    Columns("D:D").Select
    Selection.ColumnWidth = 2.71
    ActiveWindow.DisplayGridlines = False
    Range("A1").Select
    Columns("B:B").EntireColumn.AutoFit
    Range("B18").Select
    Columns("B:B").ColumnWidth = 37
    Range("E11").Select
    ActiveWindow.SmallScroll Down:=-15
    Range("A1").Select
    End Sub


    This is what we did to make it work. This would take the file off the Desktop and grab the file if it was named z.txt. The rest is how we formatted the columns, so you might need to mess with that but i doubt it. Depending on what you are taking from the AS400. This is what our setup was for bringing in a G/L. Hope this helps.

    Greg

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