Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheets.add in VBScript overwrites existing worksheet
Background: Opening a tab delimited file, generated by netsh, with Excel to
format the data. Formatting includes creating new worksheets. Here's the short code in vbscript: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Set objExcel = WScript.CreateObject("Excel.Application") 'Opening existing Workbook Set objExcelWorkbook = objExcel.Workbooks.Open(filename) 'Adding worksheet to opened, existing workbook objExcel.Worksheets.Add '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''' What happens with the above code is the data in the existing worksheet gets overwritten, but the name of the worksheet is retained. Value add: Any good reference on vbscriipt, Excel specific objects & methods would be appreciated. VBA references tend toward VB and not VBScript, which means you waste a lot of time trying to figure out what objects and methods are available and how they should be formatted. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheets.add in VBScript overwrites existing worksheet
I've narrowed it down to the fact that the output file is not
xlWorkbookNormal format. I can't seem to change that when I do a SaveAs to a new name. I get a message that the object doesn't support that method, i.e., FileFormat:=. Any help still appreciated. "donparkerjr" wrote: Background: Opening a tab delimited file, generated by netsh, with Excel to format the data. Formatting includes creating new worksheets. Here's the short code in vbscript: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Set objExcel = WScript.CreateObject("Excel.Application") 'Opening existing Workbook Set objExcelWorkbook = objExcel.Workbooks.Open(filename) 'Adding worksheet to opened, existing workbook objExcel.Worksheets.Add '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''' What happens with the above code is the data in the existing worksheet gets overwritten, but the name of the worksheet is retained. Value add: Any good reference on vbscriipt, Excel specific objects & methods would be appreciated. VBA references tend toward VB and not VBScript, which means you waste a lot of time trying to figure out what objects and methods are available and how they should be formatted. Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheets.add in VBScript overwrites existing worksheet
How about
objExcelWorkbook.Worksheets.Add? Does it demonstrate the same behavior? HTH Kostis Vezerides donparkerjr wrote: Background: Opening a tab delimited file, generated by netsh, with Excel to format the data. Formatting includes creating new worksheets. Here's the short code in vbscript: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Set objExcel = WScript.CreateObject("Excel.Application") 'Opening existing Workbook Set objExcelWorkbook = objExcel.Workbooks.Open(filename) 'Adding worksheet to opened, existing workbook objExcel.Worksheets.Add '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''' What happens with the above code is the data in the existing worksheet gets overwritten, but the name of the worksheet is retained. Value add: Any good reference on vbscriipt, Excel specific objects & methods would be appreciated. VBA references tend toward VB and not VBScript, which means you waste a lot of time trying to figure out what objects and methods are available and how they should be formatted. Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheets.add in VBScript overwrites existing worksheet
It works... I know I tried that, too (I even have it commented out in my
code), but it didn't work before. How frustrating. Thank you for your solution. "vezerid" wrote: How about objExcelWorkbook.Worksheets.Add? Does it demonstrate the same behavior? HTH Kostis Vezerides donparkerjr wrote: Background: Opening a tab delimited file, generated by netsh, with Excel to format the data. Formatting includes creating new worksheets. Here's the short code in vbscript: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Set objExcel = WScript.CreateObject("Excel.Application") 'Opening existing Workbook Set objExcelWorkbook = objExcel.Workbooks.Open(filename) 'Adding worksheet to opened, existing workbook objExcel.Worksheets.Add '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''' What happens with the above code is the data in the existing worksheet gets overwritten, but the name of the worksheet is retained. Value add: Any good reference on vbscriipt, Excel specific objects & methods would be appreciated. VBA references tend toward VB and not VBScript, which means you waste a lot of time trying to figure out what objects and methods are available and how they should be formatted. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If then formulas | Excel Worksheet Functions | |||
How:Add New Worksheet that copies the cell of the previous sheet | Excel Worksheet Functions | |||
is it possible to apply template to a existing worksheet | Excel Worksheet Functions | |||
How do I convert an existing MS Excel worksheet tracking a simple. | Excel Discussion (Misc queries) | |||
Creating new worksheet from existing | Excel Discussion (Misc queries) |