How to add worksheets and rename with vba?

I have a batch process that exports from Access. The problem I'm having is
creating (and naming) a new workbook, and inserting (and naming) the
multiple worksheets.

First, creating the Workbook (see line marked with ****). How do I create a
Workbook (and give the Workbook a specified name)? For example, in the
below function, I pass in "fld", which is a path to a directory. If the
user selected "new workbook" then I need to create a new Workbook in the
given directory and name the new Workbook somehow. Do I do this with Excel
automation? fso object?

Public Function GetSubFolders(fld As Scripting.Folder) As Boolean
Dim xlapp As Excel.Application
Dim xlwkbs As Excel.Workbooks
Dim xlwkb As Excel.Workbook
Dim fldSub As Scripting.Folder
Dim fso As Scripting.FileSystemObject
Dim strMdb As String
Dim strTarget As String
Dim bytOutput As Byte
Set xlapp = New Excel.Application
Set xlwkbs = xlapp.Workbooks
bytOutput = Forms("frmMain")!fraOutput
strTarget = Forms("frmMain")!txtOutput
Select Case bytOutput
Case 1 'existing workbook
Set xlwkb = xlwkbs(strTarget)
Case 2 'new workbook
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(strTarget) Then
fso.DeleteFile (strTarget) 'delete if already exists
End if
xlwkbs.Add '************************
End Select
For Each fldSub In fld.SubFolders
strMdb = fld & "\" & fldSub.Name & "\MEAS.MDB"
If LinkTables(strMdb) Then Call CreateWorksheets(bytOutput, _
strTarget, xlapp, xlwkbs, xlwkb)
Next fldSub
GetSubFolders = True
End Function

Next, I need to insert a bunch of Worksheets. Am I going about this the
right way?

Private Function CreateWorksheets(bytOutput As Byte, strTarget As String, _
xlapp As Excel.Application, xlwkbs As Excel.Workbooks, wkb As
Excel.Workbook)
Dim xlwks As Excel.Worksheets
Dim xlwkss As Excel.Worksheets
Dim i As Byte
Select Case bytOutput
Case 1 'existing workbook
i = xlwkbs.Count - 1
xlwkss.Add After:=Worksheets(i)
Case 2 'new workbook
xlwkss.Add After:=Worksheets(i)
End Select
Set xlwks = xlwkss(i + 1)
xlwks.Name = strWksName '************
Call PopulateWorksheet 'dumps tables into wks
End Function

Thanks in advance.

Deko,

You cannot rename the workbook as you add it, it doesn't get a name until it
is saved, so you should save it after creating to give it a name.

To add a worksheet and name it, use

worksheets.Add(After:=worksheets(i)).name=strWksName

--

HTH

RP
(remove nothere from the email address if mailing direct)


"deko" <[email protected]> wrote in message
news:[email protected]...
> I have a batch process that exports from Access. The problem I'm having
is
> creating (and naming) a new workbook, and inserting (and naming) the
> multiple worksheets.
>
> First, creating the Workbook (see line marked with ****). How do I create
a
> Workbook (and give the Workbook a specified name)? For example, in the
> below function, I pass in "fld", which is a path to a directory. If the
> user selected "new workbook" then I need to create a new Workbook in the
> given directory and name the new Workbook somehow. Do I do this with
Excel
> automation? fso object?
>
> Public Function GetSubFolders(fld As Scripting.Folder) As Boolean
> Dim xlapp As Excel.Application
> Dim xlwkbs As Excel.Workbooks
> Dim xlwkb As Excel.Workbook
> Dim fldSub As Scripting.Folder
> Dim fso As Scripting.FileSystemObject
> Dim strMdb As String
> Dim strTarget As String
> Dim bytOutput As Byte
> Set xlapp = New Excel.Application
> Set xlwkbs = xlapp.Workbooks
> bytOutput = Forms("frmMain")!fraOutput
> strTarget = Forms("frmMain")!txtOutput
> Select Case bytOutput
> Case 1 'existing workbook
> Set xlwkb = xlwkbs(strTarget)
> Case 2 'new workbook
> Set fso = CreateObject("Scripting.FileSystemObject")
> If fso.FileExists(strTarget) Then
> fso.DeleteFile (strTarget) 'delete if already exists
> End if
> xlwkbs.Add '************************
> End Select
> For Each fldSub In fld.SubFolders
> strMdb = fld & "\" & fldSub.Name & "\MEAS.MDB"
> If LinkTables(strMdb) Then Call CreateWorksheets(bytOutput, _
> strTarget, xlapp, xlwkbs, xlwkb)
> Next fldSub
> GetSubFolders = True
> End Function
>
> Next, I need to insert a bunch of Worksheets. Am I going about this the
> right way?
>
> Private Function CreateWorksheets(bytOutput As Byte, strTarget As String,
_
> xlapp As Excel.Application, xlwkbs As Excel.Workbooks, wkb As
> Excel.Workbook)
> Dim xlwks As Excel.Worksheets
> Dim xlwkss As Excel.Worksheets
> Dim i As Byte
> Select Case bytOutput
> Case 1 'existing workbook
> i = xlwkbs.Count - 1
> xlwkss.Add After:=Worksheets(i)
> Case 2 'new workbook
> xlwkss.Add After:=Worksheets(i)
> End Select
> Set xlwks = xlwkss(i + 1)
> xlwks.Name = strWksName '************
> Call PopulateWorksheet 'dumps tables into wks
> End Function
>
> Thanks in advance.
>
>

