Fixed Width - Opening Certain Fixed Width Files
I receive weekly data files from the USPS in a fixed
width format. Each file will have the exact same 23 fields defined when opened (position 1, 9, 16, 32, etc.). Is there a way to create a macro that will automatically parse the file at the correct 23 columns so this doesn't have to be done manually each time I receive a new data file? Thanks, Jan |
Fixed Width - Opening Certain Fixed Width Files
Dave,
Worked just great. Thanks so much! Jan -----Original Message----- (From a previous post--so don't trust the column layout!) ========= Start a new workbook. Tools|macro|Record new macro Do all your importing and reformatting. Include all the things you like (worksheet headers, print headers/footers/freeze panes/Data|Filter|Autofilter) Stop recording and save that workbook with the recorded code in it. Put a giant button from the forms toolbar on the first worksheet in that "importer" workbook. Assign your macro to the button. You'll probably have to adjust the code a little to make it more generic. When you recorded your macro, you got something that looked like: Option Explicit Sub Macro1() Workbooks.OpenText Filename:="C:\myfile.txt", Origin:=437, StartRow:=1, _ DataType:=xlFixedWidth, FieldInfo:=Array(Array (0, 1), Array(15, 1), _ Array(41, 1)) End Sub Well, instead of having your filename in the code, you can give the user a chance to pick it themselves (take a look at getopenfilename in VBA's help): Sub macro1A() Dim myFileName As Variant myFileName = Application.GetOpenFilename (filefilter:="Text Files, *.Txt", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.OpenText Filename:=myFileName '....rest of recorded code here! End Sub Jan wrote: I receive weekly data files from the USPS in a fixed width format. Each file will have the exact same 23 fields defined when opened (position 1, 9, 16, 32, etc.). Is there a way to create a macro that will automatically parse the file at the correct 23 columns so this doesn't have to be done manually each time I receive a new data file? Thanks, Jan -- Dave Peterson . |
All times are GMT +1. The time now is 10:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com