macro linking to excel
 

How can I create a macro where Outlook can retrieve certain cells from Excel.

Thanks!

macro linking to excel
 

"Judy" wrote in message
...
: How can I create a macro where Outlook can retrieve certain cells from
Excel.
:
: Thanks!

Here's a copy of a post from Jake Marx discussing connecting to Excel using
ADO

<quote
Another way to do this is to use ADO. Here's a function that will return
the value in cell A1 of the first worksheet in a closed workbook. There is
one drawback, however - your data in cell A1 must be non-numeric. If it's a
number, you'll get an invalid result back. So only use this if you expect
all values in A1 to be text. (This function would work fine for any type of
value if you were looking at cells in row 2 and below, FWIW.)


To use this, first set a reference to the "Microsoft ActiveX Data Objects
2.x Library" through Tools | References.


Regards,
Jake Marx


Function vGetA1FromXLFile(rsFullPath As String) As Variant
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset


On Error GoTo ErrHandler


Set cn = New ADODB.Connection
cn.Open "Provider=MSDASQL.1;Data Source=Excel Files;" _
& "Initial Catalog=" & rsFullPath
Set rs = cn.Execute("[A1:A1]")
vGetA1FromXLFile = rs.Fields(0).Name


ExitRoutine:
If Not rs Is Nothing Then
If rs.State = adStateOpen Then rs.Close
Set rs = Nothing
End If
If Not cn Is Nothing Then
If cn.State = adStateOpen Then cn.Close
Set cn = Nothing
End If
Exit Function
ErrHandler:
Resume ExitRoutine
End Function


</quote