Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Worksheets save new file to Folder on Desktop
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Worksheets save new file to Folder on Desktop
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 -- Regards, OssieMac "Jeff" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Worksheets save new file to Folder on Desktop
Thanks will see how I go.
"OssieMac" wrote in message ... 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 -- Regards, OssieMac "Jeff" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically Save a file on the Desktop | Excel Programming | |||
Macro - Save file in Desktop | Excel Worksheet Functions | |||
Save file in a new folder, but create folder only if folder doesn't already exist? | Excel Programming | |||
Need code to save file to new folder, erase from old folder | Excel Discussion (Misc queries) | |||
open file from folder save in new folder | Excel Programming |