vba to export selections from worksheets

jacknc

Board Regular
Joined
Nov 28, 2005
Messages
75
Office Version
  1. 365
  2. 2019
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.


Code:
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]
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,936
try something like this (not tested):

Code:
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

Board Regular
Joined
Nov 28, 2005
Messages
75
Office Version
  1. 365
  2. 2019
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,760
Messages
5,574,087
Members
412,566
Latest member
TexasTony
Top