CODEWITHSUNDEEP
vbaexcel
Sergey
Sergey

Reputation: 123

SaveAs won't accept strings that contain "." in Excel VBA

I am using the following code in order to add a new workbook,save and name the workbook (based on a date which is located in a certain cell in the sheet).

Dim wb As Workbook
Dim wbName As String
wbName = ThisWorkbook.Sheets("Sheet1").Range("M145").value 


    fName = Application.GetSaveAsFilename(wbName)
    If fName = False Then
        MsgBox "Publish couldn't be completed since you didn't choose where to save the file."
        Exit Sub
    Else
        Set wb = Workbooks.Add
        wb.SaveAs (fName)
    End If

But it seems that whenever cell "M145" contains dots (".") as in "31.3.16", my file name doesn't appear in the SaveAs prompt and I see a blank line without any error message.

I don't think that this has anything to do with it, but my sheet is right-to-left. Does anyone has an idea on how to fix this?

Upvotes: 3

Views: 1521

Answers (2)

user4039065
user4039065

Reputation:

There two things going on here.

First, M145 likely contains a date that has been given a format mask of dd\.mm\.yy. To get that displayed value out of the cell and into a variable you need the Range.Text property, not the Range.Value property.

Second, the default filetype for the Application.GetSaveAsFilename method is *.* which means that it is going to accept the .yy as the file extension. You need to limit the available file extensions to Excel file types.

Dim wb As Workbook
Dim wbName As String, fName As Variant  'variant in case user clicks Cancel

wbName = ThisWorkbook.Sheets("Sheet1").Range("M145").Text

With Application
    fName = .GetSaveAsFilename(InitialFileName:=wbName, _
                               FileFilter:="Excel Workbook (*.xlsx), *.xlsx," & _
                                           "Macro Workbook (*.xlsm), *.xlsm," & _
                                           "Binary Workbook (*.xlsb), *.xlsb")
End With

That gets you past the problems with your filename selection. However, the Workbook.SaveAs method should also provide the correct XlFileFormat Enumeration.

Select Case fName
    Case False
        'user clicked Cancel or Close (×)
        Debug.Print fName
    Case Else
        With Workbooks.Add
            Select Case Right(fName, 5)
                Case ".xlsx"
                    .SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbook
                Case ".xlsm"
                    .SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
                Case ".xlsb"
                    .SaveAs Filename:=fName, FileFormat:=xlExcel12
                Case Else
                    'there really shouldn't be a case else
            End Select
        End With
End Select

Upvotes: 2

David Zemens
David Zemens

Reputation: 53623

While I'm not able to replicate the error, perhaps you will have better luck with a FileDialog object:

Dim wb As Workbook
Dim wbName As String
Dim fdlg As FileDialog

wbName = ThisWorkbook.Sheets("Sheet1").Range("M145").value 

Set fdlg = Application.FileDialog(msoFileDialogSaveAs)
With fdlg
    .InitialFileName = wbName
    .Show
    Set wb = Workbooks.Add
    On Error Resume Next 'Suppress any errors due to invalid filename, etc.
    wb.SaveAs(fdlg.SelectedItems(1))
    If Err.Number <> 0 Then  
        MsgBox "Publish couldn't be completed since you didn't choose where to save the file."
        wb.Close False  'Get rid of the workbook since it's not being saved
        Exit Sub
    End If
    On Error GoTo 0 'Resume normal error handling
End With

Upvotes: 6

Related Questions