Anyone have recommendations on how to avoid getting an error when a user tries to rename a file the user is initially trying to save? My file name is stored in cell 3,4. The code then takes whatever is in that cell and uses it as the file name to save. This code works fine if the file doesn't exist. If it does exist and the user does not overwrite the existing file and chooses to rename the file something else I then run into a problem. Perhaps there is a better way to do the code to get the same results.
Sub TestGetFolderName()
Dim FolderName As String
Dim txtFileName As String
txtFileName = ActiveWorkbook.Sheets("Home").Cells(3, 4)
FolderName = GetFolderName("Select a folder")
If FolderName = "" Then
MsgBox "You didn't select a folder."
Else
ActiveWorkbook.SaveAs Filename:=FolderName & "\" & txtFileName & ".xlsx" _
, FileFormat:=51
End If
End Sub
***this is the function
Function GetFolderName(Msg As String) As String
Dim bInfo As BrowseInfo, Path As String, r As Long
Dim x As Long, pos As Integer
bInfo.pidlRoot = 0& ' Root folder = Desktop
If IsMissing(Msg) Then
bInfo.lpszTitle = "Select a folder."
' the dialog title
Else
bInfo.lpszTitle = Msg ' the dialog title
End If
bInfo.ulFlags = &H1 ' Type of directory to return
x = SHBrowseForFolder(bInfo) ' display the dialog
' Parse the result
Path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal Path)
If r Then
pos = InStr(Path, Chr(0))
GetFolderName = Left(Path, pos - 1)
Else
GetFolderName = ""
End If
End Function
at the top of my module is
Global TypeSelect
Private Type BrowseInfo ' used by the function GetFolderName
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal Pidl As Long, ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) As Long
Bookmarks