Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
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? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
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? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
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... losmac wrote: *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? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~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 . ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
could anybody give me a hand here? I would really appreciate it! Thanks ;-)) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
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 wrote: could anybody give me a hand here? I would really appreciate it! Thanks ;-)) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
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 --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
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 wrote: 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 --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
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 wrote: *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 wrote: 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 --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson * --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
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. goepf wrote: 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 wrote: *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 wrote: 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 --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson * --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
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. goepf wrote: 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 wrote: *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 wrote: 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 --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson * --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
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 goepf wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
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 Sandy V wrote: 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 goepf wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save sheet as .TXT out of Workbook
@ ALL
Thanks guys. It works now fine with your support! what a forum ; -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help! When I 'save changes' to my workbook/sheet | Excel Discussion (Misc queries) | |||
Save Sheet as Workbook | Excel Discussion (Misc queries) | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions | |||
Can I only save one sheet out of the workbook? | Excel Discussion (Misc queries) | |||
Save sheet as .TXT out of Workbook | Excel Programming |