VBA Macro to save tab as .txt file

[cchantler]

Hi all

I am trying to achieve the following:

I have a macro enabled spreadsheet that contains 3 separate tabs, each of which I want to save as a .txt file
I want to do this by clicking a single button
I want the text files to be saved in the same folder as the parent excel file, and be named [EXCEL FILE NAME]_[PRE DEFINED TEXT].txt
I also want to do this in a way that does not modify the original excel workbook

I am sure this can be done with a button / macro / VBA, I have scoured internet discussion boards but I simply cannot work out how to do it

Any help would be greatly appreciated
Thanks
Chris

[PaulSP8]

Hi Chris

Welcome to the Forum

Should be doable, what are you basing the [PRE DEFINED TEXT] on? A cell reference or something else?

Is it a straight Text file you want to save to or a delimited one?

[BellyGas]

Chris,

simple learning exercise for you: Use the macro recorder to record yourself saving all 3 sheets as a .txt file. Then post the resulting mess of code here and we'll clean it up for you and tell you what we've done to clean it up so you can do the same next time you want some code. Slowly but surely you'll be learning VBA.

[cchantler]

Hi Paul

Thanks for reply
The pre defined text can be set. For example the tabs will be called:
"opts_list"
"DQ_Cpd_list_
"DQ_Batch_file"
or something like that so I just need to concatenate the original XL file name to this to name the text file, for example
[ORIGINAL XL FILE NAME]_opts_list.txt
etc etc

I have been manually saving as tab delimited files, so I'd probably go for these

Thanks a lot
Chris

[cchantler]

Hi, thanks for reply !

I tried to do this initially.
I seem to be having problems in that in saving the file/tab as a txt file

When I go back to the original XL file (after saving the tabs) the macro appears to not have saved in this sheet

Thanks
Chris

[PaulSP8]

Hey Chris

So it's based on the sheet name?

In that case try;

Please Login or Register  to view this content.

[cchantler]

Hi Paul

Its appears to toggle through the tabs (so appears to be doing something)
But when I look in the folder the parent XL sheet is stored in, I see no text files

Thanks a lot
Chris

[PaulSP8]

Weird, "works on my machine"

Are you trying to run it from a network drive?

[PaulSP8]

If it is a Network drive, then try this...

Please Login or Register  to view this content.

[cchantler]

That worked, thankyou so much !

Chris