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?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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?
 
Upvote 0
Yes.

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

How do you want to specify the filename?
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Why not just send the workbook as an attachment?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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