Store excel file in network directory with VBA

[thomasks]

I have been trying to write some code to store an excel file onto a network drive with limited success. Could someone look this over and tell me where I am going wrong?
Thanks

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sProjNum As String
Dim ThisFile As String
Dim sFilePath As String
Dim MyFile As String
Dim MyPath
Dim sFileSaveName As String

'Build string for file name using the cells from the worksheet that contain
'the project number, quote type, and version.

    ThisFile = Worksheets("Pricing Sheet").Range("K10").Value _
    & " " & Worksheets("Pricing Sheet").Range("K11").Value & " " _
    & Worksheets("Pricing Sheet").Range("K12").Value & " " & "estwksht" & ".xls"
    sProjNum = Left(ThisFile, 6)

' Find the path on the T drive that matches the project number and store in a string
' called "sFilePath"
MyPath = ("T:\Quot\" & (sProjNum) & "*")
sFilePath = "T:\Quot\" & Dir(MyPath, vbDirectory) & "\50 Estimating (Quote file)" _
& "\85 Pricing" & "\d Controls"
Debug.Print sFilePath
'Set the path to the T drive, project number folder, and subfolders
Application.DefaultFilePath = sFilePath
Debug.Print Application.DefaultFilePath

'Open the save as dialog with the filename built from the string and path
sFileSaveName = Application.GetSaveAsFilename(ThisFile, filefilter:="Excel Files (*.xls), *.xls")
Debug.Print sFileSaveName

'If user has selected the filename and folder to store the new worksheet, then save it.
If sFileSaveName <> "False" Then
    Application.EnableEvents = False
    Application.ActiveWorkbook.SaveAs Filename:=sFileSaveName
    Application.EnableEvents = True
    Cancel = True
    sFilePath = vbNullString
    sFileSaveName = vbNullString
    
    Else
    sFilePath = vbNullString
    sFileSaveName = vbNullString

    Exit Sub
End If

'If user cancels, then exit the subroutine
If vbCancel = 2 Then
sFilePath = vbNullString
sFileSaveName = vbNullString

    Exit Sub
End If
sFilePath = vbNullString
sFileSaveName = vbNullString

End Sub

 

[combo]

You still try to pass an invalid character (*) into directory name. This will never work.
Some hints:
- build directory string and use Dir for testing it first,
- Application.DefaultFilePath will only mess excel,
- point proper path in save dialog and it will use it. Depending on excel version:

Application.GetSaveAsFilename InitialFileName:="T:\"

With Application.FileDialog(msoFileDialogSaveAs)
    .InitialFileName = "T:\"
    .Show
End With

combo