Linking a Text Box to a cell in an Excel Document

  • Thread starter Thread starter TADropik
  • Start date Start date
T

TADropik

I have a very simple Continuous form displaying each record in a table.
In that table, I have a field storing a location of a unique Excel document.

Is it possible to link a text box in the Form to a cell in that Excel
Document?
In other words the data source for that text box would be a specific cell in
that Excel Document.
 
I figured it out.

I was able to create a Public function. (see below)

Then I added the following to the text boxe's Data Control source:
=IIf(IsNull([Me.FileName]),"",Get_Cell([Me.FileName],1))

Public Function Get_Cell(sFileName As String, iField As Integer)

'*** Start the excel COM
Set objExcel = GetObject("", "excel.application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

Set objWorkbook = objExcel.Workbooks.Open(sFileName)
Set objWorksheet = objWorkbook.Worksheets("TabName")
objWorksheet.Select

If iField = 1 Then
Get_Cell = objWorksheet.Cells(9, 4)
ElsfIf iField = 2 Then
Get_Cell = objWorksheet.Cells(33, 8)
ElseIf iField = 3 Then
Get_Cell = objWorksheet.Cells(33, 9)
End If

objExcel.Quit
Set objWorkbook = Nothing
Set objWorksheet = Nothing
Set objExcel = Nothing

End Function
 
Back
Top