![]() |
|
|
|
#1
|
|||
|
|||
|
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 |