As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active Worksheet in a particular Cell.
Opening an Word Document is more like Excel Workbook only.
To work the below code you need to Add the reference of Microsoft Word in your Excel Workbook.
Sub Copy_From_Word()
Application.DisplayAlerts = False 'Disable all the Alerts from excel
Application.ScreenUpdating = False 'After opening Word Doc, Document will not be visible
'Create a New Object for Microsoft Word Application
Dim objWord As New Word.Application
'Create a New Word Document Object
Dim objDoc As New Word.Document
'Open a Word Document and Set it to the newly created object above
Set objDoc = objWord.documents.Open("C:\Users\Vish\Desktop\New Microsoft Office Word Document.docx")
'To Store all the content of that word Document in a variable
strTemp = objDoc.Range(0, objDoc.Range.End)
'Now store that variable value in to a cell range
Range("A1").Value = strTemp
str1 = objDoc.Range(0, 1)
Range("A2").Value = str1
Range("B2").Value = strTemp
objDoc.Close SaveChanges:=wdDoNotSaveChanges
objWord.Quit
End Sub
Hi Viswa,
Good day!!
I have created one macro to send a automatic mails to the employees for this medical reminders for this I have fixed all the issue except the body of the mail. for this I have a word document, which is stored in excel cell as a hyperlink. I want to open the word document from excel hyperlink then copy the whole document and need to paste the same in body of the mail.
if you wan met to paste the code here I will paste it.. pls help anyone to solve this issue
——————————
Sub Send_Files()
‘Make a list in Sheets(“Sheet1”) with :
‘In column A : Names of the people
‘In column B : E-mail addresses
‘In column C:Z : Filenames like this C:DataBook2.xls (don’t have to be Excel files)
‘The Macro will loop through each row in “Sheet1” and if there is a E-mail address in column B
‘and file name(s) in column C:Z it will create a mail with this information and send it.
‘Working in Excel 2000-2016
‘For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range
Dim FileCell As Range
Dim rng As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set sh = Sheets(“Sheet1”)
Set OutApp = CreateObject(“Outlook.Application”)
For Each cell In sh.Columns(“B”).Cells.SpecialCells(xlCellTypeConstants)
‘Enter the path/file names in the C:Z column in each row
Set rng = sh.Cells(cell.Row, 1).Range(“F1:Z1”)
If cell.Value Like “?*@?*.?*” And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = cell.Value
.cc = cell.Offset(1, 0).Value
.Subject = cell.Offset(2, 0)
.Body = “Hi” & cell.Offset(0, -1).Value
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) “” Then
If Dir(FileCell.Value) “” Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell
.Send ‘Or use .Display
End With
Set OutMail = Nothing
End If
Next cell
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
——————————————————
I want to import word documents in various columns(named) instead of everything in one column, How can I do that ?
Hi,
That is possible to do, but for you need to know how your data is arranged in word document so that you can read them and put it in separate rows/column.
can u tel me how please
As mentioned above, you need to know how your data is arranged in word document so that you can read them and put it in separate rows/column.
I would like to have a word document for calculations (text and pictures) and then make a macro (already done) that open an excel file.
Question:
1. I have information in word, project ID and Projectname, that should be transfered to Excel – this information is given in a userform.
the information should save the excel in a certain place and with a certain filename.
fx: if the project number is 12345 then excel should save the file in a folder called 12345 and with the name E12345.xlsm