import fixed width files

mrxwantobe

Board Regular
Joined
May 2, 2002
Messages
158
I have to import the same format fixed width file all the time. It is 42 fields wide and i have to use the import wizard to separate the fields because Excel doesn't recognize about half the fields. Is there any way to have Excel remember the cells for this one format? Thanks in advance.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Open a blank file, turn on the macro recorder and record your steps of importing the file. Now save the original blank file. You can then run this macro in the future to open the file.

If the file name will be the same every time, you are all set. If it is different, we can add more generic VB code to ask the user for the name of the file to import.
 
Upvote 0
In your recorderd macro, you should have something that looks like this:
Code:
   Workbooks.OpenText Filename:="C:\test.txt", ...

with whatever filename you are importing is. The ... represents there are more details that follow, depending on your import selections.

Simply replace that portion of code with something like this:
Code:
'   Enter drive and directory you want open dialog box to default to:
    ChDrive "C:\"
    ChDir "C:\Users\"
    myfile = Application.GetOpenFilename("All Files,*.*")
    If myfile = False Then
        Exit Sub
    End If
    Application.DisplayAlerts = False
    Workbooks.OpenText _
        Filename:=myfile, ...

    Application.DisplayAlerts = False

That should do it!
 
Upvote 0
are you saying to replace that whole line or just the part in the quotes
Workbooks.OpenText Filename:="C:\test.txt",
 
Upvote 0
Replace the "Workbooks.OpenText" line up to the file name (not the specs) with the whole block of code I posted EXCEPT the last line (Application.DisplayAlerts = False).

Make sure the specs are attached to the line after "Filename:=myfile, ". Paste the "Application.DisplayAlerts = False" after all the code importing the file.

If you have any trouble, post your code, and we can clean it up.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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