1

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?

YowE3K
  • 23,852
  • 7
  • 26
  • 40
user4174836
  • 21
  • 1
  • note that `Dim maxRows, maxCols, r, c As Integer` doesn't define all these variables as integer. Excel only looks to the next comma, so the first 3 are `Variant` and only the last is integer. Once you have a folder, could you copy a blank (prepared) document to that location? – SeanC Jan 04 '18 at 20:36
  • Yes; I thought of doing that too. It would take about 30 seconds to move the files over. However, I can't find anything that does for Word documents what the script above for folders; i.e., create a bunch of word documents based on cells in excel. – user4174836 Jan 04 '18 at 20:48

1 Answers1

1

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
Anthony Neis
  • 11
  • 2
  • Thanks! That is a lot of help. Is there a way to make the file name above ("SomeDoc.docx") come from a range of cells or even a single highlighted cell? I created directories from a column in excel, and I am hoping to recreate this with word documents. – user4174836 Jan 04 '18 at 21:44
  • There is a `Text` property on Ranges. Single cells are still considered ranges they just have a single cell as the start and end of its range definition (technically). Here's the documentation on the `Text` property https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-text-property-excel Its not very helpful documentation, but at least its something. Ultimately, you would do something like: `Set fileName = myrange.Text ... worDoc.SaveAs(fileName)` – Anthony Neis Jan 04 '18 at 22:06