Interacting with Other Applications using VBA

Home/Excel VBA/Interacting with Other Applications using VBA

We can Interacting with Other Applications using VBA- with the MS Office Applications like Word, PowerPoint,Outlook,etc… and other applications like Internet Explorer, SAS,etc.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

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


In this Section:

Interacting with Other Applications using VBA – An Introduction:

Yes, we can interact with the other Applications using VBA,i.e; with the MS Office Applications like Word, PowerPoint,Outlook,etc… and other applications like Internet Explorer, SAS,etc…
to do this first we need to establish a connection with those applications, then we can able to access objects of the other applications from VBA.

Interacting with Other Applications using VBA

There are two ways to establish the Connection:Late binding and Early binding. In late binding, we will create an Object in run time it will assign the necessary object. In early binding, we have to refer the necessary object before we use it.

If you want to automate Other Microsoft Applications: You would declare the following variables at the top of your procedure, you need to declare some object variables specific to the application being automated.

(In early binding, first we need to set a reference to the Other application’s object library. In the Visual Basic Editor (VBE) => Select References… from the Tools menu => Select Other application’s object library)

Late Binding

Dim otherApp As Object
Dim otherDoc As Object
Dim otherSpecificObjects As Object

'To open a new instance of Other:
Set otherApp = CreateObject("Other.Application")

'Or to use an existing instance of Other:
Set otherApp = GetObject(, "Other.Application")

Early binding – wee need to add the reference for required object.

Dim otherApp As Other.Application
Dim otherDoc As Other.DocType
Dim otherSpecificObjects As Other.SpecificObjects

'To open a new instance of Other:
Set otherApp = CreateObject("Other.Application")

'Or to use an existing instance of Other:
Set otherApp = GetObject(, "Other.Application")

For example if you want to interact with MS Word Application, you have to write the code as follows:

Dim wordApp As Word.Application
Dim wordDoc As Word.Document
' Reference existing instance of Word
Set wordApp = GetObject(, "Word.Application")
' Reference active document
Set wordDoc = wordApp .ActiveDocument 

How to interact with MS Word? (Early Binding)

The following example will show you how to interact with MS word Application from Excel VBA, it will create a new word document and add some text to the newly created document.

*Create a new module and Add the reference to Microsoft Word Object Library and then Paste the following code into the module and run (Press F5) it to test it.

Sub sbWord_CreatingAndFormatingWordDoc()
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: mongopono.ru"
para.Format.Alignment = wdAlignParagraphCenter
para.Range.Font.Size = 18
para.Range.Font.Name = "Cambria"
'
For i = 0 To 2
Set para = oWDoc.Paragraphs.Add
para.Space2
Next
'
Set para = oWDoc.Paragraphs.Add
With para
.Range.Text = "Paragraph 2 - Example Paragraph, you can format it as per yor requirement"
.Alignment = wdAlignParagraphLeft
.Format.Space15
.Range.Font.Size = 14
.Range.Font.Bold = True
End With
'
oWDoc.Paragraphs.Add
'
Set para = oWDoc.Paragraphs.Add
With para
.Range.Text = "Paragraph 3 - Another Paragraph, you can create number of paragraphs like this and format it"
.Alignment = wdAlignParagraphLeft
.Format.Space15
.Range.Font.Size = 12
.Range.Font.Bold = False
End With
'
oWApp.Visible = True
End Sub

How to interact with MS Word? (Late Binding)

The following example will show you how to interact with MS word Application from Excel VBA, it will create a new word document and add some text to the newly created document.

Sub sbWord_CreatingAndFormatingWordDocLateBinding()
Dim oWApp As Object
Dim oWDoc As Object
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: mongopono.ru"
para.Format.Alignment = wdAlignParagraphCenter
para.Range.Font.Size = 18
para.Range.Font.Name = "Cambria"
'
For i = 0 To 2
Set para = oWDoc.Paragraphs.Add
para.Space2
Next
'
Set para = oWDoc.Paragraphs.Add
With para
.Range.Text = "Paragraph 2 - Example Paragraph, you can format it as per yor requirement"
.Alignment = wdAlignParagraphLeft
.Format.Space15
.Range.Font.Size = 14
.Range.Font.Bold = True
End With
'
oWDoc.Paragraphs.Add
'
Set para = oWDoc.Paragraphs.Add
With para
.Range.Text = "Paragraph 3 - Another Paragraph, you can create number of paragraphs like this and format it"
.Alignment = wdAlignParagraphLeft
.Format.Space15
.Range.Font.Size = 12
.Range.Font.Bold = False
End With
'
oWApp.Visible = True
End Sub

