AS400 to Excel download

[KaushikV]

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?

 

[greg0226]

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.

 

[greg0226]

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