Extract specific text from cell to save file

Hi all,

I get a CSV file which I format and want to save as an Excel file.

In Cell A3 is always "Period : 02/06/2008 - 09/02/2008 " (Note final space as well)

I have been messing with code to extract various bits from the above as I want to save the file as:

Path & 2008 Jun 02 - 09.xls)

Please Login or Register  to view this content.

Firstly can anyone tell me how to get the dtStartDay out of that cell and secondly am I going about this whole thing the right way or is there an easier (more elegant) way to achieve this.

Many thanks as always

Seamus

Hi Seamus,

I'm puzzled as to where you get the text 'Jun' from.

Was that a typo in the second date element and you meant '09/06/2008' not '09/02/2008'?

i.e. the month (06=June) is indeed the central characters in the two date elements.

Rgds

Hi Richard,

Yes it was a typo - I meant it to say:

"Period : 02/06/2008 - 09/06/2008 "

Any help would be appreciated

Thanks

Seamus

Hi,

Assuming cell B3 is blank and which can be used to store the full Path/File Name string, then one way is the following

Please Login or Register  to view this content.

HTH

Hi Richard

Fantastic - thanks very much.

I just had to tweak one line to give me the exact desired answer.

Your line of

Please Login or Register  to view this content.

gave me a #VALUE error so I changed it to

Please Login or Register  to view this content.

and that was perfect.

Thanks again

Seamus

Thanks for the feedback Seamus.

Yes that was an obvious typo of mine - well spotted.

Regards

Hi Richard,

A bit of further help would be appreciated. When I use the code to get the filename and then invoke the FileSaveAs dialog box to let the user choose the path, the file name appears in the dialog box with "" quotes round it. Is there any way of not having quotes around the name so that the user just chooses the path and saves the workbook.

Many thanks

Seamus

Hi Seamus

Try the following modification. It prompts the user to choose the path/folder then saves the whole thing.

Please Login or Register  to view this content.

Rgds

Richard,

At home now and have left the file at work but will add your code tomorrow and report back.

Thanks again

Seamus