Excel VBA to Interact with Other Applications

Home/Excel VBA/Excel VBA to Interact with Other Applications

Another powerful feature of Excel VBA is to interact with Other Applications. We can deal with Other MS Office Applications (Word, PowerPoint,Access). We can also deal with Other Applications like Internet Explorer, SAS, VBScript from Excel. I will show some examples on interacting with different Application from Excel.



50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates

Interact with PowerPoint from Excel VBA

The following code will show you how to deal and interact with PowerPoint. We can create PowerPoint presentation from Excel or modify the existing presentation using Excel VBA.

Add Reference:Microsoft PowerPoint Object Library

Sub sbPowePoint_SendDataFromExcelToPPT()
Dim oPPT As PowerPoint.Application
Dim oPPres As PowerPoint.Presentation
Dim oPSlide As PowerPoint.Slide
Dim sText As String
'Open PowerPoint
Set oPPT = New PowerPoint.Application
Set oPPres = oPPT.Presentations.Add
oPPT.Visible = True
'Add a Slide
Set oPSlide = oPPres.Slides.Add(1, ppLayoutTitleOnly)
'Copy a range as a picture and align it
ActiveSheet.Range("A1:B10").CopyPicture Appearance:=xlScreen, Format:=xlPicture
oPPT.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
oPPT.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
'Add the title text
sText = "My Header"
oPSlide.Shapes.Title.TextFrame.TextRange.Text = sText
'Release Objects
Set oPSlide = Nothing
Set oPPres = Nothing
Set oPPT = Nothing
End Sub

Dealing with MS Word From Excel VBA

The following code will show you how to deal and interact with Word. We can create Word document from scratch or modify the existing document using Excel VBA.

Sub sbWord_FormatingWordDoc()
Dim oWApp As Word.Application
Dim oWDoc As Word.Document
Dim sText As String
Dim iCntr As Long
Set oWApp = New Word.Application
Set oWDoc = oWApp.Documents.Add() '("C:\Documents\Doc1.dot") 'You can specify your template here
'Adding new Paragraph
Dim para As Paragraph
Set para = oWDoc.Paragraphs.Add
para.Range.Text = "Paragraph 1 - My Heading"
para.Format.Alignment = wdAlignParagraphCenter
para.Range.Font.Size = 18
para.Range.Font.Name = "Cambria"
For i = 0 To 2
Set para = oWDoc.Paragraphs.Add
Set para = oWDoc.Paragraphs.Add
With para
.Range.Text = "Paragraph 2 - Some Text for the next Paragraph"
.Alignment = wdAlignParagraphLeft
.Range.Font.Size = 14
.Range.Font.Bold = True
End With
Set para = oWDoc.Paragraphs.Add
With para
.Range.Text = "Paragraph 3 - This is another Paragraph, you can create number of paragraphs like this and format it"
.Alignment = wdAlignParagraphLeft
.Range.Font.Size = 12
.Range.Font.Bold = False
End With
oWApp.Visible = True
End Sub

Interact with MS Access from Excel VBA

The following code will show you how to deal and interact with Access.

Add Reference: Microsoft Access Object Library

Sub sbAccess_OpenAForm()
'Declaring Access Application
Dim oAApp As Access.Application
'Connecting Access Data base
Set oAApp = New Access.Application
oAApp.OpenCurrentDatabase ("C:\ExampleDatabase.accdb")
'Opening a From
With oAApp
.DoCmd.OpenForm "MyForm", acNormal
.Visible = True
End With
End Sub

Interact with Outlook from Excel VBA

Sub sbOutlook_SendAMail()
Dim oOApp As Object
Dim oMail As Object
Set oOApp = CreateObject("Outlook.Application")
Set oMail = oOApp.CreateItem(0)
On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With oMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Write Your Subject Here"
.Body = "Hi, This is example Body Text."
'.Attachments.Add ("C:\Temp\ExampleFile.xls") '=> To add any Attcahment
.Display '=> It will display the message
'.Send '=> It will send the mail
End With
On Error GoTo 0
Set oMail = Nothing
Set oOApp = Nothing
End Sub

Interact with MS Word from Excel VBA -Another Example

Add Reference: Microsoft Word Object Library

Sub sbWord_ExcelToWord()
Dim oWApp As Word.Application
Dim oWDoc As Word.Document
Dim sText As String
Dim iCntr As Long
set oWApp = New Word.Application
Set oWDoc = oWApp.Documents.Add() '("C:\Documents\Doc1.dot") 'You can specify your template here
For iCntr = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
sText = Cells(iCntr, 1)
sText = sText & " " & Cells(iCntr, 2)
sText = sText & " " & Cells(iCntr, 3)
sText = sText & " " & Cells(iCntr, 4)
oWDoc.Content.InsertAfter (sText)
Next iCntr
oWApp.Visible = True
' Releasing objects
Set oWDoc = Nothing
Set oWApp = Nothing
End Sub

