Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-25-2023, 06:04 AM
BrianHoard BrianHoard is offline Creating a Word document from Excel data Windows 10 Creating a Word document from Excel data Office 2019
Advanced Beginner
Creating a Word document from Excel data
 
Join Date: Jul 2022
Location: Haymarket, VA USA
Posts: 85
BrianHoard is on a distinguished road
Default 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.
Attached Images
File Type: jpg excelWordSnap.jpg (144.4 KB, 10 views)
Attached Files
File Type: xlsx excelSample.xlsx (11.2 KB, 5 views)
File Type: docx sampleWordDoc.docx (14.5 KB, 4 views)
Reply With Quote
  #2  
Old 07-26-2023, 12:39 AM
gmayor's Avatar
gmayor gmayor is offline Creating a Word document from Excel data Windows 10 Creating a Word document from Excel data Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,106
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
  #3  
Old 07-26-2023, 06:09 AM
BrianHoard BrianHoard is offline Creating a Word document from Excel data Windows 10 Creating a Word document from Excel data Office 2019
Advanced Beginner
Creating a Word document from Excel data
 
Join Date: Jul 2022
Location: Haymarket, VA USA
Posts: 85
BrianHoard is on a distinguished road
Default

Thank you, sir! This is working perfectly. I think I'm a good 90% done already. Your expertise is much appreciated!
Reply With Quote
Reply



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
Creating a Word document from Excel data Word slow to open when creating new document from Excel Wojix Word 6 08-29-2018 03:33 PM
Creating a Word document from Excel data 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 a Word document from Excel data 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:01 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft