Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text File | Excel Discussion (Misc queries) | |||
Text records in Excel | Excel Discussion (Misc queries) | |||
Changing a comma separated text file and save it. | Excel Discussion (Misc queries) | |||
how can i convert text file to excel file ? | New Users to Excel | |||
Improrting Text file to a matrix when things are not constant. | Excel Worksheet Functions |