Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save Buttion/Save As Selection (2002)

    I have XL files residing on a server which are accessible via 3rd party software via the web. My issue is that when a read-only file is opened and either the "Save Button" or "Save As" is selected. The individuals see the sub-directory where the files exist and every file. What I would like to do is take control over these two functions and navigate the user back to their desktop to save the file(s). I have code to recognize their desktop path but do not know how to take control over Save Button" or "Save As" functions.

    I didn't have this issue under W2K.

    Any advice would be appreciated.
    Thanks,
    John

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Save Buttion/Save As Selection (2002)

    You could add something like this to the thisworkbook object:

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sFileSaveName As String

    sFileSaveName = Application.GetSaveAsFilename("c:" & ThisWorkbook.Name)
    If sFileSaveName <> False Then
    ThisWorkbook.SaveAs FileName:=sFileSaveName, FileFormat:=xlNormal
    End If
    Cancel = True
    End Sub</pre>


    Before you do a save it will prompt for a name from the "c Drive" change default as desired. You can even give it a default name. It currently uses the current name.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Buttion/Save As Selection (2002)

    Steve,

    When I try running the code I come up with an error message "Type mismatch".

    Is it possible to change the path itself to C: via code? When you select "Save As" or "Save" you see the sub-directory on the server where the file came from. I thought it may be possible just to change the path in code prior to saving the sheet.

    John

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Save Buttion/Save As Selection (2002)

    1) What line gives you the error? When I add it to the workbook and choose save or saveas, It runs the code fine.
    2) The path is changed to "c:" via code

    <pre> sFileSaveName = Application.GetSaveAsFilename("c:" & ThisWorkbook.Name)</pre>


    Pulls up the saveas dialog and sets the path to c:

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Buttion/Save As Selection (2002)

    Steve,

    The line in error seems to be: "If sFileSaveName <> False Then"

    By stepping through the code it erros and if I select debug the line is highlighted in yellow.

    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Buttion/Save As Selection (2002)

    Replace the declaration

    Dim sFileSaveName As String

    with

    Dim sFileSaveName As Variant

    A string cannot be False, hence the Type Mismatch error. A Variant can be all types.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Save Buttion/Save As Selection (2002)

    Hans,

    Do you know if this is a change from earlier editions of XL?

    The code works in XL97 as written and designed. If a name is chosen from the list then the name <> False and it saves it, if cancel is chosen (or <Esc> is pressed) then the string is false and it is not saved. There are no type mismatch errors in XL97.

    Has VB gotten more picky??

    Steve

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Buttion/Save As Selection (2002)

    Hans,

    Thank you for the suggestion. I got the code to continue however at the "SAVE AS" prompt when I click on save the prompt appears again. I once again click on the save button and no file is actually saved.


    John

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Save Buttion/Save As Selection (2002)

    If you goto VB at add a break point at the line:
    if sFIlename <> false
    and then step thru it,
    does it run the saveas line?
    If not what is the value of sFileSavename?

    Steve

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Buttion/Save As Selection (2002)

    Apparently Excel 2002 VBA is more strict. I get the Type Mismatch error too if I declare As String, not when I declare As Variant. And I get prompted twice too; the moment SaveAs is called, the event fires again. So you must use Application.EnableEvents = False before, and Application.EnableEvents = True after the SaveAs instruction.

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Buttion/Save As Selection (2002)

    Hans,

    It worked very nicely.

    Being curious in nature, is it possible to differentiate between the "Save" button on the Standard Toolbar and from the dropdown selection under File>Save or Save As? What I would like to achieve: if the Standard Toolbar Save button is clicked or File>Save is selected, save the file to it's original location on the server. If the file>Save As is selected point the path to C: and save accordingly. Keep in mind my original post that the End User does not need to see the path to the server or other XL files (post 347211).

    John

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Save Buttion/Save As Selection (2002)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> oops, forgot about disabling the events.

    Steve

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Save Buttion/Save As Selection (2002)

    Check the variable SaveAsUI = true when it is a saveas, else it is a save

    Steve

    <pre>Option Explicit
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sFileSaveName
    If SaveAsUI Then
    sFileSaveName = Application.GetSaveAsFilename("c:" & ThisWorkbook.Name)
    If sFileSaveName <> False Then
    Application.EnableEvents = False
    ThisWorkbook.SaveAs FileName:=sFileSaveName, FileFormat:=xlNormal
    Application.EnableEvents = True
    End If
    Else
    Application.EnableEvents = False
    ThisWorkbook.Save
    Application.EnableEvents = True
    End If
    Cancel = True
    End Sub</pre>


  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Buttion/Save As Selection (2002)

    Steve,

    Exactly what I was looking for.

    Thanks for everyone's assistance.

    John

  15. #15
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Buttion/Save As Selection (2002)

    Thanks Steve - exactly what I needed too! [hailpraise]

Posting Permissions

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