vba to export selections from worksheets

[jacknc]

I need to output several "sections" into one txt file and am confused how to do it.

Here is what I am needing:
Sheet4: a1-a166
Sheet2: Entire A column - this could vary. I dont know how to determine last row
Sheet3: Column count varys - I have a variable in sheet1(Sheet1!E2) x 5 rows. Output should be b1,b2,b3,b4,b5. c1,c2,c3,c4,c5 etc depending on variable
Sheet 4 a:626-a656

Can I use a varible to save text as Sheet1!D2.txt
I am currently using the following code. However I have to go in and select cells.

Sub Export2Textfile()
Dim Textfile As Variant
Dim LastRow As Long
Dim XportArea As Range
Dim Cel As Range
Dim iFnum As Integer
'Select a textfile to save to
Textfile = Application.GetSaveAsFilename( _
        InitialFileName:="text.txt", _
        FileFilter:="Text files, *.txt)", _
        Title:="Save textfile as:")
If Textfile = False Then Exit Sub
On Error Resume Next
'Select the cells to export:
Set XportArea = Application.InputBox( _
    "Select the cells to export:", _
    "Use your mouse:", Selection.Address, Type:=8)
If XportArea Is Nothing Then Exit Sub
'open / create the textfile
iFnum = FreeFile
Open CStr(Textfile) For Append As iFnum
'loop cells
For Each Cel In XportArea
'write to textfile
Print #iFnum, Cel.Text
Next
Close #iFnum
End Sub

Any help would be greatly appreciated.[/code]

 

[btadams]

try something like this (not tested):

Dim iFnum As Integer


Sub Export2Textfile()
Dim Textfile As Variant
Dim LastRow As Long
Dim XportArea As Range
Dim Cel As Range

'Select a textfile to save to
Textfile = Application.GetSaveAsFilename( _
        InitialFileName:="text.txt", _
        FileFilter:="Text files, *.txt)", _
        Title:="Save textfile as:")
If Textfile = False Then Exit Sub
On Error Resume Next
'open / create the textfile
iFnum = FreeFile
Open CStr(Textfile) For Append As iFnum
'Select the cells to export:
Sheets("Sheet4").Activate
Set XportArea = Sheets("Sheet4").Range("A1:A166")
WriteTxt XportArea
Sheets("Sheet2").Activate
Set XportArea = Sheets("Sheet2").Range([A1], [A65536].End(xlUp))
WriteTxt XportArea
Sheets("Sheet3").Activate
Set XportArea = Sheets("Sheet3").Range([B1], [B1].End(xlToRight).End(xlDown))
WriteTxt XportArea
Sheets("Sheet4").Activate
Set XportArea = Sheets("Sheet4").Range("A626:A656")
WriteTxt XportArea
Close #iFnum
End Sub

Sub WriteTxt(rngText As Range)
Dim cell As Range
'loop cells
MsgBox rngText.Address
For Each cell In rngText
'write to textfile
Print #iFnum, cell.Text
Next

End Sub

 

[jacknc]

Bt,

Thanks for your reply!
I believe we are getting somewhere. Everything works good except for 'sheet3'. That sheet contains one column which varys in rows depending on info from sheet1. I use the following formula to split it up into '4s' on sheet 3.I believe your script would work if I could make the coulumns not display after main column has finished splitting into 4s. The needed results start at b1x5 rows x columns(sheet1c2)

here is what I use
=IF(OFFSET($A$1,Sheet1!$C$2*(ROW()-1)+COLUMN()-2,0)="","",OFFSET($A$1,Sheet1!$C$2*(ROW()-1)+COLUMN()-2,0))

Thats why I thought I could just use the variable from sheet1 C2 to determine the columns needed.

Again thanks for your help.
Jack