Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed Width - Opening Certain Fixed Width Files
(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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fixed Column Width Export | Excel Discussion (Misc queries) | |||
Column fixed width | Excel Discussion (Misc queries) | |||
Fixed width fonts | Excel Discussion (Misc queries) | |||
Software to download Fixed Width Files | Excel Discussion (Misc queries) | |||
How do I create a "fixed-width" datafile? | Excel Discussion (Misc queries) |