By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,704 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,704 IT Pros & Developers. It's quick & easy.

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

P: 10
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:=True

Dim wbResults As 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!
Aug 7 '08 #1
Share this Question
Share on Google+
10 Replies

Expert Mod 15k+
P: 31,656
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 :
Expand|Select|Wrap|Line Numbers
  1. FROM : Length=2"      TO : "Length=2"""
  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)(*.prn)". Unfortunately this doesn't use tabs as separators but only spaces.

Hope this all helps.
Aug 7 '08 #2

Expert Mod 15k+
P: 31,656
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 :)
Aug 7 '08 #3

P: 10

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:
Expand|Select|Wrap|Line Numbers
  1. ActiveWorkbook.SaveAs FileFormat:=xlTextPrinter
  2. ActiveWorkbook.Close SaveChanges:=True
Aug 7 '08 #4

Expert Mod 15k+
P: 31,656
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 :
Expand|Select|Wrap|Line Numbers
  1. ActiveWorkbook.SaveAs Filename:="...", FileFormat:=xlText, AddToMRU:=False
  2. 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 :)
Aug 7 '08 #5

P: 10
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)
Expand|Select|Wrap|Line Numbers
  1. ActiveWorkbook.SaveAs Filename:="...", FileFormat:=xlText, AddToMRU:=False
  2. ActiveWorkbook.Close SaveChanges:=False
Thanks for all your help!
Aug 7 '08 #6

Expert Mod 15k+
P: 31,656

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"?
Expand|Select|Wrap|Line Numbers
  1. Dim strName As String
  2. ...
  3. With ActiveWorkbook
  4.   strName = Replace(.FullName, ".xls", ".Txt")
  5.   .SaveAs Filename:=strName, FileFormat:=xlText, AddToMRU:=False
  6.   .Close SaveChanges:=False
  7. End With
Aug 7 '08 #7

P: 10
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.

Expand|Select|Wrap|Line Numbers
  1. Dim f As Long
  2. f = f + 1
  3. ActiveWorkbook.SaveAs Filename:=f, FileFormat:=xlText, AddToMRU:=False
  4. 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?

Aug 7 '08 #8

Expert Mod 15k+
P: 31,656
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?
Aug 7 '08 #9

P: 10
Yes, that makes sense.

Thanks for all your help with this, I really appreciate your time and knowledge.
Aug 7 '08 #10

Expert Mod 15k+
P: 31,656
No worries - It's been fun :)
Aug 7 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.