FileSave() Word Macro

Question

I have written a macro which when a file is created and the little save button is clicked the macro is triggered(as it overrides the default FileSave function). The macro extracts information from a table in my document, converts it to a string then cleanses that string of any carriage returns then uses that as the file name. An if statement then runs checking whether a hidden row in the table has a value of 1 and if not then it will set the value to 1 and save the document at the location specified with the new filename.

All of this works great except when I re-open the file to edit it, as my users will do, and click save again it tries to run everything again, completely ignoring my If statements first statement and will add a carriage return to the start of the filename effectively breaking the save function to SharePoint as it has an invalid character in it. If you then click save again it will seemingly run the macro as normal and save it by actually reading the if statement correctly. Am I doing something wrong here?

Here is the code:

Sub FileSave()
    Dim strText As String
    Dim strClean As String
    Dim strFileName As String
    Dim strLocation As String
    Dim strSavedName As String
    Dim strCleanSave As String

    strText = ActiveDocument.Tables(1).Rows(1).Cells(2).Range.Text

    strClean = Application.CleanString(strText)

    strFileName = strClean + "_" + Format(Date, "yyyy-mm-dd")

    strLocation = "[My SharePoint Site]"

    If ActiveDocument.Tables(1).Rows(1).Cells(3).Range.Text = "1" Then
        strSavedName = ActiveDocument.Name
        strCleanSave = Application.CleanString(strSavedName)
        ActiveDocument.SaveAs FileName:=strSavedName
        Exit Sub
    Else
        ActiveDocument.Tables(1).Rows(1).Cells(3).Range.Text = "1"
        ActiveDocument.SaveAs FileName:=strLocation & strFileName & ".docx"
    End If
End Sub

Are you using "Option Explicit"? If not, add that at the very top of you form or module where your code is. It may help uncover any issues that are causing the problems you are seeing. — MatthewD
I am not using Option Explicit. This is for a word macro. Saving the first time is flawless. Open it again to edit, hit save after editing it will throw and upload error because rather than the filename being: "[name]_2015-09-21.docx" It ends up turning into: "[name] _2015-09-21.docx" This is where word throws the error because for some reason rather than taking the existing document name as I have told it to it tries to recreate it and adds a carriage return, but if I go ahead and click the save button again even after getting the upload error it runs normally. — Andrew Addison

xidgel xidgel · Accepted Answer · 2015-09-21T16:12:17

Word table cell text ranges are terminated with two hidden chars, a carriage return (ASCII 13) and a bell (ASCII 7). Your IF condition returns false because it is testing the equivalence of "1" and "1" & Chr(13) & Chr(7).

In your case you can limit the test to the first char:

If Left$(ActiveDocument.Tables(1).Rows(1).Cells(3).Range.Text, 1) = "1" Then

More generally you can test the visible cell contents with a Len() - 2.

Hope that helps.

FileSave() Word Macro

1 Answers