| || |
Automate Excel interface
I have a application that loads/embeds Excel into the application UI, and I believe it is using the desktop install of Excel to do this. I want to be able to get down to the cell level using the Excel Object Library, but can I do this if the application is launching the Excel app, or would I have to had opened it using the Excel.Application object? (which I can not control in this case) Thx!
Re: Automate Excel interface
If excel.exe is shown in the window process list, then you may be able to hook into it using:
set oExcel = getobject(,"Excel.application")
This should return you an interface to the Excel object just same as if you used "set oExcel = createobject("Excel.application")"
I must admit I've never tried this on an embedded instance of the any of the Office Suite, but I have used it to hook into instances I havn't personally created (Outlook & Word at least anyway).
<Added:> I've just given it a try, and it appears to work e.g.
oExcel.activesheet.range("A1").value = "X"
changed the value of cell A1 in my embedded excel workbook.
[ 06-13-2004, 07:06 PM: Message edited by: B Straka ]
Re: Automate Excel interface
Thanks B... I was able to get it working. Here is some functions that you all may find useful...
</font><blockquote><font size="1" face="Verdana, Arial, Helvetica">code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">'You must make a reference to Microsoft Excel for this script to work
Public w As Excel.Workbook
Public ws As Worksheet
Public myExcel As Object
'Write the Text to the Cell
WriteText "A2", "Alias"
'Get the text from the cell
theCellText = GetText("A2"
MsgBox "The Cell Text of A2 is: " & theCellText
'Get the text using row col. (Array used in function)
theCellText = GetInfo(4, 2)
MsgBox "The Cell Text of Row 4, Col 2 is: " & theCellText
Public Function GetInfo(theRow As Integer, theCol)
Dim i As Integer 'counter
Dim CurRow As Integer 'current row
Dim CurCol As Integer 'current column
Dim x() As String 'array for customer information
CurRow = theRow
i = 0
Do While ws.Cells(CurRow, 1) <> ""
CurCol = 1
Do While ws.Cells(CurRow, CurCol) <> ""
ReDim Preserve x(i)
x(i) = ws.Cells(CurRow, CurCol)
i = i + 1
CurCol = CurCol + 1
CurRow = CurRow + 1
GetInfo = x(theCol - 1) 'return the array of customer information
Public Function setup() 'Can pass in FileName - if so use commented code instead
'Work with the Excel app that is already open on desktop
Set myExcel = GetObject(, "Excel.Application"
Set w = myExcel.Workbooks(1)
Set ws = w.Worksheets(1) 'sets the desired worksheet
' 'if working with specific file name use this instead----
' Set w = GetObject(FileName) 'sets the file specified by script function call
' Set ws = w.Worksheets(1) 'sets the desired worksheet
Public Function WriteText(theCell, theText)
ws.Range(theCell).Value = theText
Public Function GetText(theCell)
GetText = ws.Range(theCell).Text
End Function</pre><hr /></blockquote><font size="2" face="Verdana, Arial, Helvetica">