Creating and Saving Excel Sheets 
Author Message
 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  
 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  
 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  
 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  
 
 [ 4 post ] 

 Relevant Pages 

1. Close and Save Excel Sheet from Access

2. Help - Saving Excel Sheet from Access97 Object

3. How to save data from vb.net to xml doc and Excel Sheet

4. Question: Saving RDO resultset into Excel sheet

5. Saving a Single Sheet in Excel

6. saving an Excel sheet

7. Saving an Excel Sheet from VB 5

8. How do I save an Excel sheet using OLE

9. Saving Excel Sheet

10. HOW TO CREATE A DYNAMIC ROWS IN THE TABLE AS IN EXCEL SHEET

11. Creating Excel-sheet with VBS-problem

12. Create Excel-sheet with VB4 ??

 

 
Powered by phpBB® Forum Software