Excel to Word VBA

[samitnair]

Hello Everyone,

I needed to understand if my ask can be achieved through VBA.

So I have an excel sheet1 that has data from A1: U2000+ and we input the data under each heading.

and then I have to create an individual word document (below) for each name recorded in Sheet 1. So what I currently do is I enter the record in Sheet 1 and then again enter the same data in the required format in Sheet2 and then copy the data from Sheet2 to a word document. Doing this for 2000+ rows is a very time consuming and tedious task do we have an automated VBA option to fill the data in the desired format and save it in a word document.

If anyone can help me that would be a great relief

 

[JGordon11]

Why does it need to be moved to a Word document? What is done with the Word document once it is created?

 

[samitnair]

That's a compliance to save all respective person details in a word file. If we can get to create individual excel sheets of each row that would help me a lot

 

[JGordon11]

Are you creating one Word file or 2000+ ?

 

[samitnair]

Are you creating one Word file or 2000+ ? I

I need individual word file for each row

 

[samitnair]

I need individual word file for each row

I am currently using column A to mark if we have copied the data manually to a word document.

 

[JGordon11]

First thing to do is populate your template sheet with INDEX MATCH formulas to look up the data in the data spreadsheet and populate the cells in the template based on a unique value like name. So in the Sex cell your formula could be something like =INDEX(Data!$A$2:$U$2000,MATCH($C$2,Data!$A$1:$A$2000,0),MATCH(D2,UPPER(Data!$A$1:$U$1),0)) entered with CSE not just Enter. When you enter a valid name in C2 the Sex cell should auto populate with the correct value. You need to do this for all the data cells on the template sheet. The only data cell without a formula would be name.

Once that is accomplished it is a simple program to run through all the names, pop them one by one into C2 and print the sheet as a pdf with some naming convention.

 

[NdNoviceHlp]

Hi samitnair. There are several alternate ways to resolve this. You could create a Word template of the form and populate it with the data and/or use mail merge. You could also re-create your form "on the fly" by creating a Word doc and then adding tables and filling in the data as needed for each patient... this takes quite a bit of code which I have already roughed out. However, it seems that the output form image that you provided is on an XL sheet. Is this sheet2 where you manually copy data to from sheet1? If so, is the "form" a table... click on it and see what the name manager in the upper left of the ribbon says. You indicate that you manually save it to a Word document... how? Copy and paste? It would be a whole lot easier and less code to use VBA to fill in an existing "form" and then copy and paste it to a Word doc. A bit more information is needed for a resolution. Dave

 

[samitnair]

Hi samitnair. There are several alternate ways to resolve this. You could create a Word template of the form and populate it with the data and/or use mail merge. You could also re-create your form "on the fly" by creating a Word doc and then adding tables and filling in the data as needed for each patient... this takes quite a bit of code which I have already roughed out. However, it seems that the output form image that you provided is on an XL sheet. Is this sheet2 where you manually copy data to from sheet1? If so, is the "form" a table... click on it and see what the name manager in the upper left of the ribbon says. You indicate that you manually save it to a Word document... how? Copy and paste? It would be a whole lot easier and less code to use VBA to fill in an existing "form" and then copy and paste it to a Word doc. A bit more information is needed for a resolution. Dave

Hello Dave,

Mail merge is something I tried and failed. Yes I am learning to create a form in excel that will allow us to enter the data in realtime and add a save command button which will create a word document with the respective patient name and date. The form can be put into practice once I make a word document of all the existing data in sheet 1.

Yes the screenshot of sheet 2 is in excel and yes I manually enter each cell from sheet 1 and copy paste it to a word document.

I have converted the range into a table with name "Table2" .

So I think a VBA is needed to

1. Create a excel sheet with each patient name and details
2. Save these created individual sheets into word

 

[NdNoviceHlp]

If "Table2" is on sheet2 and laid out as per your image, this code seems to work. It will take a long time to run for +2000 patients. Save a back up wb and reduce your patient list to maybe 5 patients and give the code a trial. HTH. Dave
Module code....

Option Explicit
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long

Sub testtable2()
Dim OfsObj As Object, StrPath As String, StrTime As String
Dim Cnt As Integer, LastRow As Integer
Dim tbl As Range, ObjApp As Object, ObjDoc As Object

'create folder
Set OfsObj = CreateObject("Scripting.FilesystemObject")
If OfsObj.folderexists(ThisWorkbook.Path & "\Patients") = False Then
OfsObj.createfolder ThisWorkbook.Path & "\Patients"
End If
Set OfsObj = Nothing

'create Word app
On Error Resume Next
Set ObjApp = GetObject(, "Word.Application")
If Err Then
Set ObjApp = CreateObject("Word.Application")
End If
On Error GoTo ErFix
Application.ScreenUpdating = False

With Sheets("Sheet1")
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
End With
'loop names
For Cnt = 2 To LastRow
'update table values
Call UpdateTable2(Cnt)
Set tbl = Sheets("sheet2").ListObjects("Table2").Range
tbl.Copy
'create doc
Set ObjDoc = ObjApp.Documents.Add
ObjApp.ActiveDocument.Select
ObjApp.Selection.PasteExcelTable _
        LinkedToExcel:=False, _
        WordFormatting:=False, _
        RTF:=False
'create patient file
StrPath = ThisWorkbook.Path & "\Patients\" & Sheets("sheet1").Range("C" & Cnt)
StrTime = Format(Now, "yyyy-mm-dd")
ObjDoc.SaveAs Filename:=StrPath & " " & StrTime & ".docx"
ObjDoc.Close
OpenClipboard (0&)
EmptyClipboard
CloseClipboard
Next Cnt
'quit and tidy up
ObjApp.Quit
Set ObjDoc = Nothing
Set ObjApp = Nothing
MsgBox "Files made for all patients at " & ThisWorkbook.Path & "\Patients"
Application.ScreenUpdating = True
Exit Sub

ErFix:
On Error GoTo 0
MsgBox "error"
ObjApp.Quit
Set ObjDoc = Nothing
Set ObjApp = Nothing
Application.ScreenUpdating = True
End Sub

Function UpdateTable2(RowNum As Integer)
Sheets("Sheet2").Range("C" & 2) = Sheets("Sheet1").Range("C" & RowNum) 'name
Sheets("Sheet2").Range("E" & 2) = Sheets("Sheet1").Range("D" & RowNum) 'sex
Sheets("Sheet2").Range("C" & 3) = Sheets("Sheet1").Range("E" & RowNum) 'REQ
Sheets("Sheet2").Range("E" & 3) = Sheets("Sheet1").Range("G" & RowNum) 'Age
'etc
End Function

To operate run the testtable2 sub. Note that the UpdateTable2 function requires quite a bit more tedious typing in order to fully update the table.