#1
|
|||
|
|||
Creating a Word document from Excel data
I'm sure this has been asked before, but I am not finding a solution searching this forum. I have been writing several Word VBA scripts in Word 2019, but this is the first time using Excel in a script. Here is my goal: I make edits to the Excel file, and run the script. Not sure if it should be done from Excel or Word? I want to run some logic on the data to determine the number of days between todays date and the start date, which will be written in the Word document as Project Age. I would like the script to loop through each row in Excel, and if the first column is "include", to include that row in the Word document, otherwise, skip that row. And format the text like the attached Word doc. I'm also attaching a dummy Excel file for reference if anyone can get me started on the right track. |
#2
|
||||
|
||||
The following should get you started. Sort the worksheet on the Categories column.
Code:
Option Explicit Private Const sWB As String = "C:\Path\excelsample.xlsx" 'Your workbook Private Const sSheet As String = "Sheet1" Private Const sTemplate As String = "C:\Path\SampleWordDoc.docx" ' your example document Sub CreateDoc() Dim oDoc As Document Dim oRng As Range Dim Arr() As Variant Dim i As Long Dim sStatus As String Dim sID As String Dim sTitle As String Dim sUrl As String Dim sOrg As String Dim sStart As String Dim sEnd As String Dim sTeam As String Dim sCategory As String Dim sNote As String Set oDoc = Documents.Add(sTemplate) Set oRng = oDoc.Range oRng.MoveStart wdParagraph oRng.Text = "" Arr = xlFillArray(sWB, sSheet) For i = 0 To UBound(Arr, 2) ' Second array dimension is columns. sStatus = Arr(0, i) sID = Arr(1, i) sTitle = Arr(2, i) sUrl = Arr(3, i) sOrg = Arr(4, i) sStart = Arr(5, i) sEnd = Arr(6, i) sTeam = Arr(7, i) sCategory = Right(Arr(8, i), 1) sNote = Arr(9, i) If Not LCase(sStatus) = "include" Then GoTo Skip oRng.Text = "Category " & sCategory & vbCr oRng.Style = "Heading 2" oRng.Collapse 0 oRng.Text = sTitle & vbCr oRng.Style = "Heading 3" oRng.Collapse 0 oRng.Text = "URL: " & sUrl & vbCr oRng.Style = "List Paragraph" oRng.Collapse 0 oRng.Text = "Organization: " & sOrg & vbCr oRng.Style = "List Paragraph" oRng.Collapse 0 oRng.Text = "Project Age: " & Date - CDate(sStart) & " days" & vbCr oRng.Style = "List Paragraph" oRng.Collapse 0 oRng.Text = "Est. Completion Date: " & sEnd & vbCr oRng.Style = "List Paragraph" oRng.Collapse 0 oRng.Text = "Team: " & sTeam & vbCr oRng.Style = "List Paragraph" oRng.Collapse 0 oRng.Text = "Notes: " & sNote & vbCr oRng.Style = "List Paragraph" oRng.Collapse 0 Skip: Next i lbl_Exit: Exit Sub End Sub Private Function xlFillArray(strWorkbook As String, _ strRange As String) As Variant 'Graham Mayor - http://www.gmayor.com - 24/09/2016 Dim RS As Object Dim CN As Object Dim iRows As Long strRange = strRange & "$]" 'Use this to work with a named worksheet 'strRange = strRange & "]" 'Use this to work with a named range Set CN = CreateObject("ADODB.Connection") 'Set HDR=NO for no header row CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""" Set RS = CreateObject("ADODB.Recordset") RS.Open "SELECT * FROM [" & strRange, CN, 2, 1 With RS .MoveLast iRows = .RecordCount .MoveFirst End With xlFillArray = RS.GetRows(iRows) If RS.State = 1 Then RS.Close Set RS = Nothing If CN.State = 1 Then CN.Close Set CN = Nothing lbl_Exit: Exit Function End Function
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Thank you, sir! This is working perfectly. I think I'm a good 90% done already. Your expertise is much appreciated!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Error 5941 Creating a new word document from Excel | Kent Burel | Word VBA | 1 | 06-14-2020 01:35 PM |
Word slow to open when creating new document from Excel | Wojix | Word | 6 | 08-29-2018 03:33 PM |
Creating a letter in Word, with text pulled from an Excel document based on selections in a userform | TMAL | Word VBA | 6 | 12-15-2017 02:16 PM |
Creating Standard Word document using excel source(s) | MagicMan | Word VBA | 1 | 12-01-2017 05:06 PM |
Creating a table in one document of WORD from EXCEL with Mail Merge | Joseph.Comerford@bentley. | Mail Merge | 1 | 04-18-2015 01:19 AM |