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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
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,344
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,344
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,344
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,344
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,052
Messages
5,835,148
Members
430,342
Latest member
Sailingexcel

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