Saving Excell Data to File (More complicated then the title says)

[linuxConvert]

Hello,

I have been looking all over for a new way to do this and cant find it. So here it goes...

I have a workbook with about 20 tabs. They all contain data that starts in cell "B6" and can be in any cell down and to the right from there.

Here is the macro to select all of the data on that workbook:


Sub FileExport(wsName As String, CellBegin As String, FilePathName As String, DownAndRight As Boolean)
'
' FileExport(...)- exports input data sheets to ascii text files - extensively used.
'
Dim ws As Worksheet
Dim RangeStart As String
Dim RangeEnd As String

Set ws = Worksheets(wsName)
ws.Activate
ws.Range(CellBegin).Select
RangeStart = ActiveCell.Address

If DownAndRight = True Then
RangeEnd = ActiveCell.SpecialCells(xlCellTypeLastCell).Address
Else
RangeEnd = ActiveCell.End(xlDown).Address
End If

ws.Range(RangeStart & ":" & RangeEnd).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveWorkbook.SaveAs FileName:=FilePathName, FileFormat:=xlText, CreateBackup:=False
ActiveWindow.Close
ws.Activate
Range("A3").Select
End Sub


I need to save that copied data to a file called "something.dat". As you can see by the macro, it opens a new Workbook an then saves it. This takes some time and I am trying to remove the open workbook step. Any way to save copied data directly to a file?


EDIT: Would a OpenAsTextStream be what I want?


Thanks in advance!

-linuxConvert

[Bob Phillips]

Nope, if you want to write to something, you open it, or create a new workbook as you are doing.

Turn off screenupdating, and if your workbook has many formulae, turn calcculation mode to manual.

[linuxConvert]

I am turning off screen updating in the function that calls this functions.

I think I may have something with OpenAsTextStream.

New Question: Can I turn a selection, with Range("X1:Z4").Select, into tab delimited text to write to a file made from OpenAsTextStream?

Thanks!

[Bob Phillips]

Maybe this will work for you



Dim ary, s
Dim x
ary = Range("X1:Z4")

With Application
s = Join(.Transpose(.Index(ary, 0, 1)), vbTab) & vbTab & _
Join(.Transpose(.Index(ary, 0, 2)), vbTab) & vbTab & _
Join(.Transpose(.Index(ary, 0, 3)), vbTab)
End With