Create Excel formatted text file with generated text as file name in a macro
Posted on 2003-03-18
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 :-/