Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #6  
Old 06-08-2023, 04:26 AM
gmaxey gmaxey is offline Extract data form one Excel file to another Windows 10 Extract data form one Excel file to another Office 2019
Expert
Extract data form one Excel file to another
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,636
gmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nice
Default

Andrew,


Monkey learns! I figured out how to employ your IsError function without affecting the date values:

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 WorksheetFunction.IsFormula(oWS.Cells(lngRow, lngCol)) Then
            arrData(lngRow) = WorksheetFunction.IfError(oWS.Cells(lngRow, lngCol), "")
          Else
            arrData(lngRow) = oWS.Cells(lngRow, lngCol).Text
          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
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
 



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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:21 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft