create an empty excel file using vbscript 
Author Message
 create an empty excel file using vbscript

Still new at this and would appreciate any help.
I am trying to create an empty excel (*.xls) file using ActiveX (VBScript)
to use with a DTS package. I have found an article on how to create an
access file, but still haven't figured out how to create an excel file.

http://www.*-*-*.com/ ,101,288,0,1
 Dim oCatalog
 Set oCatalog = CreateObject("ADOX.Catalog")
 oCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\MyAccessDB.mdb"
 Set oCatalog = Nothing

Thanks for any help!



Fri, 14 Oct 2005 08:20:50 GMT  
 create an empty excel file using vbscript
hi

I have not worked on ASP.Net but in asp you can do this
using this
-----creation
     set rfile =createobject("Excel.Application")
     set rWork =rfile.Workbooks.Add
-----saving
     rfile.ActiveWorkbook.SaveAs strthefile & "\test.xls"
where strthefile is server.mappath where you want to store
the file in the server.

with regards,
Rams
i think you may not have problem in converting this code
to asp.net format

Quote:
>-----Original Message-----
>Still new at this and would appreciate any help.
>I am trying to create an empty excel (*.xls) file using
ActiveX (VBscript)
>to use with a DTS package. I have found an article on how
to create an
>access file, but still haven't figured out how to create
an excel file.

>http://www.sqldts.com/default.aspx?6,101,288,0,1
> Dim oCatalog
> Set oCatalog = CreateObject("ADOX.Catalog")
> oCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data
>Source=C:\MyAccessDB.mdb"
> Set oCatalog = Nothing

>Thanks for any help!

>.



Fri, 14 Oct 2005 17:26:44 GMT  
 create an empty excel file using vbscript
Hi Sam,

You could create Excel file using Jet provider, but only thing is you have
to create at least one spreadsheet inside that file. Otherwise file will not
be created. Your connection string would look like

  oCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data
 Source=C:\Book1.xls;Extended Properties=""Excel 8.0;"""

Check next KBs with some examples how to work with Excel from ADO

http://support.microsoft.com/default.aspx?scid=kb;en-us;278973

http://support.microsoft.com/default.aspx?scid=kb;en-us;326548

--
Val Mazur
Microsoft MVP


Quote:
> Still new at this and would appreciate any help.
> I am trying to create an empty excel (*.xls) file using ActiveX (VBscript)
> to use with a DTS package. I have found an article on how to create an
> access file, but still haven't figured out how to create an excel file.

> http://www.sqldts.com/default.aspx?6,101,288,0,1
>  Dim oCatalog
>  Set oCatalog = CreateObject("ADOX.Catalog")
>  oCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\MyAccessDB.mdb"
>  Set oCatalog = Nothing

> Thanks for any help!



Fri, 14 Oct 2005 20:02:35 GMT  
 create an empty excel file using vbscript

Still new at this and would appreciate any help.
I am trying to create an empty excel (*.xls) file using ActiveX (VBscript)
to use with a DTS package. I have found an article on how to create an
access file, but still haven't figured out how to create an excel file.

http://www.sqldts.com/default.aspx?6,101,288,0,1
 Dim oCatalog
 Set oCatalog = CreateObject("ADOX.Catalog")
 oCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\MyAccessDB.mdb"
 Set oCatalog = Nothing

You can't use the Create method. You can use the ActiveConnection property/method for the Catalog
object to create your new Excel Workbook, but must add a table (Worksheet), as Val mentioned, or the
Workbook file will not be saved:

Function CreateExcel()

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Set cat = New ADOX.Catalog

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\My Documents\TestExcel.xls;Extended Properties=Excel 8.0"

Set tbl = New ADOX.Table
tbl.NAME = "TestTable"
Set col = New ADOX.Column
With col
    .NAME = "Col6"
    .Type = adVarWChar
    .DefinedSize = 12
End With
tbl.Columns.Append col

Set col = New ADOX.Column
With col
    .NAME = "Col1"
    .Type = adDouble
End With
tbl.Columns.Append col
Set col = Nothing
Set col = New ADOX.Column
With col
    .NAME = "Col2"
    .Type = adVarWChar
End With
tbl.Columns.Append col
cat.Tables.Append tbl

Set cat = Nothing

End Function


Microsoft MVP (Visual Basic)



Fri, 14 Oct 2005 23:35:59 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Using VBScript to create a hyperlink in Excel?

2. how to select columns using macro/vbscript from a readonly excel file

3. Exporting Outlook Contacts to an Excel spreadsheet or to a txt file by using VBScript

4. Reading Excel file using VBScript

5. Create and Edit Files from WSH using VBScript

6. create Excel file using VBA

7. Creating Excel files using BIFF **URGENT**

8. How do I empty a frame using VBScript ?

9. SCRIPT using ADO file download empty file

10. How do I empty a frame using VBScript ?

11. creating error messages for empty text boxes using VB

12. Using catalog.create to create a new Excel database

 

 
Powered by phpBB® Forum Software