April 15, 2005 at 5:24 am
Can somebody let me know how to create excel file by using Filesystemobject object
I have used
dim objFS
Dim objexcel
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objexcel = objFS.OpentextFile("c:\tso\tso.txt",8,true)
Set objexcel = objFS.OpentextFile("c:\tso\test\tso1.xls",8,true)
set objfs = nothing
set objexcel = nothing
This is creating the file at the mentioned pathand have no provblem for text file but for excel file it's putting locks till the dts completes so i cannot wirte script to inset data in the file.
Also i will appreciate if somebody tell me how to use sp_OAcreate to create and use the excel file.
Above all I need excel workbook with 3 excel sheets
April 15, 2005 at 5:47 am
This script will create a new excel file and set connection so that you can write to it:
--------------------------------------------------------------------------
'**********************************************************************
' Visual Basic ActiveX Script
' Author: Jon Stokes
' Created: 10th Sep 2004
' Purpose: To create excel file and set connection
' Revision History:
'************************************************************************
Function Main()
' Initialise dimensions
Dim appExcel
Dim newBook
Dim oSheet
Dim oPackage
Dim oConn
'Set excel objects
Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.Add
Set oSheet = newBook.Worksheets(1)
' Populate heading columns for mapping purposes and display in excel
oSheet.Range("A1").Value = "Col1"
oSheet.Range("B1").Value = "Col2"
oSheet.Range("C1").Value = "Col3"
oSheet.Range("D1").Value = "Col4"
'Dynamically specify the name of the new Excel file to be created and exported to
With newBook
.SaveAs "C:\YOUREXCELFILENAME"
.save
End With
appExcel.quit
set oPackage = DTSGlobalVariables.parent
'connection 2 is to the Excel file
set oConn = oPackage.connections(2)
oConn.datasource = DTSGlobalVariables("fileName").Value
'Destroy objects
set oPackage = nothing
set oConn = nothing
Main = DTSTaskExecResult_Success 'End on success
End Function
--------------------------------------------------------------------------
April 15, 2005 at 5:52 am
I just wanted to use FilesystemObject object or sp_OAcreate to create excel file please
April 15, 2005 at 7:02 am
Not aware you can create a spreadsheet with filesystemobject. All you're doing is renaming text file with excel extension. Will cause errors when opening.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply