Andrew, thanks for the additional comments and help. It is always nice to have an expert on staff
Good point about fixed value for array dimension. Setting the worksheet to the OLE.Object also works.
Had troubles eliminating the Select Case complexity though.
Here is the new code:
Code:
Function fcnGetEmbeddedLabData(oDocPassed As Object) As String()
Dim oILS As InlineShape
Dim oWS As Worksheet
Dim oWB As Workbook
Dim lngRow As Long, lngCol As Long
Dim lngRows As Long
Dim arrData() As String
For Each oILS In oDocPassed.InlineShapes
If oILS.Type = wdInlineShapeEmbeddedOLEObject Then
If oILS.OLEFormat.progID = "Excel.Sheet.12" Then
oILS.OLEFormat.Edit
'Set oWB = Workbooks(2)
Set oWB = oILS.OLEFormat.Object
Set oWS = oWB.Sheets(1)
lngCol = oWS.Cells(2, oWS.Columns.Count).End(xlToLeft).Column
lngRows = oWS.Cells(Rows.Count, "A").End(xlUp).Row
ReDim arrData(lngRows)
arrData(0) = oDocPassed.SelectContentControlsByTitle(strCCUnique).Item(1).Range.Text
For lngRow = 1 To lngRows
'If Not oWS.Cells(lngRow, lngCol).Text = vbNullString Then
'arrData(lngRow) = WorksheetFunction.IfError(oWS.Cells(lngRow, lngCol), "")
'End If
Select Case oWS.Cells(lngRow, lngCol).Text
Case Is = "#DIV/0!", "#NUM!", "#VALUE!"
Case Else
arrData(lngRow) = oWS.Cells(lngRow, lngCol).Text
End Select
Next lngRow
End If
End If
Next oILS
fcnGetEmbeddedLabData = arrData()
lbl_Exit:
Set oWB = Nothing: Set oWS = Nothing
Exit Function
End Function
If I stet out the Select Case stuff and use just your suggestion, then 1) The dates appeared in the extracted data as long values and 2) Many of the fields in the embedded sheet were extracted as "0"
If I wrapped your suggested line in the IF ... End If statement, that resolves the "0" issue, but not the long values for dates.
Any ideas? Thanks.