Solved: Is MS excel installation necessary to run VBA code?

[bobomonkey]

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.

[Bob Phillips]

No, if you want to use the Excel functionality you have to have the Excel executable.

[bobomonkey]

What about excel viewer, will it do?

[RichardSchollar]

Hi

That's a no as well I'm afraid (AFAIK). You'd need to retain a full copy of Excel to use its objects/functions.

Richard

[JonPeltier]

But now the company is planning to move to open office.
Wow, I didn't realize any companies were actually so "open-minded". So much of the value of Microsoft Office is in its ability to be customized using VBA.

[bobomonkey]

thanks guys. I guess have to learn the open office api's and recode the whole library.


Wow, I didn't realize any companies were actually so "open-minded". So much of the value of Microsoft Office is in its ability to be customized using VBA.
It's a cost consideration. We are a 100 people company, MS does not give discounts to small companies.

[tpoynton]

this link is old, but I wonder if any progress has been made.

http://www.linux.com/feature/58348

a converter?

http://www.business-spreadsheets.com/vba2oo.asp

wiki

http://wiki.services.openoffice.org/wiki/VBA

I have not done anything with converting VBA to OO, but have thought about it. I think more than I do...

not sure how extensive your VBA projects are, but it can get quite expensive to convert everything, too, in addition to retraining and associated loss of productivity. MS has put out papers making this cost comparison, no? :)