AS400 to Excel download

KaushikV

New Member
Joined
Feb 25, 2002
Messages
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?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top