How to interact with MS PowerPoint?

'Add Microsoft PowerPoint Object Library
Sub sbPowePoint_SendDataFromExcelToPPT()
'Declarations
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)
oPSlide.Select
'Copy a range as a picture and align it
ActiveSheet.Range("A3:E10").CopyPicture Appearance:=xlScreen, Format:=xlPicture
oPSlide.Shapes.Paste
oPPT.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
oPPT.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
'Add the title text
sText = "My Header - mongopono.ru Example"
oPSlide.Shapes.Title.TextFrame.TextRange.Text = sText
oPPT.Activate
'Release Objects
Set oPSlide = Nothing
Set oPPres = Nothing
Set oPPT = Nothing
'
End Sub

How to interact with MS Outlook?

'Late Binding
Sub sbOutlook_SendAMail()
'Declaration
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 - Example mail - mongopono.ru"
.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

Example File

You can download the example file here and explore it.

mongopono.ru – Interacting With Other Applications

LIMITED TIME OFFER

By |June 3rd, 2013|Excel VBA|4 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

4 Comments

  1. Jasham July 10, 2015 at 3:52 AM - Reply

    Is there is a way to automate some other applications through Excel VBA like “Remote Desktop Connection” where we can automate the application which is present in some other system.

    For Example:-

    Machine X is my system & I want to open Google from the Y Machine.

    So is there is a way to automate the process.
    (Opening the Remote Desktop Connection application-> Then open Google from Y Machine.)

    • Yaman January 23, 2017 at 7:42 PM - Reply

      Easier method :
      Use Selenium. Set port (ip if not on same LAN). Use the port in the remote PC with (–remote debugging) to open Chrome.

      Now your VBA will run on PC X and Chrome will run on PC Y.

      This assumes you can Open chrome on remote PC. If not simply use a runas command to get that done.

      Google SeleniumVBA for details.

  2. Rondiman February 21, 2017 at 11:21 PM - Reply

    Excelent!

    Could make a post about interaction with .pdf? that would be outstanding!

  3. Pandiarajan g April 6, 2017 at 12:50 PM - Reply

    I tried to execute the above query and i get this message “User-defined type not defined”…. Please guide me….

Leave A Comment


Related pages


delete empty cells in excelmerging excel sheets into onevba open a text filewriting excel macros with vbavba is nullexplain macros in excelexcel greater than formulainsert comment excel shortcutvba excel count rowsexcel remove duplicates formulaprograming in excellearn vba from scratchtechnical aptitude questions with answers pdfaccess vba function return valuevba sub procedureexcel vba max valuedrop down macro exceldeveloper tab outlook 2010vba programming coursecreating a userform in excelexcel goto vbaunlock an excel spreadsheetexcel 2007 sumifhow to use sumifs function in excelfile dialog vbaunhide spreadsheetcolor code vbareference excel cell in vbaconstruction schedule excel templatetrim function in vbavba object propertiesexcel sheet vbaexcel vba chr functionvba examples for excelexcel 2007 hide columnsaccess vba create tabledatediff in vbasas nested do loopsaccess vba color codesexample dashboards in excelhow to create a workbook in excellooking for duplicates in excelexcel syntaxuserforms in excelif statement in ms excelexcel vba userform tutorialpassword protect a spreadsheetautosave in excel 2007cut paste worksheetshow to use the sumif function in excelinterview questions on sql queries with answers pdfhow to unhide an excel columnexcel macro to unlock password protected sheetexcel interactive dashboard templateapplication screenupdating falseexcel formulas pdf in hindicopy excel worksheet to another workbookexcel color duplicatesvlookup with and functionconcatenate formula in excelworking with arrays in excel vbaexcel dynamic advanced filterexcel insert a columnexcel macro transposepowerpoint dashboard examplestypes of excel graphshow to identify duplicates in exceldim string vbaexcel clear blank cellsrca templatecheck marks in excelengineering dashboard examplesuses of pivot table in excel 2007excel vba write to access databaseadvanced oops interview questionsvariable in vbadelete blank rows exceldml and ddl commands