How to edit Linked Objects using Word VBA

How to open and edit Linked Excel files from Word using VBA

One can insert an object in word by either linking or embedding. We have already seen How to Read and Edit Embedded objects using VBA, The following code will throw light on accessing a linked object from Word (Excel sheet) and editing the same.

Sub Edit_Linked_Excel_Objects()




Dim oXL As Excel.Application ' Excel App Object

Dim oWB As Excel.Workbook ' Workbook Object

Dim sWB As String ' Linked String

Dim oIShape As InlineShape ' Inline Shape Object



On Error GoTo Err_Report



Set oXL = New Excel.Application



For Each oIShape In ActiveDocument.InlineShapes

If InStr(1, oIShape.OLEFormat.ProgID, "Excel") Then



' Check if the Object is Linked

If oIShape.Type = wdInlineShapeLinkedOLEObject Then



' Get the Source Name of Linked Workbook

sWB = oIShape.LinkFormat.SourceFullName



If Len(Dir(sWB)) <> 0 Then

Set oWB = oXL.Workbooks.Open(sWB, , False)

oWB.Sheets(1).Range("A1").Value = "ID"

oWB.Save

oWB.Close False

oIShape.LinkFormat.Update

Else

MsgBox "Linked file not found"

End If

End If

End If







Next oIShape



Finally:



oXL.Quit

If Not oXL Is Nothing Then Set oXL = Nothing

If Not oWB Is Nothing Then Set oWB = Nothing

If Not oIShape Is Nothing Then Set oIShape = Nothing



Exit Sub

Err_Report:

MsgBox Err.Description & " - " & Err.Number

Err.Clear

GoTo Finally



End Sub

Hide Sheet Tabs using VBA / Hide Excel Sheet Tabs (2007/2010)

How to Hide Excel Sheet Names using VBA

If you want to hide the Sheet Tab (as shown below) you can do that using Excel Options

Hide / Unhide Sheet Tab using Excel VBA

Uncheck the Show sheet tabs checkbox from Advanced Tab of Options Menu

Hide / Unhide Sheet Tab using Excel VBA

You can do the same through Excel VBA

ActiveWindow.DisplayWorkbookTabs = False

How to Read Excel Sheet embedded in Word Document using VBA

How to edit Embedded Objects (Excel Workbook) using Word VBA

In our previous posts we have seen how to Embedd an Word Document in Excel Object . Now let us try to read Excel spreadsheet embedded in Word document.

Excel Object Libarary Reference

You need to add a reference to the Excel Object Libary as shown above from Tools --> References from Visual Basic Editor (VBE)

The code loops through the available InlineShapes and activates them if they are Excel Spreadsheet. Then it is assigned to an Excel workbook object, which can be programatically handled.

Sub Edit_Embedded_Excel_Objects()

Dim oWB As Excel.Workbook
Dim oIShape As InlineShape


For Each oIShape In ActiveDocument.InlineShapes
    If InStr(1, oIShape.OLEFormat.ProgID, "Excel") Then
        oIShape.OLEFormat.Activate
        Set oWB = oIShape.OLEFormat.Object
        oWB.Sheets(1).Range("A1").Value = "ProdID"
    End If
Next oIShape

End Sub

The code edits the value of the cell as shown below:

Edited Embedded Object using VBA

See how other Embedded objects are programmed

How to Extract All Formula's in Excel Sheet using VBA

Highlight all cells containing Formulas using Excel VBA

The following snippet highlights all cells that contain formula

Sub HighLight_Formula_Cells()

Dim oWS As Worksheet
Dim oCell As Range

Set oWS = ActiveSheet

For Each oCell In oWS.Cells.SpecialCells(xlCellTypeFormulas)
    oCell.Interior.ColorIndex = 36
    MsgBox oCell.Formula
Next oCell


End Sub

How to Connect XLSX file (Excel Workbook) through ADO

Using Excel (Xlsx) file as a database using VBA (ActiveX Data Objects - ADO)

In the past we have already seen how to Connect to an Excel file using ADO and query its contents. That was using Microsoft Excel 2003 or earlier. With Office 2007 the file formats haver changed to XLSX, which might create the following problems

External table is not in the expected format - Excel ADO VBA

or

Could not find installable ISAM.
Excel ADO VBA

to solve that use the following Connection string:


cN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\comp\documents\visual studio 2010\Projects\ExcelWorkbookDocLevel\ExcelWorkbookDocLevel\ExcelWorkbook1.xlsx;Extended Properties=Excel 12.0;Persist Security Info=False"