AS400 to Excel download

KaushikV · Feb 26th, 2002, 08:15 PM

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 · Feb 26th, 2002, 08:19 PM

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 · Feb 27th, 2002, 10:05 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 26th, 2002, 08:15 PM	#1
KaushikV New Member Join Date: Feb 2002 Posts: 1	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?

Feb 26th, 2002, 08:19 PM	#2
greg0226 New Member Join Date: Feb 2002 Location: Benicia, CA Posts: 31	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.

Feb 27th, 2002, 10:05 AM	#3
greg0226 New Member Join Date: Feb 2002 Location: Benicia, CA Posts: 31	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