Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Need to create spreadsheet with multiple worksheets with one VBscript

386 views
Skip to first unread message

Chris

unread,
Apr 7, 2008, 3:50:04 PM4/7/08
to
Hello,

I'm not a scripter/programmer, just know how to cut and paste really
well. I need some eyes to tell me what I am doing wrong with this
script that I am trying to use to create a spreadsheet named
(currentdate) and with 18 additional worksheets added to the three
that are already created.

I found a script that does part of what I want, but I am having
trouble creating the other worksheets within the workbook....

Can someone show me the correct syntax or what I am doing wrong?
Thanks;

dtmDate = Date

strMonth = Month(Date)
strDay = Day(Date)
strYear = Right(Year(Date),2)

strFileName = "R:\xyz\weeklyreports\addedrules\" & strMonth & "-" &
strDay & "-" & strYear & ".xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()

Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add
Sheets.Add

objWorkbook.SaveAs(strFileName)

Tom Lavedas

unread,
Apr 7, 2008, 4:09:05 PM4/7/08
to
On Apr 7, 3:50 pm, Chris <chris.ho...@gmail.com> wrote:
> Hello,
>
> I'm not a scripter/programmer, just know how to cut and paste really
> well. I need some eyes to tell me what I am doing wrong with this
> script that I am trying to use to create a spreadsheet named
> (currentdate) and with 18 additional worksheets added to the three
> that are already created.
>
> I found a script that does part of what I want, but I am having
> trouble creating the other worksheets within the workbook....
>
> Can someone show me the correct syntax or what I am doing wrong?
> Thanks;
>
> dtmDate = Date
>
> strMonth = Month(Date)
> strDay = Day(Date)
> strYear = Right(Year(Date),2)
>
> strFileName = "R:\xyz\weeklyreports\addedrules\" & strMonth & "-" &
> strDay & "-" & strYear & ".xls"
>
> Set objExcel = CreateObject("Excel.Application")
> objExcel.Visible = True
>
> Set objWorkbook = objExcel.Workbooks.Add()

' Try this
Const xlWorksheet = -4167
objWorkbook.Sheets.Add xlWorksheet, 19

> objWorkbook.SaveAs(strFileName)

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/

Chris

unread,
Apr 8, 2008, 9:18:08 AM4/8/08
to
> ===========http://members.cox.net/tglbatch/wsh/- Hide quoted text -
>
> - Show quoted text -

I tried this but got the error add method of Sheets class failed. The
code I am now using is below:

dtmDate = Date

strMonth = Month(Date)
strDay = Day(Date)
strYear = Right(Year(Date),2)

strFileName = "R:\NETIQ\weeklyreports\addedrules\" & strMonth & "-" &


strDay & "-" & strYear & ".xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()

Const xlWorksheet = -4167
objWorkbook.Sheets.Add xlWorksheet, 19


objWorkbook.SaveAs(strFileName)

Tom Lavedas

unread,
Apr 8, 2008, 9:33:59 AM4/8/08
to
On Apr 8, 9:18 am, Chris <chris.ho...@gmail.com> wrote:
> On Apr 7, 3:09 pm, Tom Lavedas <tglba...@cox.net> wrote
> > On Apr 7, 3:50 pm, Chris <chris.ho...@gmail.com> wrote:
>
> > > Hello,
>
{snip}

> I tried this but got the error add method of Sheets class failed. The
> code I am now using is below:
>
> dtmDate = Date
>
> strMonth = Month(Date)
> strDay = Day(Date)
> strYear = Right(Year(Date),2)
>
> strFileName = "R:\NETIQ\weeklyreports\addedrules\" & strMonth & "-" &
> strDay & "-" & strYear & ".xls"
>
> Set objExcel = CreateObject("Excel.Application")
> objExcel.Visible = True
>
> Set objWorkbook = objExcel.Workbooks.Add()
>
' Corrected
objWorkbook.Sheets.Add ,objWorkbook.Sheets(4),19

Chris

unread,
Apr 8, 2008, 12:44:29 PM4/8/08
to
> ===========http://members.cox.net/tglbatch/wsh/- Hide quoted text -
>
> - Show quoted text -

Hello,

thanks for the suggestion; however, with the new code i get the
following error:

800A0009 - Subscript out of range...

**code**

dtmDate = Date

strMonth = Month(Date)
strDay = Day(Date)
strYear = Right(Year(Date),2)

strFileName = "R:\NETIQ\weeklyreports\addedrules\" & strMonth & "-" &
strDay & "-" & strYear & ".xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()

objWorkbook.Sheets.Add ,objWorkbook.Sheets(4),19

objWorkbook.SaveAs(strFileName)

urkec

unread,
Apr 8, 2008, 1:49:02 PM4/8/08
to
"Chris" wrote:

If objExcel.Workbooks.Add() creates a workbook with three worksheets, then
there is no objWorkbook.Sheets(4). You could try using
objWorkbook.Sheets.Count instead:


dtmDate = Date

strMonth = Month(Date)
strDay = Day(Date)
strYear = Right(Year(Date),2)

strFileName = "c:\scripts\" & strMonth & "-" & strDay & "-" & strYear &
".xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()

objWorkbook.Sheets.Add ,objWorkbook.Sheets(objWorkbook.Sheets.Count),19

objWorkbook.SaveAs(strFileName)


--
urkec

Tom Lavedas

unread,
Apr 8, 2008, 1:55:37 PM4/8/08
to
On Apr 8, 1:49 pm, urkec <ur...@discussions.microsoft.com> wrote:
> "Chris" wrote:
> > On Apr 8, 8:33 am, Tom Lavedas <tglba...@cox.net> wrote:
> > > On Apr 8, 9:18 am, Chris <chris.ho...@gmail.com> wrote:
>
> > > > On Apr 7, 3:09 pm, Tom Lavedas <tglba...@cox.net> wrote
> > > > > On Apr 7, 3:50 pm, Chris <chris.ho...@gmail.com> wrote:
>
> > > > > > Hello,
>
> > > {snip}
> > > > I tried this but got the error add method of Sheets class failed. The
> > > > code I am now using is below:
>
> > > > dtmDate = Date
>
> > > > strMonth = Month(Date)
> > > > strDay = Day(Date)
> > > > strYear = Right(Year(Date),2)
>
> > > > strFileName = "R:\NETIQ\weeklyreports\addedrules\" & strMonth & "-" &
> > > > strDay & "-" & strYear & ".xls"
>
> > > > Set objExcel = CreateObject("Excel.Application")
> > > > objExcel.Visible = True
>
> > > > Set objWorkbook = objExcel.Workbooks.Add()
>
> > > ' Corrected
> > > objWorkbook.Sheets.Add ,objWorkbook.Sheets(4),19
>
> > > > objWorkbook.SaveAs(strFileName)
>
> > > Tom Lavedas
> > > ===========http://members.cox.net/tglbatch/wsh/-Hide quoted text -

Good catch.

Thanks,

Chris

unread,
Apr 8, 2008, 3:05:06 PM4/8/08
to
On Apr 8, 12:55 pm, Tom Lavedas <tglba...@cox.net> wrote:
> On Apr 8, 1:49 pm, urkec <ur...@discussions.microsoft.com> wrote:
>
>
>
>
>
> > "Chris" wrote:
> > > On Apr 8, 8:33 am, Tom Lavedas <tglba...@cox.net> wrote:
> > > > On Apr 8, 9:18 am, Chris <chris.ho...@gmail.com> wrote:
>
> > > > > On Apr 7, 3:09 pm, Tom Lavedas <tglba...@cox.net> wrote
> > > > > > On Apr 7, 3:50 pm, Chris <chris.ho...@gmail.com> wrote:
>
> > > > > > > Hello,
>
> > > > {snip}
> > > > > I tried this but got the error add method of Sheets class failed.  The
> > > > > code I am now using is below:
>
> > > > > dtmDate = Date
>
> > > > > strMonth = Month(Date)
> > > > > strDay = Day(Date)
> > > > > strYear = Right(Year(Date),2)
>
> > > > > strFileName = "R:\NETIQ\weeklyreports\addedrules\" & strMonth & "-" &
> > > > > strDay & "-" & strYear & ".xls"
>
> > > > > Set objExcel = CreateObject("Excel.Application")
> > > > > objExcel.Visible = True
>
> > > > > Set objWorkbook = objExcel.Workbooks.Add()
>
> > > > ' Corrected
> > > >   objWorkbook.Sheets.Add ,objWorkbook.Sheets(4),19
>
> > > > > objWorkbook.SaveAs(strFileName)
>
> > > > Tom Lavedas
> > > > ===========http://members.cox.net/tglbatch/wsh/-Hidequoted text -
> ===========http://members.cox.net/tglbatch/wsh/- Hide quoted text -

>
> - Show quoted text -

Thank you to both urkec and Tom; the last change worked urkec and the
final code is:

******************************************
dtmDate = Date

strMonth = Month(Date)
strDay = Day(Date)
strYear = Right(Year(Date),2)

strFileName = "R:\NETIQ\weeklyreports\addedrules\" & strMonth & "-" &
strDay & "-" & strYear & ".xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True


Set objWorkbook = objExcel.Workbooks.Add()

objWorkbook.Sheets.Add ,objWorkbook.Sheets(objWorkbook.Sheets.Count),
19

objWorkbook.SaveAs(strFileName)
*******************************************

Thank you again!

0 new messages