Control Word from Excel using VBA in Microsoft Excel





Control Word from Excel using VBA in Microsoft Excel

You can run the macros either from the Visual Basic Editor by placing your cursor in the macro and pressing the F5 key, or from Excel by opening the Macros dialog box (ALT+F8) choosing the macro to run and clicking Run. It is best to run these macros from Visual Basic Editor by using Debug > Step Into (by pressing F8) so you can watch them as they work. Instruction If Developer Tab is not in the Ribbon..

  • Open Excel.
  • Go to VBA Editor (press Alt + F11)
  • Go to Immediate Window. ( Ctrl + G)
  • Write below Code.
    • Application.ShowDevTools = True

How to Insert VBA code in Excel

  • Go to Developer Tab > Code Group > Visual Basic
  • Click Insert > Module.
  • Will open a Blank Module for you.
  • Write / Paste provided code in that Module

Untitled-1 How to Run VBA code in Excel

  • Select anywhere in between the Code, Sub… End Sub
  • Click Run & Run Sub or F5Untitled-1

 

Write Data in a Word Document

Code

Sub CreateNewWordDoc()
' to test this code, paste it into an Excel module
' add a reference to the Word-library
' create a new folder named C:\Foldername or edit the filnames in the code
Dim i As Integer
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Add ' create a new document
' or
'Set wrdDoc = wrdApp.Documents.Open("C:\Foldername\Filename.doc")
' open an existing document
' example word operations
With wrdDoc
For i = 1 To 100
.Content.InsertAfter "Here is a example test line #" & i
.Content.InsertParagraphAfter
Next i
If Dir("C:\Foldername\MyNewWordDoc.doc") <> "" Then
Kill "C:\Foldername\MyNewWordDoc.doc"
End If
.SaveAs ("C:\Foldername\MyNewWordDoc.doc")
.Close ' close the document
End With
wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub

 

Code

 

Set wrdApp = CreateObject(“Word.Application”)

Above command is used to creates an Automation object of the specified class. If the application is already running, CreateObject will create a new instance, In above case application is WORD, which is already installed in your PC. Now wrdApp will always work as a Word Application, in this Procedure

wrdApp.Visible = True

Although you have created the object Word, it will not display in your screen. We need to set Visible status to True, to make its newly created instance visible.

 

Set wrdDoc = wrdApp.Documents.Add

We have created only the Word Application. But, we have not added any Blank Document in the Application. Something like, we have open a Word Program, but “NewDocument” was not clicked. Above command will create a New Document in the Word Applciation. . Now wrdDoc will always work as a Word Document, in this Procedure

 

‘Set wrdDoc = wrdApp.Documents.Open(“C:\Foldername\Filename.doc”)

Instead creating a New Word Doc, you can also used above command to open an existing File, to perform.

With wrdDoc…    End With

object in wrdDoc class, we don’t need to write everytime wrdDoc, with a Dot (.) it will fetch the objects in wrdDoc class. For example, instead of wrddoc.Content.InsertParagraphAfter we can simple write .Content.InsertParagraphAfter

and this trick / process will work until End With found. After and with, (.) will not recognize, the object mentioned in the With Function.

.Content.InsertAfter “Here is a example test line #” & i

.Content.InsertParagraphAfter

Above both line will work to Insert a Line, and a Enter / paragraph change. As we are using a For Loop, so it will repeat the same task 100 times. (For i = 1 To 100). So finally we are writing 100 line, like, below.

 

Image

.SaveAs (“C:\Foldername\MyNewWordDoc.doc”)

Now time to save the word file. By above line, we are saving the Word File in folder (C:\Foldername) with file name as MyNewWordDoc.doc. But, what if, in the same folder, with same name, a file is already exits. We need to delete the same file, before saving it.  With a If check, we can do the same.

If Dir(“C:\Foldername\MyNewWordDoc.doc”) <> “” Then

Kill “C:\Foldername\MyNewWordDoc.doc”

End If

If we found a file in the above mentioned folder, then, KILL command will delete the file form that location. And then we can simple save the file. Above code will only work, if in the mentioned folder, mentioned file found, otherwise, it will not execute.

 

.Close ‘ close the document

After save, we need to close the saved file, by above line.

 

wrdApp.Quit

Where .Close will exit the Word Document,  Quit Function will exit the Word Application.

    Set wrdDoc = Nothing

    Set wrdApp = Nothing

Above both line, will free the memory allocated by Object wrdDoc / wrdApp.

 

 

Read Data from a Word Document

 

Code

Sub OpenAndReadWordDoc()
' assumes that the previous procedure has been executed
Dim tString As String
Dim p As Long, r As Long
Workbooks.Add ' create a new workbook
With Range("A1")
.Value= "Word Document Contents:"
.Font.Bold = True
.Font.Size = 14
.Offset(1, 0).Select
End With
r = 3 ' startrow for the copied text from the Word document
Set wrdApp = CreateObject("Word.Application")
'wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open("C:\Foldername\MyNewWordDoc.doc")
' example word operations
With wrdDoc
For p = 1 To .Paragraphs.Count
Set tRange = .Range(Start:=.Paragraphs(p).Range.Start, _
End:=.Paragraphs(p).Range.End)
tString = tRange.Text
tString = Left(tString, Len(tString) - 1)
' exclude the paragraph-mark
' check if the text has the content you want
If InStr(1, tString, "1") > 0 Then
' fill into active worksheet
ActiveSheet.Range("A" & r).Value = tString
r = r + 1
End If
Next p
.Close ' close the document
End With
wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
ActiveWorkbook.Saved = True
End Sub

Decode

 

Workbooks.Add ‘ create a new workbook

 

Will create a New Workbook, so that, it will not destroy or overlap, youe exiting excel file.

 

With Range(“A1″)

  .Value= “Word Document Contents:”

  .Font.Bold = True

  .Font.Size = 14

  .Offset(1, 0).Select

End With

In new Workbook, at A1, will write a text and will Format it Bold, Font Size 14. Then it will come to the next row, by using Offset function.

 

Set wrdApp = CreateObject(“Word.Application”)

Set wrdDoc = wrdApp.Documents.Open(“C:\Foldername\MyNewWordDoc.doc”)

 

wordApp or wrdDoc will work mentioned Application or Document.

 

For p = 1 To .Paragraphs.Count

We are repeating a task, and repetition will end end when P counter will turn to Number of Paragraph in the Word Document. If word has total 10 paragraph, for loop will end after 10 time repetition.

 

Set tRange = .Range(Start:=.Paragraphs(p).Range.Start, _

    End:=.Paragraphs(p).Range.End)

In each repetition, according to p’s value, it will read that paragraph / line, and store the same in a variable called tRange. In simple, it will read each line.

 

tString = tRange.Text

tString = Left(tString, Len(tString) – 1)

In each line of WordDoc, at the end of line, word add a Paragraph mark. (¶), so we need to exclude that character from each line.  So updated tString will change to old tString -  1 character.

 

ActiveSheet.Range(“A” & r).Value = tString

In excel file, in each line, it will write that text,m with repetation, like below.

Image

However if you need to write only few line, or only line that matched a criteria, you can do the same, using a If function, like  If InStr(1, tString, “1″) > 0 Then with this line, we are checking, if in any line a character (1) found , then only write that line, you can exclude that criteria if required. And using r = r+1 we are increasing r’s value, to write each line in excel.



Leave a Reply

Your email address will not be published. Required fields are marked *


× five = 25

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>