File Save with Predefined Name and Value from TextBox

[HTSCF Fareha]

I'm trying to allow the user to save their document, which is produced via a UserForm and utilising content controls, using the file name as "NFA Letter - " and string this together with the entry from the TextBox called txtRMSNum.

The error checking for ensuring that txtRMSNum has already been catered for, so this literally only needs to provide the concatenated file name prepopulated ready for the user to just select the save location.

Here is what I have so far, which is not producing the desired result.


Option Explicit

Sub ShowSaveAsDialog()

Dim oDialog As Dialog
Dim occ As ContentControl
Dim oRng As Range

Set oRng = occ.Range.Text
.txtRMSNum.Text = occ.Range.Text

Set oDialog = Application.Dialogs(wdDialogFileSaveAs)
With oDialog
.Name = "NFA Letter - " & oRng.txtRMSNum.Text
.Format = wdFormatXMLDocument
.Show
End With
lbl_Exit:
Exit Sub
End Sub

[gmayor]

You must tell the macro what OCC refers to e.g. as follows. I have assumed that the userform has written a value to the content control OCC, and that OCC is called 'txtRMSNum'. Make the changes as appropriate if this is not so.

Sub ShowSaveAsDialog()
Dim oDialog As Dialog
Dim occ As ContentControl
'assumes the CC is titled 'txtRMSNum'
Set occ = ActiveDocument.SelectContentControlsByTitle("txtRMSNum").Item(1)
If occ.ShowingPlaceholderText = True Then
MsgBox "Enter the data for this field!", vbCritical
occ.Range.Select
GoTo lbl_Exit
End If
Set oDialog = Application.Dialogs(wdDialogFileSaveAs)
With oDialog
.Name = "NFA Letter - " & occ.Range.Text
.Format = wdFormatXMLDocument
.Show
End With
lbl_Exit:
Set occ = Nothing
Set oDialog = Nothing
Exit Sub
End Sub

[HTSCF Fareha]

My thanks to you Graham, this works perfectly! :thumb

Just altered the CC as advised.

[gmayor]

It will work provided there are no illegal filename characters written to the content control. If that is a possibility check the name for illegal characters using a function called from the code e.g.

With oDialog
.Name = "NFA Letter - " & CleanFilename(occ.Range.Text)


Private Function CleanFilename(strFileName As String) As String
Dim arrInvalid() As String
Dim lng_Index As Long
'Define illegal characters (by ASCII CharNum)
arrInvalid = Split("9|10|11|13|34|42|47|58|60|62|63|92|124", "|")
'Remove any illegal filename characters
CleanFilename = strFileName
For lng_Index = 0 To UBound(arrInvalid)
CleanFilename = Replace(CleanFilename, Chr(arrInvalid(lng_Index)), Chr(95))
Next lng_Index
lbl_Exit:
Exit Function
End Function

[HTSCF Fareha]

Thank you, Graham.

Illegal filename characters shouldn't be an issue in my scenario, but I have added this function in just in case because one can never tell if a user decides to alter the name of the intended save document.

[HTSCF Fareha]

Thought I'd test it by throwing in a "%" in the file name and unfortunately I get a

Compile error - Wrong number of arguments or invalid property assignments here:-


CleanFilename = Replace(CleanFilename, Chr(arrInvalid(lng_Index)), Chr(95))

[gmayor]

% is not an illegal filename character. If you want to exclude % then add 37 to arrInvalid. (see below)
The CleanFilename function only acts on the contents of the content control. There are no illegal characters in the fixed part of the name.
How did you test it?

Sub ShowSaveAsDialog()
Dim oDialog As Dialog
Dim occ As ContentControl
'assumes the CC is titled 'txtRMSNum'
Set occ = ActiveDocument.SelectContentControlsByTitle("txtRMSNum").Item(1)
If occ.ShowingPlaceholderText = True Then
MsgBox "Enter the data for this field!", vbCritical
occ.Range.Select
GoTo lbl_Exit
End If
Set oDialog = Application.Dialogs(wdDialogFileSaveAs)
With oDialog
.Name = "NFA Letter - " & CleanFilename(occ.Range.Text)
.Format = wdFormatXMLDocument
.Show
End With
lbl_Exit:
Set occ = Nothing
Set oDialog = Nothing
Exit Sub
End Sub

Private Function CleanFilename(strFileName As String) As String
Dim arrInvalid() As String
Dim lng_Index As Long
'Define illegal characters (by ASCII CharNum)
arrInvalid = Split("9|10|11|13|34|37|42|47|58|60|62|63|92|124", "|")
'Remove any illegal filename characters
CleanFilename = strFileName
For lng_Index = 0 To UBound(arrInvalid)
CleanFilename = Replace(CleanFilename, Chr(arrInvalid(lng_Index)), Chr(95))
Next lng_Index
lbl_Exit:
Exit Function
End Function

[HTSCF Fareha]

Apologies Graham, I should've run a couple of other tests on characters because as you say, % is not an illegal character.

I've run the original Userform which populates all the content controls on the document.

The "ShowSaveAsDialog" sub is run when a ribbon button is pressed. Regardless of whether I put in an illegal character or not in the "RMS Number", it will throw up the previously mentioned error.

[gmayor]

What is 'RMS Number'? If that is the title of the content control, did you change the title of the content control in the Code to match?

Set occ = ActiveDocument.SelectContentControlsByTitle("txtRMSNum").Item(1)

[HTSCF Fareha]

Yes, I changed it as below.


Option Explicit

Sub ShowSaveAsDialog()
Dim oDialog As Dialog
Dim occ As ContentControl

Application.ScreenUpdating = False

Set occ = ActiveDocument.SelectContentControlsByTitle("RMS Number").Item(1)
If occ.ShowingPlaceholderText = True Then
MsgBox "Enter the RMS Number!", vbCritical, "Triage Hub"
occ.Range.Select
GoTo lbl_Exit
End If
Set oDialog = Application.Dialogs(wdDialogFileSaveAs)
With oDialog
.Name = "NFA Letter - " & CleanFilename(occ.Range.Text)
.Format = wdFormatXMLDocument
.Show
End With

Application.ScreenUpdating = True

lbl_Exit:
Set occ = Nothing
Set oDialog = Nothing
Exit Sub
End Sub

Private Function CleanFilename(strFileName As String) As String
Dim arrInvalid() As String
Dim lng_Index As Long
'Define illegal characters (by ASCII CharNum)
arrInvalid = Split("9|10|11|13|34|42|47|58|60|62|63|92|124", "|")
'Remove any illegal filename characters
CleanFilename = strFileName
For lng_Index = 0 To UBound(arrInvalid)
CleanFilename = Replace(CleanFilename, Chr(arrInvalid(lng_Index)), Chr(95))
Next lng_Index
lbl_Exit:
Exit Function
End Function

[gmayor]

Can you post the document?

[HTSCF Fareha]

Arghh, I hate it when Windows 10 decides that it is going to do an update! :steamed:

Sorry Graham, shortly after managing to get my last post in, I started to do a bit of searching on the internet and found the answer that seems to work. I have altered this line


CleanFilename = Replace(CleanFilename, Chr(arrInvalid(lng_Index)), Chr(95))

And have replaced it with this


CleanFilename = VBA.Strings.Replace(CleanFilename, Chr(arrInvalid(lng_Index)), Chr(95))


I found this suggestion here https://stackoverflow.com/questions/27488761/replace-compile-error-wrong-number-of-arguments-or-invalid-property-assignment


Now everything is working!

My thanks to you Graham for looking at this for me.

Steve