| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need some VB script please | Excel Discussion (Misc queries) | |||
| Excel Throwing Circular Errors When No Errors Exist | Excel Worksheet Functions | |||
| VB Script | Excel Worksheet Functions | |||
| Script Errors | Excel Discussion (Misc queries) | |||
| Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions | |||
Linear Mode
