Wednesday, August 25, 2010

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.Close False



MsgBox "Linked file not found"

End If

End If

End If

Next oIShape



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


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


GoTo Finally

End Sub

Saturday, August 21, 2010

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

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

You can do the same through Excel VBA

ActiveWindow.DisplayWorkbookTabs = False

Friday, August 06, 2010

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.

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
        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:

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

Wednesday, August 04, 2010

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

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"
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.