Save sheet as .TXT out of Workbook

[goepf]

I would like to save a sheet as .TXT out of a workbook. This works fine
but if:

I have several workbooks open and I switched between the workbooks by
minimizing them (in stead of using the menu "window") the code doesn't
work properly because the copied sheet is not the active sheet and the
code will save the original workbook as a .TXT...

code:
Sheets("BESPRO MT").Select 'sheet to copy and save as .txt
Sheets("BESPRO MT").Copy
DLSfile = "DL72112.txt"
ActiveWorkbook.SaveAs FileName:=path & DLSfile, FileFormat:=xlText,
CreateBackup:=False
ActiveWindow.Close 'should close dl72112.txt but see problem above...
Windows(FileName).Activate
Sheets("BESPRO MT").Delete


I tried with maximize window and also with activate but it didn't
work...

any idea?

 

[losmac]

Is that workbook You are trying to copy as txt file have a
name? Yes, so:

Workbooks(name).SaveAs FileName:=path & DLSfile,
FileFormat:=xlText,CreateBackup:=False
Workbooks(name).Close

Always set context to work with workbooks!

-----Original Message-----

I would like to save a sheet as .TXT out of a workbook. This works fine
but if:

I have several workbooks open and I switched between the workbooks by
minimizing them (in stead of using the menu "window") the code doesn't
work properly because the copied sheet is not the active sheet and the
code will save the original workbook as a .TXT...

code:
Sheets("BESPRO MT").Select 'sheet to copy and save as .txt
Sheets("BESPRO MT").Copy
DLSfile = "DL72112.txt"
ActiveWorkbook.SaveAs FileName:=path & DLSfile, FileFormat:=xlText,
CreateBackup:=False
ActiveWindow.Close 'should close dl72112.txt but see problem above...
Windows(FileName).Activate
Sheets("BESPRO MT").Delete


I tried with maximize window and also with activate but it didn't
work...

any idea?


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step

guide to creating financial statements

 

[goepf]

Thanks but...

I only save 1 sheet out of the workbook and then I'd like to close tha
new sheet/file and go back to my workbook...

 

[goepf]

could anybody give me a hand here? I would really appreciate it!

Thanks ;-))

 

[Dave Peterson]

Instead of using Windows, how about using Workbooks?

Option Explicit
Sub testme01()

Dim DLSFile As String
Dim myPath As String

DLSFile = "DL72112.txt"
myPath = ThisWorkbook.Path & "\"

Worksheets("BESPRO MT").Copy

With ActiveWorkbook
.SaveAs Filename:=myPath & DLSFile, _
FileFormat:=xlText, CreateBackup:=False
.Close savechanges:=False
End With

End Sub

I wasn't sure what Path was, but I don't like using a VBA keyword as a
variable. So I changed it to myPath and set it equal to the path of the
workbook containing the macro.

 

[goepf]

Thanks but...

This still doesn't work. This code creates the same problem! Meaning if
I change between my Excel workbooks with "minimize window" (minimize
all the windows and then choose the one I like and maximize it again)
in stead of using the menu "window" this code saves my initial workbook
as a txt file and not the sheet (BESPRO MT) I copied!! very strange!!
It looks like the active window is not the one that I create with my
copy (eventhough it is when not using that minimize / maximize!!)

conclusion:

HELP

 

[Dave Peterson]

The code copied the worksheet to a new workbook, saved that new workbook and
closed that new workbook.

I'm not quite sure how you could choose this workbook without opening it again.

Ps. The code was placed in the original workbook and that original workbook was
active when I ran the code.

If that isn't the case, then you'll have to fully qualify this reference:

Worksheets("BESPRO MT").Copy

Something like:
Workbooks("myworkbookname.xls").Worksheets("BESPRO MT").Copy

:More confused:

 

[goepf]

Dave,

first thanks for your help!

I'll try to explain the problem again. The code did work for me IF:

only one workbook is open (the one that I use to copy the sheet from)
or more workbooks are open but they are all "maximized". Try once the
following:

open 2 different WB (one with the code) and minimize both WB with the
little button on the top right (_) and then you should have the two
windows at the bottom of your excel screen (minimized). Then maximize
the one with the code and run the code...

What happens with my excel (2000) is that the sheets gets copied but it
saves the initial workbook as a txt and not the new sheet...

Let me know what you get...

Cheers,

 

[Dave Peterson]

Ouch. That looks ugly to me.

When I stepped through the code, right after the .copy line, I went back to
excel.

The newly copied worksheet was maximized, but there was a little title bar at
the bottom of the screen that had focus. That was the original workbook--and it
was active!

(I've never seen this and it looks like a bug to me.)

I think that the safest thing to do would be resize each window to make it more
than minimized.

Option Explicit
Sub testme01()

Dim wkbk As Workbook
Dim DLSFile As String
Dim myPath As String
Dim myWindow As Window

DLSFile = "DL72112.txt"
myPath = ThisWorkbook.Path & "\"

Set wkbk = ThisWorkbook ' or activeworkbook????

For Each myWindow In Application.Windows
If myWindow.WindowState = xlMinimized Then
myWindow.WindowState = xlNormal
End If
Next myWindow

wkbk.Worksheets("BESPRO MT").Copy

With ActiveWorkbook
.SaveAs Filename:=myPath & DLSFile, _
FileFormat:=xlText, CreateBackup:=False
.Close savechanges:=False
End With

End Sub

I don't have a better way to approach it.

And now I'm worried about all the code that I have that may blow up! It doesn't
sound like an easy thing to fix--well, it's easy to fix, just difficult to find
all the places where it could occur.

I don't feel well...but on the other hand, I hardly ever minimize the windows
within excel.

So I got that going for me--which is good.

 

[Dave Peterson]

And I should have added it was a problem in xl2002.

 

[Dave Peterson]

Tushar Mehta suggested this cleaner alternative in a different forum:

Sub testme01()

Dim wkbk As Workbook, DLSFile As String, myPath As String, _
NewWB As Workbook

DLSFile = "DL72112.txt"
myPath = ThisWorkbook.Path & "\"

Set wkbk = ThisWorkbook ' or activeworkbook????
wkbk.Worksheets("BESPRO MT").Copy
Set NewWB = Workbooks(Workbooks.Count)
With NewWB
.SaveAs Filename:=myPath & DLSFile, _
FileFormat:=xlText, CreateBackup:=False
.Close savechanges:=False
End With

End Sub

It looks cleaner to me, too.

 

[Sandy V]

Could you copy straight into a text file:

Sub Range2Txt()
Dim MyData As DataObject

Set MyData = New DataObject

ActiveSheet.UsedRange.Copy
'Selection.Copy
MyData.GetFromClipboard

Open "C:\Temp\Range2Txt_Test1.txt" For Output As #1
Print #1, MyData.GetText(1)
Close #1

Application.CutCopyMode = False
End Sub

Seems too simple in view of the experiences you and Dave have had, so
I might have missed something!

Regards,
Sandy

 

[Dave Peterson]

Neat idea.

geopf,

if you get an error on the DataObject line, you can set a reference:
Inside the VBE,
tools|references|
put a check mark in front of: Microsoft Forms 2.0 Object Library

(or just insert|userform will create the reference, too.)

But I did have a couple of problems. If I had a pretty large usedrange,
sometimes I'd get an error on the .getfromclipboard line if I just ran the code.

But if I stepped through it with F8's, it never failed.

(Maybe a combination of lack of resources/windows version???)

And if you want a little more control on what to save, you may want to look at
Chip Pearson's site. He has example code that exports a range to a text file.
(It's more complex, but much more customizable.)

http://www.cpearson.com/excel/imptext.htm

 

[goepf]

@ ALL

Thanks guys. It works now fine with your support!

what a forum ;