ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Script Errors Out (https://www.excelbanter.com/excel-discussion-misc-queries/143298-script-errors-out.html)

Jeff C
Script Errors Out
 
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("Existing.xls")

objWorkbook.Worksheets(1).Copy before_
objWorkbook1.Worksheets(1)

objWorkbook1.Close SaveChanges = True
objWorkbook.Close

There is one worksheet in New.xls which I want to copy and add to
Exisiting.xls as the top tab. The above errors out and I have re-written so
many times I can't remember where I started. I also wanted to name the new
worksheet with the date but couldn't get syntax that would work.

This is a *.vbs text file that I want to place in the task scheduler.

Am using MS Office Pro 2003. I appreciate any assist. Thanks
--
Jeff C
Live Well .. Be Happy In All You Do

Jim Rech
Script Errors Out
 
You cannot use named arguments in a script, only by position.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close

--
Jim
"Jeff C" wrote in message
...
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("Existing.xls")

objWorkbook.Worksheets(1).Copy before_
objWorkbook1.Worksheets(1)

objWorkbook1.Close SaveChanges = True
objWorkbook.Close

There is one worksheet in New.xls which I want to copy and add to
Exisiting.xls as the top tab. The above errors out and I have re-written
so
many times I can't remember where I started. I also wanted to name the
new
worksheet with the date but couldn't get syntax that would work.

This is a *.vbs text file that I want to place in the task scheduler.

Am using MS Office Pro 2003. I appreciate any assist. Thanks
--
Jeff C
Live Well .. Be Happy In All You Do



Jeff C
Script Errors Out
 
Thank you very much, I was so close but too far to figure out! I am
appreciative. Would someone share the syntax to get the current date placed
in the name of the tab?
--
Jeff C
Live Well .. Be Happy In All You Do


"Jim Rech" wrote:

You cannot use named arguments in a script, only by position.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close

--
Jim
"Jeff C" wrote in message
...
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("Existing.xls")

objWorkbook.Worksheets(1).Copy before_
objWorkbook1.Worksheets(1)

objWorkbook1.Close SaveChanges = True
objWorkbook.Close

There is one worksheet in New.xls which I want to copy and add to
Exisiting.xls as the top tab. The above errors out and I have re-written
so
many times I can't remember where I started. I also wanted to name the
new
worksheet with the date but couldn't get syntax that would work.

This is a *.vbs text file that I want to place in the task scheduler.

Am using MS Office Pro 2003. I appreciate any assist. Thanks
--
Jeff C
Live Well .. Be Happy In All You Do




Jeff C
Script Errors Out
 
Got It, Thank you for your help!!!

strYear = Right(DatePart("yyyy",Date), 2)
strMonth = Right(100+DatePart("m",Date),2)
strDay = Right(100+DatePart("d",Date),2)


Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objworkbook.worksheets(1).name = strMonth & strDay & strYear

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close True
--
Jeff C
Live Well .. Be Happy In All You Do


"Jim Rech" wrote:

You cannot use named arguments in a script, only by position.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close

--
Jim
"Jeff C" wrote in message
...
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("Existing.xls")

objWorkbook.Worksheets(1).Copy before_
objWorkbook1.Worksheets(1)

objWorkbook1.Close SaveChanges = True
objWorkbook.Close

There is one worksheet in New.xls which I want to copy and add to
Exisiting.xls as the top tab. The above errors out and I have re-written
so
many times I can't remember where I started. I also wanted to name the
new
worksheet with the date but couldn't get syntax that would work.

This is a *.vbs text file that I want to place in the task scheduler.

Am using MS Office Pro 2003. I appreciate any assist. Thanks
--
Jeff C
Live Well .. Be Happy In All You Do




Dave Peterson
Script Errors Out
 
Or

objworkbook.worksheets(1).name = format(date,"mmddyy")



Jeff C wrote:

Got It, Thank you for your help!!!

strYear = Right(DatePart("yyyy",Date), 2)
strMonth = Right(100+DatePart("m",Date),2)
strDay = Right(100+DatePart("d",Date),2)

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objworkbook.worksheets(1).name = strMonth & strDay & strYear

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close True
--
Jeff C
Live Well .. Be Happy In All You Do

"Jim Rech" wrote:

You cannot use named arguments in a script, only by position.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close

--
Jim
"Jeff C" wrote in message
...
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("Existing.xls")

objWorkbook.Worksheets(1).Copy before_
objWorkbook1.Worksheets(1)

objWorkbook1.Close SaveChanges = True
objWorkbook.Close

There is one worksheet in New.xls which I want to copy and add to
Exisiting.xls as the top tab. The above errors out and I have re-written
so
many times I can't remember where I started. I also wanted to name the
new
worksheet with the date but couldn't get syntax that would work.

This is a *.vbs text file that I want to place in the task scheduler.

Am using MS Office Pro 2003. I appreciate any assist. Thanks
--
Jeff C
Live Well .. Be Happy In All You Do




--

Dave Peterson

Jeff C
Script Errors Out
 
I tried that but every time I used Format, I got errors

Why?
--
Jeff C
Live Well .. Be Happy In All You Do


"Dave Peterson" wrote:

Or

objworkbook.worksheets(1).name = format(date,"mmddyy")



Jeff C wrote:

Got It, Thank you for your help!!!

strYear = Right(DatePart("yyyy",Date), 2)
strMonth = Right(100+DatePart("m",Date),2)
strDay = Right(100+DatePart("d",Date),2)

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objworkbook.worksheets(1).name = strMonth & strDay & strYear

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close True
--
Jeff C
Live Well .. Be Happy In All You Do

"Jim Rech" wrote:

You cannot use named arguments in a script, only by position.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close

--
Jim
"Jeff C" wrote in message
...
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("Existing.xls")

objWorkbook.Worksheets(1).Copy before_
objWorkbook1.Worksheets(1)

objWorkbook1.Close SaveChanges = True
objWorkbook.Close

There is one worksheet in New.xls which I want to copy and add to
Exisiting.xls as the top tab. The above errors out and I have re-written
so
many times I can't remember where I started. I also wanted to name the
new
worksheet with the date but couldn't get syntax that would work.

This is a *.vbs text file that I want to place in the task scheduler.

Am using MS Office Pro 2003. I appreciate any assist. Thanks
--
Jeff C
Live Well .. Be Happy In All You Do




--

Dave Peterson

Jeff C
Script Errors Out
 
Error Type Mismatch 'format'
--
Jeff C
Live Well .. Be Happy In All You Do


"Dave Peterson" wrote:

Or

objworkbook.worksheets(1).name = format(date,"mmddyy")



Jeff C wrote:

Got It, Thank you for your help!!!

strYear = Right(DatePart("yyyy",Date), 2)
strMonth = Right(100+DatePart("m",Date),2)
strDay = Right(100+DatePart("d",Date),2)

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objworkbook.worksheets(1).name = strMonth & strDay & strYear

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close True
--
Jeff C
Live Well .. Be Happy In All You Do

"Jim Rech" wrote:

You cannot use named arguments in a script, only by position.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close

--
Jim
"Jeff C" wrote in message
...
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("Existing.xls")

objWorkbook.Worksheets(1).Copy before_
objWorkbook1.Worksheets(1)

objWorkbook1.Close SaveChanges = True
objWorkbook.Close

There is one worksheet in New.xls which I want to copy and add to
Exisiting.xls as the top tab. The above errors out and I have re-written
so
many times I can't remember where I started. I also wanted to name the
new
worksheet with the date but couldn't get syntax that would work.

This is a *.vbs text file that I want to place in the task scheduler.

Am using MS Office Pro 2003. I appreciate any assist. Thanks
--
Jeff C
Live Well .. Be Happy In All You Do




--

Dave Peterson

Dave Peterson
Script Errors Out
 
Did you use Date as a variable or were you trying to use VBA's Date function?

If you open the VBE and put:

msgbox format(date,"mmddyy")

in the immediate window, does it work?

Jeff C wrote:

Error Type Mismatch 'format'
--
Jeff C
Live Well .. Be Happy In All You Do

"Dave Peterson" wrote:

Or

objworkbook.worksheets(1).name = format(date,"mmddyy")



Jeff C wrote:

Got It, Thank you for your help!!!

strYear = Right(DatePart("yyyy",Date), 2)
strMonth = Right(100+DatePart("m",Date),2)
strDay = Right(100+DatePart("d",Date),2)

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objworkbook.worksheets(1).name = strMonth & strDay & strYear

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close True
--
Jeff C
Live Well .. Be Happy In All You Do

"Jim Rech" wrote:

You cannot use named arguments in a script, only by position.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close

--
Jim
"Jeff C" wrote in message
...
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("Existing.xls")

objWorkbook.Worksheets(1).Copy before_
objWorkbook1.Worksheets(1)

objWorkbook1.Close SaveChanges = True
objWorkbook.Close

There is one worksheet in New.xls which I want to copy and add to
Exisiting.xls as the top tab. The above errors out and I have re-written
so
many times I can't remember where I started. I also wanted to name the
new
worksheet with the date but couldn't get syntax that would work.

This is a *.vbs text file that I want to place in the task scheduler.

Am using MS Office Pro 2003. I appreciate any assist. Thanks
--
Jeff C
Live Well .. Be Happy In All You Do




--

Dave Peterson


--

Dave Peterson

Jeff C
Script Errors Out
 
I am running this in a *.vbs file and finding a challenge getting all the
code translated from VB to VBS
--
Jeff C
Live Well .. Be Happy In All You Do


"Dave Peterson" wrote:

Did you use Date as a variable or were you trying to use VBA's Date function?

If you open the VBE and put:

msgbox format(date,"mmddyy")

in the immediate window, does it work?

Jeff C wrote:

Error Type Mismatch 'format'
--
Jeff C
Live Well .. Be Happy In All You Do

"Dave Peterson" wrote:

Or

objworkbook.worksheets(1).name = format(date,"mmddyy")



Jeff C wrote:

Got It, Thank you for your help!!!

strYear = Right(DatePart("yyyy",Date), 2)
strMonth = Right(100+DatePart("m",Date),2)
strDay = Right(100+DatePart("d",Date),2)

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objworkbook.worksheets(1).name = strMonth & strDay & strYear

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close True
--
Jeff C
Live Well .. Be Happy In All You Do

"Jim Rech" wrote:

You cannot use named arguments in a script, only by position.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close

--
Jim
"Jeff C" wrote in message
...
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("Existing.xls")

objWorkbook.Worksheets(1).Copy before_
objWorkbook1.Worksheets(1)

objWorkbook1.Close SaveChanges = True
objWorkbook.Close

There is one worksheet in New.xls which I want to copy and add to
Exisiting.xls as the top tab. The above errors out and I have re-written
so
many times I can't remember where I started. I also wanted to name the
new
worksheet with the date but couldn't get syntax that would work.

This is a *.vbs text file that I want to place in the task scheduler.

Am using MS Office Pro 2003. I appreciate any assist. Thanks
--
Jeff C
Live Well .. Be Happy In All You Do




--

Dave Peterson


--

Dave Peterson

Dave Peterson
Script Errors Out
 
Ahhhh.

Sorry. I should have read the thread...

This may be a little cleaner.

objworkbook.worksheets(1).name = Right(0 & Month(Date), 2) & _
Right(0 & Day(Date), 2) & _
Right(Year(Date), 2)

There may be better ways of writing this--but I suffer that same problem with
scripts.



Jeff C wrote:

I am running this in a *.vbs file and finding a challenge getting all the
code translated from VB to VBS
--
Jeff C
Live Well .. Be Happy In All You Do

"Dave Peterson" wrote:

Did you use Date as a variable or were you trying to use VBA's Date function?

If you open the VBE and put:

msgbox format(date,"mmddyy")

in the immediate window, does it work?

Jeff C wrote:

Error Type Mismatch 'format'
--
Jeff C
Live Well .. Be Happy In All You Do

"Dave Peterson" wrote:

Or

objworkbook.worksheets(1).name = format(date,"mmddyy")



Jeff C wrote:

Got It, Thank you for your help!!!

strYear = Right(DatePart("yyyy",Date), 2)
strMonth = Right(100+DatePart("m",Date),2)
strDay = Right(100+DatePart("d",Date),2)

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objworkbook.worksheets(1).name = strMonth & strDay & strYear

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close True
--
Jeff C
Live Well .. Be Happy In All You Do

"Jim Rech" wrote:

You cannot use named arguments in a script, only by position.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("c:\Existing.xls")

objWorkbook.Worksheets(1).Copy objWorkbook1.Worksheets(1)

objWorkbook1.Close True
objWorkbook.Close

--
Jim
"Jeff C" wrote in message
...
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("Existing.xls")

objWorkbook.Worksheets(1).Copy before_
objWorkbook1.Worksheets(1)

objWorkbook1.Close SaveChanges = True
objWorkbook.Close

There is one worksheet in New.xls which I want to copy and add to
Exisiting.xls as the top tab. The above errors out and I have re-written
so
many times I can't remember where I started. I also wanted to name the
new
worksheet with the date but couldn't get syntax that would work.

This is a *.vbs text file that I want to place in the task scheduler.

Am using MS Office Pro 2003. I appreciate any assist. Thanks
--
Jeff C
Live Well .. Be Happy In All You Do




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 03:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com