Excel/Excel Macro To Edit Layout + Save To New Worksheet
I am looking for help on an issue.
I'm pretty new to Excel/macros.
Currently I have an excel file containing 1 sheet that looks like this.
I have been asked to do the follow.
The file our process picked up from [Removed Directory] contained:
(**Data from spreadsheet pasted in a .txt file)
021030879 CIF T992000927 AFE
022774509 CIF METAFLAKE AFE
023478761 CIF T993001236 AFE
025713660 NULL 5473539361133832 CCC
055607444 CIF T025000110 AFE
056197015 CIF T316000006002 AFE
056530934 CIF T028000054001 AFE
52550191 NULL 052833639 CIF
There are a few things wrong with this file.
1 - No header record which should contain row count and checksum
2 - It is meant to be comma delimited but no commas in the file.
3 - Source system values shown as NULL when should be CIF in this case.
I have created a file that will process and looks like:
Should not have the NULL values in source but format correct.
The original spreadsheet had a way of generating the checksum in each row (field 5) and is summed up in field 2 of header. I just added any number to get it to work.
The first field of the header contains the row count of the rows in the file.
The code stops processing when the checksum in field 5 is zero indicating a blank line.
This is what I have so far
I've never used VB/written macros.
I have the code to pull all information into 1 cell and seperate them with a comma.
I'm also ok with counting the string length/number of rows.
However the end goal is to have a macro which should work like this.
-User opens up excel file (the one not formatted correctly)
-User goes to Tools->Macros and runs the macro.
-The macro styles all the information on the sheet the way it's required and then saves it as a new file.
I know this is a bit of a broad request but I appreciate any help you could offer.
I have tried for the last 2 days to do it on my own but now I feel my lack of knowledge is just wasting time.
Many thanks again in advance.
process sounds fairly straightforward - IF the file is text, is it fixed length? If so, no need to reformat as csv - I'd suggest almost that the macro asks for the file to be reformatted,then reads that (probably as text) outputting the header row first, then storing the information read from the file into rows 2 onwards. If it finds a value of NULL it should instead store CIF.
is it possible to see a sample of the file (email is email@example.com)