Activate IE Browser?

Sub Activate_IE()

Dim oIE As Object

Set oIE = CreateObject("InternetExplorer.Application")

With oIE

.Visible = True

.Navigate "http://www.xldennis.com"

End With

Set oIE = Nothing

End Sub

Open_Access

Sub Open_Access()

Dim oAccess As Object

Set oAccess = CreateObject("Access.Application")

With oAccess

.Visible = True

.UserControl = True

End With

End Sub

MS Outlook

Sub Open_OutLook()

Dim oOutlook As Object

Dim oNameSpace As Object

Dim oInbox As Object



Set oOutlook = CreateObject("Outlook.Application")

Set oNameSpace = oOutlook.GetNamespace("MAPI")

Set oInbox = oNameSpace.Folders(1)

Set oInbox = oInbox.Folders("Inbox")

oInbox.Display

End Sub

How to find the last row or column or cell on a worksheet?

How to find the last row with data on a worksheet:

On Error Resume NextMsgBox ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row
How to find the last column with data on a worksheet:

On Error Resume NextMsgBox ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column
How to find the last cell with data on a worksheet:

On Error Resume NextdxLastCol= ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column
dxLastRow= ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row
dxLastCell=cells(ldxLastRow,dxLastCol).address
', '

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

How to Extract All Formula's in Excel Sheet using 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

Excel VBA Autofilter - Specify Multiple Criteria using Array:-

After long time let us revisit our good old Autofilter Fruits example.
If you need to filter say Oranges and Apples alone, you can either pass both criteria (Yes! I have avoided using - Mutliple criteria) or can try using an Array where you can pass multiple values



Sub AutoFilter_Using_Arrays()

Dim oWS As Worksheet

On Error GoTo Err_Filter

Dim arCriteria(0 To 1) As String

Set oWS = ActiveSheet

arCriteria(0) = "Apple"
arCriteria(1) = "Orange"

oWS.UsedRange.AutoFilter Field:=2, Criteria1:=arCriteria, Operator:=xlFilterValues

Finally:

If Not oWS Is Nothing Then Set oWS = Nothing

Err_Filter:
If Err <> 0 Then
MsgBox Err.Description
Err.Clear
GoTo Finally
End If
End Sub


If you leave out the Operator in Excel VBA Autofilter- Only Last Value of the Array Will be displayed.