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

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

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

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

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