Save a Word file using Cell Value as Filename-CodePudding

I have a word document that allows us to merge info from an online database. I am trying to create a macro that will take the value in a specific table cell and use that to save the file in a specific directory.

I am not proficient with VBA but try to muck about with what others have done by copying code from other resources such as here.

I have found code that allows me to select the correct cell and I was able to make it work.

I found other code to save the file and I have tried to modify it to include the cell value as the file name.

Sub SaveAsCellContent()

    Dim Invoice As String
    
    Dim directory As String
    
    Invoice = ActiveDocument.Tables(1).Cell(2, 5)
        
    directory = "D:\Dropbox (DRYBSMT)\~ DB Forms\Word Saves\"
    
    wordApp.DisplayAlerts = False
    
    WordDoc.SaveAs FileName = DIR & INV & ".docx", FileFormat:=wdFormatDocumentDefault
    WordDoc.Close
    wordApp.Quit
    Set WordDoc = Nothing
    Set wordApp = Nothing
    
End Sub

As written I get an error "Compile Error: Type Mismatch".

I have spent hours and hours trying to figure this out and I think it is time to solicit expert help.

What I have now, and it is working:

Sub SaveAsCellContent()

    Dim Invoice As String
    
    Dim Directory As String
    
    Invoice = ActiveDocument.Tables(1).Cell(2, 5).Range.Text
    
    Invoice = Left(Invoice, Len(Invoice) - 2)
        
    Directory = "D:\Dropbox (DRYBSMT)\~ DB Forms\Word Saves\"
  
    ActiveDocument.SaveAs Filename:=Directory & Invoice & "i.docx", fileformat:=wdFormatXMLDocument
   
End Sub

But I am told I need to add Debug.Print. Which is where I am at now.

CodePudding user response：

I've included all above comments in below working code example including adding an example error handler and a debug.print statement that prints the build directory file name to the Immediate (debug) window.

Sub SaveAsCellContent()

Dim Invoice As String
Dim directory As String

' Get the filename that is in the 1st table in the document
Invoice = ActiveDocument.Tables(1).Cell(1, 1).Range.Text

' so the Invoice string is always at least 2 characters long
' because a cell's value contains the cell boundary characters
' remove the cell boundary characters as they cannot be used for the file name
Invoice = Left(Invoice, Len(Invoice) - 2)

' no file name specified
If Len(Invoice) = 0 Then
    Invoice = "default filename"
End If

' build the directory   filename to use in the saveas
directory = "D:\Dropbox (DRYBSMT)\~ DB Forms\Word Saves\"
' adding the file extension .docx is not needed
' is determined by the FileFormat parametes
Dim fileNm As Variant
fileNm = directory & Invoice

' print to the Immediate window for testing purposes
Debug.Print ("File name: " & fileNm)
   
On Error GoTo saveError
ActiveDocument.SaveAs FileName:=fileNm, 
FileFormat:=WdSaveFormat.wdFormatDocumentDefault

saveError:
    If Err.Number > 0 Then
        MsgBox ("Some error occurred:" & vbCrLf & Err.Number)
    End If
End Sub