Macro Save As filename not working when saving in the network using IP address

Question

I have working save as line for my VBA script in excel before and the file is saving to a network folder. Before, the server has name, now we access the server folders using an IP (192.168.20.212), hence I changed the address in code using the IP.

Now, the problem is that the file naming I set is not working. When the dialog box appears, the filename is empty and the user needs to manually enter the filename. However, if I put a server name or use local address, the file naming is working. I have no choice but use the IP to save file.

The following is the line for the file naming;

    filenme = "PENDING CLAIMS_" + szNextDatereformat

And the following is the line for saving the file before;

Dim sFileSaveName As String
        sFileSaveName = Application.GetSaveAsFilename _
                                     (InitialFileName:="\\SERVERNAME\excel_files\" & filenme & sTargetFile, _
                                      FileFilter:="Excel Files (*.xlsx), *.xlsx")

        If sFileSaveName <> "False" Then
             '-- Savethe file --
             Application.DisplayAlerts = False
             ActiveWorkbook.SaveAs FileName:=sFileSaveName, _
                                   FileFormat:=51
            Application.DisplayAlerts = True
        Else
             '-- Popup message --
             MsgBox "Template not saved!", vbExclamation, "Warning"
        End If

The new one should be;

Dim sFileSaveName As String
        sFileSaveName = Application.GetSaveAsFilename _
                                     (InitialFileName:="\\192.168.20.212\excel_files\" & filenme & sTargetFile, _
                                      FileFilter:="Excel Files (*.xlsx), *.xlsx")

        If sFileSaveName <> "False" Then
             '-- Savethe file --
             Application.DisplayAlerts = False
             ActiveWorkbook.SaveAs FileName:=sFileSaveName, _
                                   FileFormat:=51
            Application.DisplayAlerts = True
        Else
             '-- Popup message --
             MsgBox "Template not saved!", vbExclamation, "Warning"
        End If

Have you tried to manually copy the file to your IP address, just to be sure it's not a network (or permission) related problem? — Louis
I tried it and the network is working with write permission. Even, when running the macro, the dialog box for saving is going to the correct network folder path, it's just that the filename bar in the dialog box is empty and the the user needs to put manually for the filename. However, if I put local address or use server name instead of IP the file naming is working. — Paolo Medina

Mikisz Mikisz · Accepted Answer · 2019-04-10T08:01:53

something like this works for me:

Dim txtFileName As String
Dim finalPath As String

finalPath = "\\10.10.10.11\PUBLIC\SOMETHING\"
finalPath = finalPath & "myWorkbookName.xlsx"

txtFileName = Application.GetSaveAsFilename(finalPath, "Excel (*.xlsx), *.xlsx", , "Excel network save")
        If txtFileName = "False" Then
            MsgBox ("We could not save Excel.")
            Exit Sub
        End If

From my observations problems are when: - computer does not have access to network share, - workbook name that is proposed does not match "rules" of saving, e. g. is too long or has same strange characters.

You should try to:

1) check if computer has access to this network drive

2) check if IP address is correct

3) check if file name is correct.

Macro Save As filename not working when saving in the network using IP address

2 Answers