Command word from excel

Hi Andy,

Thanks. And as you correctly pointed out, in Excel is sends each new carriage return to a new row. What I was hoping for was to allow the contents to all go into one cell. When I recorded what I wanted using the vb recorder, I got this code:

Please Login or Register  to view this content.

Of course I typed this directly into excel but I think it will better illustrate what I am after.

Thanks for your help.

Abousetta

Hi Andy,

Sorry I have not responded sooner but I was off the grid for a while. Char(10) seems to work as you suggested and I finally got it working and tweeked the code just a bit to fit with the rest of the project. Even so I still have a few questions I hope you can help me with:

Here is the code as it stands right now:

Please Login or Register  to view this content.

The changes so far are minor as I just added Application.GetOpenFilename and Application.GetSaveAsFilename and changed the headers to represent what truly comes from the database. All the headers are a fixed length of six characters, spaces or numbers. In the example above I used "AB - " for the first header and "** - " for the second header. Is there any way for me to modify the code that I don't have to put down all the possibilities. The reason I ask this is because I noticed that the code will replace all the return carriages until it finds the stopping header. What I have found is that this stopping header (i.e. the next header) may be different from one file to the next.

The other question I have is when I try to save the new output file as the same name as the original file, it gives me error at this line of code:

Please Login or Register  to view this content.

Is there anything I can do to allow me to overwrite the original file?

Thanks for all your help.

Abousetta

Thanks Andy. I will try to add the KILL and NAME commands and ask for advice if I can't make it work.

As for the second part, thanks for modifying the code above, I will try it out and let you know.

Thanks for all your help and advise.

Abousetta

Hi Andy,

I am trying to make the code work when there are different headers but I just can't seem to make it happen.

The headers are all constant in the sense that they all consist of 6 characters (with spaces), of which the last two are always a dash followed by a space (e.g. "AB - ", "XY -", "A1 - "). Each header represents something differernt, but in the end the all must be the same length. Is there anyway to use wildcards to replace the characters (e.g. ** or ??) so that the macro will run until it finds the next header. I have tried but all it does is erase the rest of the document. Also, unfortunaley the number of lines is variable based on original user input and so I cannot determine this a priori.

Thanks for all your help.

Abousetta

P.S. I have modified the code below to work with the attached example.

Please Login or Register  to view this content.

Thanks Andy... It seems to work perfectly this time around.

Best wishes,

abousetta