Extract data form one Excel file to another

gmaxey · #5 06-08-2023, 03:46 AM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Extract data from a Word file.	donlincolnmsof	Word VBA	3	08-31-2019 05:27 AM
Extract data from HTML File.	donlincolnmsof	Word VBA	5	08-26-2019 08:36 PM
Extract data from HTML File.	donlincolnmsof	Word VBA	0	03-07-2019 12:17 PM
Outlook 2013 Forms (how to questions)- Quick opening of a form file & Linking form's data to Excel	gamin2407	Outlook	0	01-21-2017 10:14 PM
Macro to highlight repeated words in word file and extract into excel file	aabri	Word VBA	1	06-14-2015 07:20 AM