asked on

Create Excel formatted text file with generated text as file name in a macro

I have >100 excel files to save as .txt files and this field changes in each one so I'm trying to create a macro to do it and use the field in the file name...

This is an example of the data I have:

A1 = 01234
A2 = ABCDE
in A3 is =CONCATENATE(A1,A2) giving 01234ABCDE
I want to be able to save this file as 01234ABCDE.txt (text file TAB delimited)
where 01234 is different every time.

I tried to copy the concatenated field to a text field using paste special:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

and then tried copying the value to the save box but it gave me the same value again and again.
ActiveWorkbook.SaveAs Filename:="c:\01234ABCDE.txt", FileFormat:=xlText, CreateBackup:=False
I want it to look at A3.

Stuck and hope you can help please :-/

ASKER CERTIFIED SOLUTION

TimCottee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mellissa

ASKER

Thank you,
Tried, tested and works a treat

It always looks easy when someone else shows you how.

.

DocM

Sub SaveFile()
Path = "c:\"

strfilename = Path & Range("A3").Value & ".txt"

While Dir(strfilename) <> ""

Number = Range("A1").Value + 1
Number = Format(Number, "00000")
Range("A1").Value = "'" & CStr(Number)

strfilename = Path & Range("A3").Value & ".txt"
Wend

ActiveWorkbook.SaveAs Filename:=strfilename, FileFormat:=xlText, _
CreateBackup:=False

End Sub