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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
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!
 

mrxwantobe

Board Regular
Joined
May 2, 2002
Messages
158
are you saying to replace that whole line or just the part in the quotes
Workbooks.OpenText Filename:="C:\test.txt",
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

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
Top