> You cannot rename the workbook as you add it, it doesn't get a name until
it
> is saved, so you should save it after creating to give it a name.

Ah, I see....

Here's what I've got so far:

Dim NewWorkbook as Object

Case 1 'existing workbook
Set xlwkb = xlwkbs(strTarget)
Case 2 'new workbook
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(strTarget) Then fso.DeleteFile (strTarget)
Set NewWorkbook = xlwkbs.Add
NewWorkbook.SaveAs (strTarget)


Seems to be working - but is it possible to create the Workbook without any
Worksheets? I will be adding several with code, and they need to be named
as thery are added. I suppose I could just delete the default 3 sheets, but
it would be more efficient to create the Workbook without any sheets. Can
this be done?

> To add a worksheet and name it, use
>
> worksheets.Add(After:=worksheets(i)).name=strWksName

Great! That looks easy enough.

Thanks for the help!

You cannot create a workbook with no worksheets, must be at least 1. You can
pre-set this like so

Application.SheetsInNewWorkbook = 1
Workbooks.Add



--

HTH

RP
(remove nothere from the email address if mailing direct)


"deko" <[email protected]> wrote in message
news:[email protected]...
> > You cannot rename the workbook as you add it, it doesn't get a name
until
> it
> > is saved, so you should save it after creating to give it a name.
>
> Ah, I see....
>
> Here's what I've got so far:
>
> Dim NewWorkbook as Object
>
> Case 1 'existing workbook
> Set xlwkb = xlwkbs(strTarget)
> Case 2 'new workbook
> Set fso = CreateObject("Scripting.FileSystemObject")
> If fso.FileExists(strTarget) Then fso.DeleteFile (strTarget)
> Set NewWorkbook = xlwkbs.Add
> NewWorkbook.SaveAs (strTarget)
>
>
> Seems to be working - but is it possible to create the Workbook without
any
> Worksheets? I will be adding several with code, and they need to be named
> as thery are added. I suppose I could just delete the default 3 sheets,
but
> it would be more efficient to create the Workbook without any sheets. Can
> this be done?
>
> > To add a worksheet and name it, use
> >
> > worksheets.Add(After:=worksheets(i)).name=strWksName
>
> Great! That looks easy enough.
>
> Thanks for the help!
>
>

> You cannot create a workbook with no worksheets, must be at least 1. You
can
> pre-set this like so
>
> Application.SheetsInNewWorkbook = 1
> Workbooks.Add

10-4.

Could you also help me with the following question:

I am using late binding, and need to pass objects between functions. How do
I do this?

Do I do it like this:

Private Function CreateWorksheet(bytOutput As Byte, strTarget As String, _
xlapp As Object, xlwkbs As Object, _
xlwkb As Object, strSheetName As String)

Dim xlwks As Object
Dim xlwkss As Object
Dim i As Byte
Set xlwkss = xlwkb.Worksheets
Debug.Print xlwkss.Count '**** ? ?
i = xlwkss.Count
xlwkss.Add After:=Worksheets(i).Name = strSheetName
Debug.Print "inserting worksheet " & i

End Function

The idea is to set the objects once in the calling function, and then make a
call to this function from a loop (so I don't have to create and destroy the
objects in each iteration). Doe this make sense?

Thanks again.

Deko,

That is one way of passing objects, but I would make few observations.

Firstly, pass your variables ByVal unless you need to modify them, it is
more efficient.

Private Function CreateWorksheet(ByVal bytOutput As Byte, _
ByVal strTarget
As String, _
ByVal xlapp As
Object, _
ByVal xlwkbs As
Object, _
ByVal xlwkb As
Object, _
ByVal
strSheetName As String)

If you are setting specific objects in the caller, such as the workbook, you
will already have used the app object, so it is probably not necessary to
pass that. It is hard for me to be definitive, as there is no code in the
called module that uses it, so I assume it is just example code. Only pass
the objects that you need.

This statement seems superfluous

Set xlwkss = xlwkb.Worksheets
Debug.Print xlwkss.Count '**** ? ?

why not just use

Debug.Print xlwkb.Worksheets.Count

setting the object seems pointless, even for the worksheet.add.

Oh, I also don't think this works

xlwkss.Add After:=Worksheets(i).Name = strSheetName

it should be

xlwkss.Add(After:=Worksheets(i)).Name = strSheetName


--

HTH

RP
(remove nothere from the email address if mailing direct)


"deko" <[email protected]> wrote in message
news:[email protected]...
> > You cannot create a workbook with no worksheets, must be at least 1. You
> can
> > pre-set this like so
> >
> > Application.SheetsInNewWorkbook = 1
> > Workbooks.Add
>
> 10-4.
>
> Could you also help me with the following question:
>
> I am using late binding, and need to pass objects between functions. How
do
> I do this?
>
> Do I do it like this:
>
> Private Function CreateWorksheet(bytOutput As Byte, strTarget As String, _

> xlapp As Object, xlwkbs As Object, _
> xlwkb As Object, strSheetName As String)
>
> Dim xlwks As Object
> Dim xlwkss As Object
> Dim i As Byte
> Set xlwkss = xlwkb.Worksheets
> Debug.Print xlwkss.Count '**** ? ?
> i = xlwkss.Count
> xlwkss.Add After:=Worksheets(i).Name = strSheetName
> Debug.Print "inserting worksheet " & i
>
> End Function
>
> The idea is to set the objects once in the calling function, and then make
a
> call to this function from a loop (so I don't have to create and destroy
the
> objects in each iteration). Doe this make sense?
>
> Thanks again.
>
>