bobomonkey
03-20-2008, 12:54 AM
hi,
I have a bunch of functions that perform the below operations
1)create excel application instance
2)open workbook
3)copy-paste text in sheets
4)text enter formulae) in an excel sheet
Below is an example of code i have, all of it is in vbscript
Function overWriteXLsheet(SourceFilePath,Sourcesheet,DestFilePath,Destsheet)
'MsgBox "overWriteXLsheet Function called SourceFilePath"&SourceFilePath&" Sourcesheet "&Sourcesheet&" DestFilePath "&DestFilePath&" Destsheet "&Destsheet
Dim errFlag
errFlag=0
'msgbox "indside overWriteXLsheet before Set ExcelApp1 = CreateObject(Excel.Application)"
Set ExcelApp1 = CreateObject("Excel.Application")
ExcelApp1.Visible = False
ExcelApp1.DisplayAlerts= False
On Error Resume Next
set workBookObj1=ExcelApp1.Workbooks.Open(SourceFilePath)
'MsgBox "set workBookObj1=ExcelApp1.Workbooks.Open(SourceFilePath) Err.Number '"&Err.Number &"', Err.Description '"&Err.Description&"'"
'file doesn't exist
If Err.Number=1004 Then
errFlag=1
ExcelApp1.Quit
overWriteXLsheet=errFlag
Exit Function
End If
'MsgBox "Before workBookObj1.workSheets(Sourcesheet).select"
On Error Resume Next
workBookObj1.workSheets(Sourcesheet).select
'MsgBox "workBookObj1.workSheets(Sourcesheet).select Err.Number '"&Err.Number &"', Err.Description '"&Err.Description&"'"
'MsgBox Err.Number&" "&Err.Description
'sheet doesn't exist
If Err.Number=9 Then
'MsgBox "Err.Number=9 sheet doesn't exist"
errFlag=2
ExcelApp1.Quit
overWriteXLsheet=errFlag
Exit function
End If
workBookObj1.Sheets(Sourcesheet).cells.Select
ExcelApp1.selection.copy
workBookObj1.Sheets(Sourcesheet).Cells(1,1).select
Set workBookObj2=ExcelApp1.Workbooks.Open(DestFilePath)
workBookObj2.Sheets(Destsheet).cells(1,1).Select
ExcelApp1.Activesheet.Paste
workBookObj2.Sheets(Destsheet).Cells(1,1).select
workBookObj2.Save
workBookObj1.close'(False)
workBookObj2.Close
ExcelApp1.Quit
Set ExcelApp1=Nothing
Set workBookObj1=Nothing
Set workBookObj2=Nothing
overWriteXLsheet=errFlag
End Function
But now the company is planning to move to open office.
My question is
1) Is there a way to run these functions without MS excel installation?
Is it not enough to have the dlls that have the com interface for excel?
Thanks for reading.
I have a bunch of functions that perform the below operations
1)create excel application instance
2)open workbook
3)copy-paste text in sheets
4)text enter formulae) in an excel sheet
Below is an example of code i have, all of it is in vbscript
Function overWriteXLsheet(SourceFilePath,Sourcesheet,DestFilePath,Destsheet)
'MsgBox "overWriteXLsheet Function called SourceFilePath"&SourceFilePath&" Sourcesheet "&Sourcesheet&" DestFilePath "&DestFilePath&" Destsheet "&Destsheet
Dim errFlag
errFlag=0
'msgbox "indside overWriteXLsheet before Set ExcelApp1 = CreateObject(Excel.Application)"
Set ExcelApp1 = CreateObject("Excel.Application")
ExcelApp1.Visible = False
ExcelApp1.DisplayAlerts= False
On Error Resume Next
set workBookObj1=ExcelApp1.Workbooks.Open(SourceFilePath)
'MsgBox "set workBookObj1=ExcelApp1.Workbooks.Open(SourceFilePath) Err.Number '"&Err.Number &"', Err.Description '"&Err.Description&"'"
'file doesn't exist
If Err.Number=1004 Then
errFlag=1
ExcelApp1.Quit
overWriteXLsheet=errFlag
Exit Function
End If
'MsgBox "Before workBookObj1.workSheets(Sourcesheet).select"
On Error Resume Next
workBookObj1.workSheets(Sourcesheet).select
'MsgBox "workBookObj1.workSheets(Sourcesheet).select Err.Number '"&Err.Number &"', Err.Description '"&Err.Description&"'"
'MsgBox Err.Number&" "&Err.Description
'sheet doesn't exist
If Err.Number=9 Then
'MsgBox "Err.Number=9 sheet doesn't exist"
errFlag=2
ExcelApp1.Quit
overWriteXLsheet=errFlag
Exit function
End If
workBookObj1.Sheets(Sourcesheet).cells.Select
ExcelApp1.selection.copy
workBookObj1.Sheets(Sourcesheet).Cells(1,1).select
Set workBookObj2=ExcelApp1.Workbooks.Open(DestFilePath)
workBookObj2.Sheets(Destsheet).cells(1,1).Select
ExcelApp1.Activesheet.Paste
workBookObj2.Sheets(Destsheet).Cells(1,1).select
workBookObj2.Save
workBookObj1.close'(False)
workBookObj2.Close
ExcelApp1.Quit
Set ExcelApp1=Nothing
Set workBookObj1=Nothing
Set workBookObj2=Nothing
overWriteXLsheet=errFlag
End Function
But now the company is planning to move to open office.
My question is
1) Is there a way to run these functions without MS excel installation?
Is it not enough to have the dlls that have the com interface for excel?
Thanks for reading.