Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Folder, Workbook, and Worksheet
I'm trying to figure out a way to create a routine that when activated
will 1) Place a new folder on the desktop When another situation is true 2) Create a new workbook with a name that is in a string When another situation is true 3) Add a new worksheet in the workbook with a name that is held in a string My biggest struggle is the last item. The only routine I've found to rename a worksheet requires that I know the name of the active worksheet. But everytime I add a new worksheet it gets indexed with a new name (e.g. Sheet1, Sheet2, Sheet3, etc.). Do I have to track this or is there an easier way. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Folder, Workbook, and Worksheet
Hi
1 + 2 Add folder and add/save a workbook in that folder Sub test() Dim wsh As Object Dim fs As Object Dim DesktopPath As String Dim DirString As String Dim fname As String Dim wb As Workbook Set wsh = CreateObject("WScript.Shell") Set fs = CreateObject("Scripting.FileSystemObject") DesktopPath = wsh.SpecialFolders.Item("Desktop") DirString = DesktopPath & "\Testfolder" If Not fs.FolderExists(DirString) Then fs.CreateFolder DirString Else End If Application.ScreenUpdating = False fname = DirString & "\Ron.xls" Workbooks.Add xlWBATWorksheet Set wb = ActiveWorkbook With wb .SaveAs fname .Close False End With Application.ScreenUpdating = True End Sub 3) Worksheets.Add.Name = "Ron" -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... I'm trying to figure out a way to create a routine that when activated will 1) Place a new folder on the desktop When another situation is true 2) Create a new workbook with a name that is in a string When another situation is true 3) Add a new worksheet in the workbook with a name that is held in a string My biggest struggle is the last item. The only routine I've found to rename a worksheet requires that I know the name of the active worksheet. But everytime I add a new worksheet it gets indexed with a new name (e.g. Sheet1, Sheet2, Sheet3, etc.). Do I have to track this or is there an easier way. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to update active workbook in one folder | Excel Worksheet Functions | |||
Workbook saved in FTP folder | Excel Discussion (Misc queries) | |||
Move Active Workbook to another Folder | Excel Discussion (Misc queries) | |||
Link with workbook in other folder which is in another group | Excel Discussion (Misc queries) | |||
Force a Readonly Workbook to save to a different folder | Excel Discussion (Misc queries) |