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]
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,942
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,020
Messages
5,834,986
Members
430,331
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top