Results 1 to 3 of 3

Save As-Overwrite existing File

Threaded View

  1. #1
    Registered User
    Join Date
    08-24-2012
    Location
    nowhere
    MS-Off Ver
    Excel 2003
    Posts
    2

    Save As-Overwrite existing File

    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
    Last edited by loulorisgomez; 10-02-2012 at 10:40 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1