vbscript, Creating and Saving Excel Sheets

Creating and Saving Excel Sheets

Author	Message
Axel Strasse #1 / 4	Creating and Saving Excel Sheets In my script I am using the following code: ----------------------------------------------------------- Const OutDir = "c:\temp\" Const ExcelFile = "Excelfile.xls" Const xlWBATWorksheet = -4167 Dim fso ' Filesystem Variable Dim objXL, objWb, objWs ' Excel-Objektvariable Set objXL = WScript.CreateObject("Excel.Application") Set fso = CreateObject("Scripting.FileSystemObject") Set objWb = objXl.Workbooks objWb.Add (xlWBATWorksheet) Set objWs = objXL.ActiveWorkBook.WorkSheets(1) objWs.Name = "test" objXl.Save OutDir & ExcelFile objWb.Close objXl.Quit Set objXL = Nothing ----------------------------------------------------------- My problem is now, that the script seems to create to spreadsheets. One is saved in the proper place and than I will be ask for a second filename. Both files are looking identical, both files have a sheet called "Test". If I am opening the Excelfile.xls the spreadsheet has the name tabelle1.xls. What's wrong with my script ? Axel
Sat, 07 May 2005 02:28:51 GMT

Armi #2 / 4	Creating and Saving Excel Sheets Hi Axel, check the Save Method of Excel...."The first time you save a workbook, use the SaveAs method to specify a name for the file." Greetings Armin Quote: > In my script I am using the following code: > ----------------------------------------------------------- > Const OutDir = "c:\temp\" > Const ExcelFile = "Excelfile.xls" > Const xlWBATWorksheet = -4167 > Dim fso ' Filesystem Variable > Dim objXL, objWb, objWs ' Excel-Objektvariable > Set objXL = WScript.CreateObject("Excel.Application") > Set fso = CreateObject("Scripting.FileSystemObject") > Set objWb = objXl.Workbooks > objWb.Add (xlWBATWorksheet) > Set objWs = objXL.ActiveWorkBook.WorkSheets(1) > objWs.Name = "test" > objXl.Save OutDir & ExcelFile > objWb.Close > objXl.Quit > Set objXL = Nothing > ----------------------------------------------------------- > My problem is now, that the script seems to create to spreadsheets. > One is saved in the proper place and than I will be ask for a second > filename. Both files are looking identical, both files have a sheet called > "Test". If I am opening the Excelfile.xls the spreadsheet has the name > tabelle1.xls. What's wrong with my script ? > Axel
Sat, 07 May 2005 06:18:50 GMT

Axel Strasse #3 / 4	Creating and Saving Excel Sheets Hi Armin, thx for your answer. In the meantime I fixed one problem: ------------------------------------------------------------- Const ExcelFile = "Excelfile.xls" Const OutDir = "c:\temp\" Const xlWBATWorksheet = -4167 Set objXL = WScript.CreateObject("Excel.Application") Set objWb = objXl.Workbooks objWB.Add (xlWBATWorksheet) Set objWS = objXL.ActiveWorkBook.ActiveSheet objWS.Name = "Test" objXL.ActiveWorkBook.SaveAs OutDir & ExcelFile objWB.Close objXL.Quit Set objXL = Nothing ---------------------------------------------------------------- One problem has been that I was saving the Excel object and not the workbook , the second one, as you mentioned, the SaveAs.but the behavior of the new Excelfile is a little bit strange. It seems that the filename is Excelfile.xls, but the spreadsheet has the name "table1.xls". Why ? And why I have to define an "BuildIn" Excel Constant "xlWBATWorksheet" ? If I am not defining it, its also not working. Axel
Sat, 07 May 2005 06:48:09 GMT

Armi #4 / 4	Creating and Saving Excel Sheets Hi ! try using Quote: >>objWB.Add instead of >>objWB.Add (xlWBATWorksheet) otherwise you just add another WB into your project.... greetings Armin Quote: > Hi Armin, > thx for your answer. In the meantime I fixed one problem: > ------------------------------------------------------------- > Const ExcelFile = "Excelfile.xls" > Const OutDir = "c:\temp\" > Const xlWBATWorksheet = -4167 > Set objXL = WScript.CreateObject("Excel.Application") > Set objWb = objXl.Workbooks > objWB.Add (xlWBATWorksheet) > Set objWS = objXL.ActiveWorkBook.ActiveSheet > objWS.Name = "Test" > objXL.ActiveWorkBook.SaveAs OutDir & ExcelFile > objWB.Close > objXL.Quit > Set objXL = Nothing > ---------------------------------------------------------------- > One problem has been that I was saving the Excel object and not the workbook > , > the second one, as you mentioned, the SaveAs.but the behavior of the new > Excelfile > is a little bit strange. It seems that the filename is Excelfile.xls, but > the spreadsheet > has the name "table1.xls". Why ? And why I have to define an "BuildIn" Excel > Constant "xlWBATWorksheet" ? If I am not defining it, its also not working. > Axel
Sat, 07 May 2005 07:12:10 GMT

Page 1 of 1

[ 4 post ]