Slightly daunted by the following task ...but hopefully you may be able steer me in the right direction...
I have been asked to see if it's possible to create an export tool to convert a spreadsheet into User definable 'flat file' .
The premise being-
WkBk1 has the 'tool' and the 'data' is in a customer's Workbooks which contain basically the same info, just with different headers / layout ...I'll call this Wkbk2.
What I envisage –
In WkBk1 the User clicks on a button to open a user form
The Userform will have a text box for user to put path and file name of where WkBk2 is ....and preferably a BROWSE button to open a dialog..
A textbox to indicate which sheet info is in
There should then be a tick box to say if "First Line contains headers"
Then a radio button to choose Use previous template OR
to choose new USER defined
On User defined,
Click on radio button to determine if Fixed Width or Delimited …on Delimited a textbox becomes visible for user to input delimiter ….like a comma, or semi-colon etc. ( might need to indicate a defined range with a case structure to validate)
A combobox (?) is populated with all WkBk2's headers or Column letters if tickbox for "First line contains headers" is not ticked.
Another combobox (?) has our pre-defined list to match against.
If User has already chosen Fixed width, there are then three text boxes call START LENGTH and END
The idea is User chooses one from User, one from Ours, put the start position, length of field and end position. (Positions will have defaults)
On completion of these five boxes, they would click an “Add to template” button. Then they pick the next field…etc….It would be good to see these build below / what hasn’t been mapped yet (mandatory fields)
Once complete they click on Create Template and they can name their Template which becomes a the name of a new sheet in WkBk2 (details are recorded in sheet …some how)
This adds also to the Use previous Template at the beginning.
User asked if they want to run now, If no cancel true, if yes then they choose what they want the file to be called and where (back to a text box for name and path OR Browse option.)
Then records are mapped.
File name will be something like C:\Customer1.txt
As I said daunting …but any thoughts to push this in the right direction appreciated.
There is an attached picture of a suggested layout that will hopefully help you picture the above (and if you’ve read this then thanks for your time and patience!!!)