CODEWITHSUNDEEP
excelvbams-wordfilenames
user4174836
user4174836

Reputation: 21

How to use Excel to generate a folders containing a wordfile of the same name?

I am a tech writer, and I am looking for a way to streamline my software-release workflow. With every release, we get an Excel file of changes that contains a request number and a description. Our department creates a word document for each change, and uses the request number and description for the title of each document. Since these files contain images and other files, I always create a folder of the same name from this excel sheet to hold all of the changes for each release. I use this bit of code to generate the files:

Sub MakeFolders()
Dim Rng As Range
Dim maxRows, maxCols, r, c As Integer
Set Rng = Selection
maxRows = Rng.Rows.Count
maxCols = Rng.Columns.Count
For c = 1 To maxCols
r = 1
Do While r <= maxRows
If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then
MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))
On Error Resume Next
End If
r = r + 1
Loop
Next c
End Sub

I am wondering: is there a way to ALSO generate a word file in each of these folders with the same name?

Upvotes: 1

Views: 68

Answers (1)

Anthony Neis
Anthony Neis

Reputation: 11

The Office default namespace should already have the ability to create Word documents, Excel Workbooks, PowerPoints, etc. The capability is a little hidden because you need to use the CreateObject function which accepts a string identifier, which ultimately is the type name of the object you want to create. In this case you want "Word.Application". Word.Application is not yet a Word document, its exactly what it sounds like (an object reference to the Microsoft Word Application itself). This should get you started.

Set wordApp = CreateObject("Word.Application")
Set wordDoc = wordApp.Documents.Add
With wordDoc
    .SaveAs ("C:/SomeDirectory/SomeDoc.docx")
    ' VERY IMPORTANT TO CLOSE THE DOCUMENT
    .Close
End With

' Close the Microsoft Word Application
wordApp.Quit 

' Always cleanup your variables when you are done
Set wordDoc = Nothing
Set wordApp = Nothing

Upvotes: 1

Related Questions