VBA Worksheets save new file to Folder on Desktop

[Jeff]

Help needed to separate Excel sheets from 1 file into separate new file
worksheet. Each new file created needs to be saved as text appearing in one
of the cells into a new folder on my desktop.



Is anyone able to help me with Excel VBA please – I usually do Word and
Access VBA.



Beginner Excel!



Thank you.

 

[OssieMac]

Firstly you need to have the macro in a separate workbook and then open the
workbook from where you want to save the worksheets to individual txt files.

You did not indicate the type of txt file so if I have not used the correct
type in the following macro, simply record a macro to save the file in your
required format. Don't forget to start the recording in a separate workbook
and change to the workbook where you want to save the sheets.

I have used the names of the sheets for the file names and it saves all
sheets in the workbook to separate txt filenames. If you want to exclude any
then test for the worksheet names. If you have used VBA in other
applications, I am sure that you can adjust this if required.

The macro was tested in xl2002

Option Explicit
Sub SaveShtsAsText()
Dim ws As Worksheet
Dim strPath As String
Dim strFile As String
Dim strPathNFile As String

strPath = "c:\Documents and Settings\User\Desktop\"

Workbooks.Open Filename:="FileToSaveAsTxt.xls"
Windows("FileToSaveAsTxt.xls").Activate
For Each ws In Worksheets
ws.Select
strFile = ws.Name
strPathNFile = strPath & strFile
ActiveWorkbook.SaveAs Filename:= _
strPathNFile, _
FileFormat:=xlTextMSDOS, _
CreateBackup:=False
Next ws
Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True
End Sub

 

[Jen]

Thanks will see how I go.

Firstly you need to have the macro in a separate workbook and then open
the
workbook from where you want to save the worksheets to individual txt
files.

You did not indicate the type of txt file so if I have not used the
correct
type in the following macro, simply record a macro to save the file in
your
required format. Don't forget to start the recording in a separate
workbook
and change to the workbook where you want to save the sheets.

I have used the names of the sheets for the file names and it saves all
sheets in the workbook to separate txt filenames. If you want to exclude
any
then test for the worksheet names. If you have used VBA in other
applications, I am sure that you can adjust this if required.

The macro was tested in xl2002

Option Explicit
Sub SaveShtsAsText()
Dim ws As Worksheet
Dim strPath As String
Dim strFile As String
Dim strPathNFile As String

strPath = "c:\Documents and Settings\User\Desktop\"

Workbooks.Open Filename:="FileToSaveAsTxt.xls"
Windows("FileToSaveAsTxt.xls").Activate
For Each ws In Worksheets
ws.Select
strFile = ws.Name
strPathNFile = strPath & strFile
ActiveWorkbook.SaveAs Filename:= _
strPathNFile, _
FileFormat:=xlTextMSDOS, _
CreateBackup:=False
Next ws
Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True
End Sub