Fixed-Length .dat file Automatically Parsing

technochik

New Member
Joined
Dec 2, 2005
Messages
15
I have a fixed length .dat external file with no column headings. I know I can import the file into Excel manually via the Text Import Wizard (where I manually specify the field widths of each column...using a printed file layout as reference). I want to automate this though. Is there a way to import the file automatically parsing into standard file layout?

Basically I don't want to have to reformat these files for an end-user every week (the Text Import Wizard is too advanced for them). I am hoping to setup a template, macro or hidden VB script that will reformat this file into the pre-defined columns and add headings. Can this be done?
 

Some videos you may like

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.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,052
Office Version
  1. 365
Platform
  1. Windows
The best way to go about this would be to record a macro (Tools>Macro>Record New Macro...) when you do it manually.

This should generate code that can be adapted to do exactly what you want.

The code could be assigned to a button for the end user to just push.
 

technochik

New Member
Joined
Dec 2, 2005
Messages
15
Thanks, that worked great! One issue though. The name of the file changes (it's always in the same folder though). Is there a way to accomodate different file names but still use the button attached to a macro?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,052
Office Version
  1. 365
Platform
  1. Windows
Yes.

That's what I meant when I said you should adapt the code.

How do you want to specify the filename?
 

technochik

New Member
Joined
Dec 2, 2005
Messages
15

ADVERTISEMENT

Yes, I figured you meant going in and editing the code generated by the macro but I don't know what code is needed.

A 3rd party product creates 3 files which are FTP'd into this directory with different file names each week. For example, one file was crc_final_lebo_d112805.dat this week and crc_final_lebo_d112105.dat last week.

Can you recommend a way that will not be too confusing to the user?
Thanks!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,052
Office Version
  1. 365
Platform
  1. Windows
Well would you like the user to be able to choose the file?

If so then you could use the GetOpenFileName method.

Otherwise you'll need to create code that constructs the filename based on the date or whatever.
 

technochik

New Member
Joined
Dec 2, 2005
Messages
15

ADVERTISEMENT

The GetOpenFileName is exactly what I needed!! It works perfectly for all 3 files now!!! Thank you so much!

Is there an easy way to share the macro with the users via email?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,052
Office Version
  1. 365
Platform
  1. Windows
Why not just send the workbook as an attachment?
 

technochik

New Member
Joined
Dec 2, 2005
Messages
15
I just created a macro and linked it to a button on the toolbar. If I send them the excel document where I saved the macro, it won't show a button on the toolbar automatically for them, will it?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,052
Office Version
  1. 365
Platform
  1. Windows
Why not write code that creates (and deletes) the command button on the toobar?

Again you can use the macro recorder for this when you goto Customize...

The code to create the button could go in the Open event of the workbook and the code to delete it in the BeforeClose event.

If there is an issue about other workbooks being open at the same time then use the Activate/Deactivate workbook events.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,285
Messages
5,571,310
Members
412,381
Latest member
RogerL
Top