Dealing with Internet Explorer

The following code will show you how to deal and interact with Internet Explorer.

Sub sbIE_OpenASite()
Dim IE As Object
' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")
' Send the form data To URL As POST binary request
IE.Navigate "/' Wait while loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
IE.Visible = True
Set IE = Nothing
End Sub

Dealing with Other Applications from Excel VBA – Calculator

The following code will show you how to deal and interact with Calculator.

Sub sbAnyApplication_OpenCalculator()
Dim sProg As String
Dim tID As Double
On Error Resume Next
sProg = "Calc.exe"
tID = Shell(sProg)
If Err <> 0 Then
MsgBox "Can't Start Calculator"
End If
End Sub

Run VBScript from Excel VBA

Sub sbVBScript_RunVBS()
Dim SFilename As String
SFilename = "C:\Temp\Test.vbs" 'Change the file path
' Run VBScript file
Set wshShell = CreateObject("Wscript.Shell")
wshShell.Run """" & SFilename & """"
End Sub

VBA to Attach Send An Excel Chart to Outlook Email

Sub emailingProgram()
Dim olapp As Outlook.Application
Dim objmail As Outlook.mailitem
Dim pos As Integer
Set olapp = Outlook.Application
For Each xcell In Sheets("Sheet1").Range(Range("RangetoCopy"), _
msgText = Range("Msg")
ActiveCell.Offset(0, 1).Select
'If you think that the email ID is in the pattern use this if block
'The code will go into the else statement if the First Name is not mentioned
If Selection.Value = "" Then
pos = InStr(1, xcell.Value, ".")
Fname = Mid$(xcell.Value, 1, InStr(1, xcell.Value, ".") - 1)
'If you have mentioned the first names in the First Name column this part will read it directly
Fname = Selection.Value
End If
'For each of the cells present in the To List we create a MailItem and send it
Set objmail = olapp.CreateItem(olMailItem)
objmail.BodyFormat = olFormatRichText
'Setting the subject
objmail.Subject = "Example Subject"
'Uncomment the following line of code in case you want to send a plain message
'objmail.Body = "Hi " + UCase(Mid$(Fname, 1, 1)) + Mid$(Fname, 2) + "," + Chr(13) + Chr(10) + msgText
'For using an image in your mail or an HTML body for styling
objmail.HTMLBody = "<p><font size='6' face='arial' color='red'><i>Dear " & UCase(Mid$(Fname, 1, 1)) + Mid$(Fname, 2) & "<br></font></p><br><p align='CENTER'><font size='5' face='COMIC SANS' color='RED'>Wishing you a Wonderful Birthday</p><br><br></font><p align='CENTER'><a href='/'><img src='/' width=450 height=412 border=0></a></a><br><br><br><p align='left'>Thanks & Regards <br><br/> _<p><p align='left'><br>Anshuman Pandey<br>/ = 
Set objmail = Nothing
Next xcell
End Sub
By |April 15th, 2013|Excel VBA|0 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

Leave A Comment

Related pages

password to unprotect excel sheetvlookup excel 2007 tutorialvb script interview questions and answersword vba referencehighlight a column in excelaccess vba query parametersvba rows autofitvba globalexcel vba select cellmsgbox macrosql pivot explainedexcel macro database queryvba coding in exceltcl in sql serverworkbooks.worksheetsexcel vba application getsaveasfilenamehow to run macro in excelwhat is sas sqlexcel vba active worksheetadd developer tab to excel 2007vba application displayalertsvba dialog boxinsert text box excelvb6 write to text filehow to unlock excelif statement excel examplesexcel vba intunprotecting excelhow to hide lines in excelcount rows in a range vbatrim function in accessfso file system objectunlocking excel sheetadodb.connection connection stringcompile data from multiple excel worksheetsmacros in ms excelvba excel dim arrayvba userform closehow to adjust columns in excelinsert column excelfor vba loopdelete a row in excel vbaexport access query to excel vbaprogramming logic questions and answersworkbook passwordbasic macro exceldemographic pie chartvba selection.findexcel spreadsheet test for interviewvba select a worksheethow to unhide rows and columns in excelmacro basics excel 2007excel with vba programmingunprotect an excel workbookcall a function in vbainteractive excel chartsexcel transpose macromeaning of hlookup in excelpivot table formulas excel 2007update sql vbaboard meeting minutes template microsoft wordhow to learn vba in excelvba excel filtercheckboxes in excelhow to do pivot table in excelhlookup in excel with examplehide sheets in excel vbatrend analysis spreadsheetvba msgbox new linevba string variableenable macro vbaawesome excel spreadsheetsarray function in vbauser defined functions in vbavba examples for excelvba xlrightexcel vba sheets.addexcel vba with functionduplicate in excel 2007