Create text file

[Rob]

Hi,

I would like to create a text file from a worksheet (named according to the
value of a cell in that worksheet), and not have the workbook from which
that sheet is attached close.

ie say the workbook is called A.xls and I create a text file called B.txt. I
want to save and close B.txt and keep A.xls open.

Can anyone help me with that?

Rob

 

[Norman Jones]

Hi Rob,

Try:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim WB2 As Workbook
Dim SH As Worksheet
Dim rng As Range
Const newFileName As String = "B" '<<==== CHANGE

Set WB = Workbooks("A.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE
Set rng = SH.Range("A1") '<<==== CHANGE

SH.Copy

Application.DisplayAlerts = False
With ActiveWorkbook
.SaveAs Filename:=newFileName, _
FileFormat:=xlTextWindows
.Close
End With
Application.DisplayAlerts = True

End Sub
'<<=============

If you are not familiar with macros, you may wish to visit David McRitchie's
'Getting Started With Macros And User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Rob]

Hi Norman,

Thankyou for your great procedure. It works brilliantly, but wonder if you
could assist with a couple of matters which I've shown in the procedure (as
I have it) below....
Sub ExportToMYOB()
Dim WB As Workbook
Dim WB2 As Workbook
Dim SH As Worksheet
Dim rng As Range
Const newFileName As String = "See note ->" 'I need this to be the
value of a cell from one of the worksheets. How can that be done?
Set WB = Workbooks("General-Journal1.xls")
Set SH = WB.Sheets("Sheet3") 'When running the code it stops
at this point and says "Subscript out of range unless I change it to the
actual name of the sheet. Is there a way to refer simply to the sheet
number?
Set rng = SH.Range("A1")
SH.Copy
Application.DisplayAlerts = False
With ActiveWorkbook
.SaveAs Filename:=newFileName, _
FileFormat:=xlTextWindows
.Close
End With
Application.DisplayAlerts = True
End Sub

Thank you for your time!

Rob

 

[Norman Jones]

Hi Rob,

Try:

'=============>>
Sub ExportToMYOB()
Dim WB As Workbook
Dim WB2 As Workbook
Dim SH As Worksheet
Dim SH2 As works
Dim rng As Range
Dim newFileName As String

Set WB = Workbooks("General-Journal1.xls")
Set SH = WB.Sheets(1) '<<==== CHANGE
Set SH2 = WB.Sheets(2) '<<==== CHANGE
Set rng = SH2.Range("A1") '<<==== CHANGE

newFileName = rng.Text
SH.Copy
Application.DisplayAlerts = False
With ActiveWorkbook
.SaveAs Filename:=newFileName, _
FileFormat:=xlText
.Close
End With
Application.DisplayAlerts = True
End Sub
'<<=============

 

[Rob]

Thankyou so much Norman, you've been a great help. The procedure works
perfectly now.

Rob