Attaching MsWord files to an Excel table

[DavidRains]

I have a user that uses a Excel (2010) to manage their workflow. The spreadsheet is build around a table with each active customer in a row, and columns for status and due dates, etc. The user wants to be able to manage notes for each customer that might consume about a single page of text (say, one piece of 8.5 x 11 in 12 point type). Ideally they would would like to be able to use Word or Notepad for this, but have the creation and launching of the Word file automated and the presence of notes shown in a table column.

I am looking for VBA code that would, for a the currently selected row:
1) Initiate action to create and open a new MSWord file, or open an already existing file. (The file name would be based on some unique data in the row, such as customer number)
2) Change a column in that row to show the date of the last edit of the file.
3) Print the Word file to a pdf and close the file.

Any help would be much appreciated.

 

[Macropod]

In what sense do you want "the presence of notes shown in a table column"? Do you want the actual notes to appear, or just a hyperlink to the file? Re 2, what happens if the user opens/creates the Word file, but decides not to save changes to it? As for 3, since you're editing the file, which would have to be done in Word without macro support, the user would have to take responsibility for that part, too.

 

[DavidRains]

Thanks for the clarifying questions.

I didn't want the actual notes to appear in the table, as they would be rather too long to display and too difficult to format and edit in Excel. I was imagining a column in the table named Notes, where if the user was in that row and pressed a Form Control button above the table, the Word file would be created (named using a value in the table) and saved, the current date would be added into the Note column for that row, and the file would be opened for editing. If the file had already been created using the process (which a date in the Notes column would indicate), the same button would instead open the file for editing. I'm not sure of how to go about this (and maybe I am hung up on the difference between embedded objects and hyperlinks.)

If the user opened the file but decided not to save changes it would just remain empty.

As far as printing to a pdf, I suppose it would be easy enough to have a couple of form controls in Word, one to save and close, and one to print to a pdf and I would imagine I could easily create those as macros, or find help in a Word forum. But to have each new file contain the buttons, I think the code in Excel would require copying a empty file with the controls rather than creating a new file?

 

[Macropod]

Try something based on the following:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'Note: A Reference to the Word Object Library is required, via Tools|References in the VBA Editor
Dim Rslt As Variant, wdApp As New Word.Application, wdDoc As Word.Document, StrPath As String, StrNm As String
With Sh
  If .Name <> "Sheet1" Then Exit Sub
  If Intersect(Target, ActiveSheet.Range("B:B")) Is Nothing Then Exit Sub
  Rslt = MsgBox("Add New Note?", vbYesNo, "Note Prompter")
  If Rslt = vbNo Then Exit Sub
  StrPath = ActiveWorkbook.Path & "\"
  With Target
    StrNm = .Offset(0, -1).Value
    .Value = Now()
    If .Offset(0, 1).Value = 0 Then
      .Offset(0, 1).Value = StrPath & StrNm & ".docx"
      Set wdDoc = wdApp.Documents.Add
      With wdDoc
        .Range.Text = "Notes for " & StrNm & " on " & Format(Now(), "ddd, d, MMMM YYYY @ hh:mm")
        .SaveAs2 Filename:=StrPath & StrNm & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
      End With
    Else
      Set wdDoc = wdApp.Documents.Open(Filename:=.Offset(0, 1).Value, AddToRecentFiles:=False)
      With wdDoc
        .Range.InsertAfter vbCr & "Notes for " & StrNm & " on " & Format(Now(), "ddd, d, MMMM YYYY @ hh:mm")
      End With
    End If
  End With
End With
wdApp.Visible = True: wdApp.Activate
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub

Add the code to your workbook's 'ThisWorkbook' code module. It intercepts any double-clicks in column B of Sheet1. Upon such an event, the user is prompted to say whether they want to add a new note. If yes, the cell is time-stamped and, if the document already exists (as indicated by its path & filename in Column C), that document is opened for editing, with a new 'note' entry started at the end of the document; otherwise a new document is created, named after whatever appears in Column A (no error-checking at present) and Column C is updated with the details.

As for editing the notes document, printing to pdf, closing the document etc. there seems to be little point in trying to add buttons & code for that, as doing so manually within Word is a trivial undertaking.

 

[DavidRains]

Thanks; this worked very well.