VBA Save As with period in name

[Realtreegirl75]

I'm working on creating a VBA that will save a document automatically based on information in the document. Here is the code:


Dim tdayName As String
Dim MPID As String
Dim numb As String
Dim tday As String


MPID = Range("O4").Text
numb = Range("C4").Text
tday = Range("H4").Text
tdayName = "SCP - " & MPID & " - " & numb & " - " & tday

MsgBox tdayName
Application.Dialogs(xlDialogSaveAs).Show tdayName


O4 is 123456
C4 is E.123456
H4 is 2022-10-30

The message box shows the correct name SCP - 123456 - E.123456 - 2022-10-30
The name won't come up in the save dialog box unless I remove the "." from cell C4. Is there anyway around this or will we just have to leave the period out when typing in the number in cell C4?

 

[DanteAmor]

Try to select the folder first.
Try one of these options:

Sub test1()
  Dim tdayName As String, MPID As String
  Dim numb As String, tday As String
  Dim sPath As String
  
  MPID = Range("O4").Text
  numb = Range("C4").Text
  tday = Range("H4").Text
  tdayName = "SCP - " & MPID & " - " & numb & " - " & tday
  
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    If .Show <> -1 Then Exit Sub
    sPath = .SelectedItems(1)
    ActiveWorkbook.SaveAs Filename:=sPath & "\" & tdayName & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook
  End With
End Sub

Sub test2()
  Dim tdayName As String, MPID As String
  Dim numb As String, tday As String
  Dim sPath As String
  
  MPID = Range("O4").Text
  numb = Range("C4").Text
  tday = Range("H4").Text
  tdayName = "SCP - " & MPID & " - " & numb & " - " & tday
  
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    If .Show <> -1 Then Exit Sub
    sPath = .SelectedItems(1)
    ActiveWorkbook.SaveCopyAs sPath & "\" & tdayName & ".xlsm"
  End With
End Sub

 

[Realtreegirl75]

I can't select the folder first because this is a form that will go out to around 50 people and they will all be saving it in different locations on their computers. I have this exact code working in a different form (without the E.123456) and it works great every time. And this works if I take the "." out of the E123456, I was just wondering if theres a way to be able to leave it in.

 

[DanteAmor]

Application.Dialogs(xlDialogSaveAs)

Don't use that way of saving, just save the file.

ActiveWorkbook.SaveAs Filename:=tdayName & ".xlsx", FileFormat:=xlOpenXMLWorkbook

 

[Realtreegirl75]

So I asked a question that I thought might be another way to fix the issue and it got the desired results: Use a VBA to remove the period in the cell. The code used was this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if update is not to cell AC3
    If Intersect(Target, Range("AC3")) Is Nothing Then Exit Sub
   
'   Get rid of periods in entry
    Application.EnableEvents = False
    Range("AC3").Value = Application.WorksheetFunction.Substitute(Range("AC3").Value, ".", "")
    Application.EnableEvents = True

End Sub

And now everything works perfectly. Thanks!

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags in the previous post for you this time.

BTW, rather than call out to the worksheet function Substitute to replace any dots, it would be more efficient to use vba's own function, Replace.

Range("AC3").Value = Application.WorksheetFunction.Substitute(Range("AC3").Value, ".", "")
Range("AC3").Value = Replace(Range("AC3").Value, ".", "")