Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new workbook and add sheets
Hi,
I am parsing a text file into component parts: I want to open a new workbook, add a few worksheets, ad data, save file. I can add the workbook but I can't seem to add worksheets to it How can I fix the code below? Thanks Habib ---------------------- SrcFile = GetFile() XLFile = Left(srcFile, Len(srcFile) - 4) & ".xls" Set wkbook = Workbooks.Add() wkbook.Activate wksheets = Array("Invoice", "Payment", "Reference", "Summary", "Other") For i = 0 To UBound(wksheets) Worksheets.Add(Befo=Worksheets(Worksheets.Count )).Name = "test" ActiveSheet.Name = wksheets(i) Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new workbook and add sheets
I'd use:
Dim wkSheets As Variant Dim i As Long Dim wkBook As Workbook Set wkBook = Workbooks.Add(1) 'single sheet wkBook.Worksheets(1).Name = "deletemelater" wkSheets = Array("Invoice", "Payment", "Reference", "Summary", "Other") For i = LBound(wkSheets) To UBound(wkSheets) With wkBook .Worksheets.Add(Befo=.Worksheets(Worksheets.Cou nt)).Name = wkSheets(i) End With Next i Application.DisplayAlerts = False wkBook.Worksheets("deletemelater").Delete Application.DisplayAlerts = True "HSalim[MVP]" wrote: Hi, I am parsing a text file into component parts: I want to open a new workbook, add a few worksheets, ad data, save file. I can add the workbook but I can't seem to add worksheets to it How can I fix the code below? Thanks Habib ---------------------- SrcFile = GetFile() XLFile = Left(srcFile, Len(srcFile) - 4) & ".xls" Set wkbook = Workbooks.Add() wkbook.Activate wksheets = Array("Invoice", "Payment", "Reference", "Summary", "Other") For i = 0 To UBound(wksheets) Worksheets.Add(Befo=Worksheets(Worksheets.Count )).Name = "test" ActiveSheet.Name = wksheets(i) Next -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new workbook and add sheets
There's a typo in my code!!!
..Worksheets.Add(Befo=.Worksheets(Worksheets.Co unt)).Name = wkSheets(i) should be: ..Worksheets.Add(Befo=.Worksheets(.Worksheets.C ount)).Name = wkSheets(i) Those leading dots mean that those items belong to the object in the previous "With" statement. Dave Peterson wrote: I'd use: Dim wkSheets As Variant Dim i As Long Dim wkBook As Workbook Set wkBook = Workbooks.Add(1) 'single sheet wkBook.Worksheets(1).Name = "deletemelater" wkSheets = Array("Invoice", "Payment", "Reference", "Summary", "Other") For i = LBound(wkSheets) To UBound(wkSheets) With wkBook .Worksheets.Add(Befo=.Worksheets(Worksheets.Cou nt)).Name = wkSheets(i) End With Next i Application.DisplayAlerts = False wkBook.Worksheets("deletemelater").Delete Application.DisplayAlerts = True "HSalim[MVP]" wrote: Hi, I am parsing a text file into component parts: I want to open a new workbook, add a few worksheets, ad data, save file. I can add the workbook but I can't seem to add worksheets to it How can I fix the code below? Thanks Habib ---------------------- SrcFile = GetFile() XLFile = Left(srcFile, Len(srcFile) - 4) & ".xls" Set wkbook = Workbooks.Add() wkbook.Activate wksheets = Array("Invoice", "Payment", "Reference", "Summary", "Other") For i = 0 To UBound(wksheets) Worksheets.Add(Befo=Worksheets(Worksheets.Count )).Name = "test" ActiveSheet.Name = wksheets(i) Next -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new workbook and add sheets
Dave,
thanks for that. That did the trick. While tinkering with the code, I found another way. wkSheets = Array("Invoice", "Payment", "Reference", "Summary", "Other") Application.SheetsInNewWorkbook = UBound(wkSheets) + 1 Set wkBook = Workbooks.Add For i = LBound(wkSheets) To UBound(wkSheets) With wkBook .Sheets(i + 1).Name = wkSheets(i) End With Next i Regards Habib "Dave Peterson" wrote in message ... There's a typo in my code!!! .Worksheets.Add(Befo=.Worksheets(Worksheets.Cou nt)).Name = wkSheets(i) should be: .Worksheets.Add(Befo=.Worksheets(.Worksheets.Co unt)).Name = wkSheets(i) Those leading dots mean that those items belong to the object in the previous "With" statement. Dave Peterson wrote: I'd use: Dim wkSheets As Variant Dim i As Long Dim wkBook As Workbook Set wkBook = Workbooks.Add(1) 'single sheet wkBook.Worksheets(1).Name = "deletemelater" wkSheets = Array("Invoice", "Payment", "Reference", "Summary", "Other") For i = LBound(wkSheets) To UBound(wkSheets) With wkBook .Worksheets.Add(Befo=.Worksheets(Worksheets.Cou nt)).Name = wkSheets(i) End With Next i Application.DisplayAlerts = False wkBook.Worksheets("deletemelater").Delete Application.DisplayAlerts = True "HSalim[MVP]" wrote: Hi, I am parsing a text file into component parts: I want to open a new workbook, add a few worksheets, ad data, save file. I can add the workbook but I can't seem to add worksheets to it How can I fix the code below? Thanks Habib ---------------------- SrcFile = GetFile() XLFile = Left(srcFile, Len(srcFile) - 4) & ".xls" Set wkbook = Workbooks.Add() wkbook.Activate wksheets = Array("Invoice", "Payment", "Reference", "Summary", "Other") For i = 0 To UBound(wksheets) Worksheets.Add(Befo=Worksheets(Worksheets.Count )).Name = "test" ActiveSheet.Name = wksheets(i) Next -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a new workbook and add sheets
You might want to cache the old value of SheetsInNewWorkbook and then put it back the way you found it when you're done. -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23870 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
How to create workbook with multiple sheets | Excel Discussion (Misc queries) | |||
Create new workbook when 00 sheets are reached | Excel Programming | |||
create workbook, copy sheets | Excel Programming | |||
Create New Workbook - Name book - 4 Sheets - Name Sheets | Excel Programming |