Saving .xls file as .txt (VBA Excel 03')

[N2Deep]

I have a macro that does a few things to an Excel workbook and then saves, and closes to an Excel workbook. I now want to save, close the Excel workbook as a text (tab delimited .txt) file.

Currently I am closing/saving the file near the end of my Sub with:

wbResults.Close SaveChanges:=Tr ue

Dim wbResults As Workbook...at the beggining of my sub.

I've tried many things and can't seem to make the change or get it to work.

Thanks in advance for any help on this!

[NeoPa]

Strangely, I've just had to do something very similar myself (if I understand you correctly of course).

There are a few issues to consider when saving data away as text from within a workbook which also contains runnable code. The principal one of which is that the Save As ==> Text part should never really be done from that file (It can be done that way but it does introduce extra complications). Instead transfer the data across to a new spreadsheet and save that away as text.

Another thing to bear in mind is that Tab Delimited format (xlText) will treat any item with commas (,) or double-quotes (") embedded within it, as requiring modification.

EG. The following translations occur :

FROM : Length=2"      TO : "Length=2"""
FROM : A,B            TO : "A,B"

This may be entirely consistent with what you require, but if not, the only text format I found which DIDN'T do this was xlTextPrinter or "Formatted Text (Space delimited)(*.pr n)". Unfortunately this doesn't use tabs as separators but only spaces.

Hope this all helps.

[NeoPa]

I should add, when working with foreign workbooks (a workbook which is different from the one the code is running from) then remember that ActiveWorkbook refers to the one you're currently working in, but ThisWorkbook refers to the one the code is running from.

Have fun :)

[N2Deep]

NeoPa,

Thank you very much for you help, I appreciate your style of teaching which has been helping me to learn.

I understand everything you are saying in your posts on this thread.

I've tried unsuccessfully many times now since last night. I've tinkered around with these last few lines, but just can't get it to work.

Here is where I'm at:

ActiveWorkbook.SaveAs FileFormat:=xlTextPrinter
ActiveWorkbook.Close SaveChanges:=True

[NeoPa]

Well, first of all, this should be data created in a New workbook. Executing the code on the ThisWorkbook, will cause you grief.

Assuming you have that under control try the following :

ActiveWorkbook.SaveAs Filename:="...", FileFormat:=xlText, AddToMRU:=False
ActiveWorkbook.Close SaveChanges:=False

The important thing you left out was the FileName in the SaveAs.

PS. Remember the xlTextPrinter was for space delimited files - not tab delimited.
Also, when the SaveAs works properly, the Close must be WITHOUT saving of course ;)

Hope that all helps :)

[N2Deep]

This Macro is opening up an excel spreadsheet in a folder, making changes, and then saving it, closing it, and then moving to the next file.

For Filename I don't want to specify (I think you say hardcode) or else when it runs the second file it will have the same name, and that will obviously cause problems. Can I save it with the name it had when it was opened?

I'm just not sure what to put in the "..." below to do that?

I've tried ActiveWorkbook, wbResults (Dim wbResults As Workbook at the beginning of my Sub)

ActiveWorkbook.SaveAs Filename:="...", FileFormat:=xlText, AddToMRU:=False
ActiveWorkbook.Close SaveChanges:=False

Thanks for all your help!

[NeoPa]

OK.

That means that you have the current name of the workbook already available to you (ActiveWorkbook .FullName). Can I assume that you would like to save the file, in your Tab Delimited form, as the same name except the extension should be changed to ".Txt"?

Dim strName As String
...
With ActiveWorkbook
  strName = Replace(.FullName, ".xls", ".Txt")
  .SaveAs Filename:=strName, FileFormat:=xlText, AddToMRU:=False
  .Close SaveChanges:=False
End With

[N2Deep]

You are correct same name .txt instead of .xls.

It worked perfectly thanks!

However, before your reply was posted I was playing with naming the files 1.txt, 2.txt, etc for as many files that run through the macro.

Dim f As Long
f = f + 1
ActiveWorkbook.SaveAs Filename:=f, FileFormat:=xlText, AddToMRU:=False
ActiveWorkbook.Close SaveChanges:=False

As it ran, I saw in the toolbar it changing the file names to 1.txt, 2.txt, etc. But when it was done and I looked in the folder and it had made no changes. I was just curious if I was doing something wrong with the f variable?

Thanks!

[NeoPa]

I suspect this is due to the lack of specification of the folder.

You may well find a bunch of files in some other folder you weren't expecting them in.

My version includes the full path in the name and it always matches the folder the .Xls file was opened from.

Does that make sense?

[N2Deep]

Yes, that makes sense.

Thanks for all your help with this, I really appreciate your time and knowledge.

[NeoPa]

No worries - It's been fun :)