XL2K: How can I save a selected range as a .txt file? 1

[MeGustaXL]

Hi All, just hoping you can help me with a teeny problemette.

Here’s an extract from a table in a worksheet:

Ser
No Tail No Unit

22	 ZD322	  DET B  
48	 ZD354	  LAARBRUCH
92	 ZD329	  NAVY
108    ZD376	  13 SQN  
109    ZG500	  MARHAM
220    ZG479	  12 SQN
223    	       STORES
228    ZX665	  ST ATHAN
229    ZD321	  2 SQN
231    ZD352	  TWCU
232    ZD330	  14 SQN
291	ZD327	  1435 FLT
328	ZG503	  MT PLEASANT
346	ZX667	  WITTERING
...
...

Is there a way to select a range of cells and save only the selected cells as a Tab-Delimited text file?
Something like this code (which fails, by the way )...

Sub Save_As_Text_File()
    Range(&quot;A3:C11&quot;).Select  ' < Replace this line with an InputBox routine
    With Selection
    .SaveAs Filename:=&quot;W:\Excel Stuff\Developer\Picked Lines.txt&quot;, _
        FileFormat:=xlText, CreateBackup:=False
    End With
End Sub

I'd hoped that would select Ser Nos 92 to 232, columns A to C and save just that block, but NIET.

The dream solution would allow me to just select down column A, say from 92 to 232, then expand the selection all the way across to the last column, maybe about L or M, and then save the selection.
Any Ideas?



Chris

Varium et mutabile semper Excel

 

[SkipVought]

Hi,

Lots of ways to skin a cat...

Save Workbook

Copy selection to a new sheet

SaveAs .txt

VOLA!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Loomah]

Hi Chris
This assumes you have already made your selection (by whatever method) then will save the selection as required.

Sub b()
Dim wbNoo As Workbook
Application.DisplayAlerts = False
    Selection.Copy
    Set wbNoo = Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
        wbNoo.SaveAs Filename:= _
            "C:\My Documents\Tests & Code Library\Excel\aaakak.txt", _
            FileFormat:=xlText, CreateBackup:=False
        wbNoo.Close False
Application.DisplayAlerts = True
Set wbNoo = Nothing
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[xlbo]

Hey Loomah - thought you'd disappeared. Does your absence mean you've finally got yourself a job ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Loomah]

xlbo
simple answer is no, just been dossing elsewhere for a few weeks
veron? thought he wasn't going anywhere!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[MeGustaXL]

Hi Loomah & Geoff

Loomah: Thanks for that, it 'kind of' does the job, but not quite as slickly as I'd like. I'll explain a bit..

There'll be several copies of this SS around at remote sites, all of which will be updated according to local events.
As each site completes its transactions, I want them to be able to:

Select that day's work, e.g. lines 3 to 12
Export the selected range as a text file, either to a floppy or their local hard disk.
Pass the text file to the Master User (Me) either by hand on a floppy or by e-mail.

When I get the text file(s), I want to Import them and append at the end of the List, probably using Data/Get External Data/Import Text File.

Selection Code:

Public Sub Pick_Selection()
[SlctRng] = Selection.Address
[CopyRng].Select
End Sub

SlctRng is a single cell, and CopyRng is defined as
=OFFSET(INDIRECT(SlctRng),0,0,ROWS(INDIRECT(SlctRng)),3)

By selecting a cell or cells in column A, an entire block is selected, bounded to the left by the User's choice.

Any way I can then Export as Text File with CopyRng, without going down the New Book, Save It, Delete It route?

Geoff: If you can read this sign, you can get a good job in the fast-paced, high-paying world of Latin!



Chris

Varium et mutabile semper Excel

 

[MeGustaXL]

Sorry Skip, left you out; no offence intended, Bud
Any other catskins or excoriation methods?

Chris

Varium et mutabile semper Excel

 

[Loomah]

Chris
I'll happily be corrected on this but as far as I'm aware there is no direct route from range to text file.

The code I posted is almost seemless, your not left with a stray wokbook or anything like that, all you have is the selected data saved as a text file. Disable screen updating and you'd never know another workbook had been opened!

I'm sure I've seen something about this on another site but I can't remember where and I can't find it. I've just done a google search which basically gave lots of options for saving a range as html but not as a text file.

;-)
Anyway, I don't speak Latin so I feel left out:-(
Might have to learn it though if the Roman revolution in SW6 gets any bigger!

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[MeGustaXL]

Loomah, I take back "not quite as slickly as I'd like". I just pasted your code in like this:

Public Sub Pick_Selection()
[slctrng] = Selection.Address
[copyrng].Select
Dim wbNoo As Workbook
Application.DisplayAlerts = False
    Selection.Copy
    Set wbNoo = Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
        wbNoo.SaveAs Filename:= _
            "C:\aaakak.txt", _
            FileFormat:=xlText, CreateBackup:=False
        wbNoo.Close False
Application.DisplayAlerts = True
Set wbNoo = Nothing

End Sub

and it ran as smooth as a cat P-ing on a sheet of velvet!

Have a star, my new best mate!

BTW, try this:

http://www.porkpie.demon.co.uk/slighty.htm

Chris

Varium et mutabile semper Excel

 

[xlbo]

Loomah - you can have Veron - just wait 'till winter and he's asked to play against Middlesborough or similar.
I can't really speak Latin either but I do know a good website for Latin quotes ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[MeGustaXL]

Thanks again for your help on this guys - I've got it working a treat now!

FYO I amended the Select & Save portion so that the user can name the file and choose its destination, thus:

Public Sub Pick_Selection()
[slctrng] = Selection.Address
[copyrng].Select
Dim wbNoo As Workbook
Dim FiletoSave As String
FiletoSave = Application.GetSaveAsFilename("DataFile", fileFilter:="Text Files (*.txt), *.txt")
Application.DisplayAlerts = False
    Selection.Copy
    Set wbNoo = Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
        wbNoo.SaveAs Filename:= _
            FiletoSave, _
            FileFormat:=xlText, CreateBackup:=False
        wbNoo.Close False
Application.DisplayAlerts = True
Set wbNoo = Nothing

End Sub

And I've let them (and Me!) pick the source file like this:

Sub DataInbound()
Dim Inbound As Range
Dim FiletoGet As Variant
Set Inbound = [A65536].End(xlUp).Offset(1, 0)
FiletoGet = Application.GetOpenFilename("Text Files (*.txt),*.txt")
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FiletoGet, _
        Destination:=[Inbound])
        .Refresh BackgroundQuery:=False
    End With
End Sub

All is well, now I can go home and have a few wet ones!


Chris

Varium et mutabile semper Excel