select directory using wildcards with excel and VBA

[thomasks]

Hi all,
I am trying to save a file to a directory that I don't always know what the name will be. I am trying to build a string and use wildcards to fill in the remainder of the directory name and I'm not getting the result I need. Could someone take a look and see if they could help? Thanks for the time.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Build string for file name using the cells from the worksheet that contain
'the project number, quote type, and version.
Dim sProjNum As String
Dim ThisFile As String
Dim sFilePath As String

    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)
sFilePath = "T:\Quot" & "\" & (sProjNum) & "?"
'Set the path to the T drive
Application.DefaultFilePath = sFilePath
'Open the save as dialog with the filename built from the string
fileSaveName = Application.GetSaveAsFilename(ThisFile, filefilter:="Excel Files (*.xls), *.xls")

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

'If user cancels, then exit the subroutine
If vbCancel = 2 Then
    Exit Sub
End If

End Sub

 

[ClulessChris]

What is the trouble you're having?

Everybody is somebodys Nutter.

 

[combo]

[!]sFilePath = "T:\Quot" & "\" & (sProjNum) & "?"[/!]
'?' is not allowed in path name.

[!]If vbCancel = 2 Then[/!]
vbCancel=2 by definition, it's a constant in VbMsgBoxResult enum.

combo

 

[Bong]

Consider the like operator or even just the dir operator. They both support wildcards.

_________________
Bob Rashkin