Create text file
 

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

Create text file
 

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


---
Regards,
Norman


"Rob" wrote in message
...
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

Create text file
 

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

Create text file
 

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
'<<=============


---
Regards,
Norman


"Rob" wrote in message
...
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

Create text file
 

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

Rob

"Norman Jones" wrote in message
...
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
'<<=============


---
Regards,
Norman


"Rob" wrote in message
...
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