Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save As in VBA (2002)

    Loungers, I have the following code:-

    Private Sub CommandButton1_Click()
    Sheets("Details").Visible = True
    Sheets("Appraiser").Visible = xlVeryHidden
    Sheets("employee").Visible = xlVeryHidden
    Sheets("Summary sheet").Visible = xlVeryHidden
    Sheets("charts").Visible = xlVeryHidden
    Sheets("chart data").Visible = xlVeryHidden
    If ActiveWorkbook.Saved Then
    Do
    fName = Application.GetSaveAsFilename
    Loop Until fName <> False
    MsgBox "This workbook contains unsaved changes."
    ThisWorkbook.Saved = True
    End If

    End Sub

    I'm trying to arrange away for the 'Save as' screen to appear with the value of a cell , say A1 (persons name) in the File Name box and a File path and then normal 'save' after that, unless the value in A1 changes the the process would loop.

    I hope that makes sence - any suggestions

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save As in VBA (2002)

    .. "unless the value in A1 changes the the process would loop" doesn't quite make sense. And do you mean

    If Not ActiveWorkbook.Saved Then

    because if it is saved, why do you need to save again? Also, by forcing this loop, you are preventing the user from cancelling out of the Save. I don't think that GetSaveAsFilename can mandate a target save folder, but you can post-set it. Finally, you need to actually save the file. So how about something like:

    Do
    MsgBox "This workbook contains unsaved changes."
    fname = Application.GetSaveAsFilename(ThisWorkbook.Workshe ets("mysheet").Range("A1").Value, _
    "Microsoft Excel Workbook (*.xls), *.xls")
    Loop until fname <> FALSE
    ActiveWorkbook.SaveAs Filename:="Cocuments and SettingsOwnerMy DocumentsExcel" & fname
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As in VBA (2002)

    John - thanks for the reply.

    Will give your code a go! A bit more info. The spreadsheet that this code comes from is for recording personnel assessments info for ~50 people, so I've got a 'Read Only' file needs to be saved with the name of the person performing the assessment - they enter their name into A1 - once the file has been saved with the persons name in the file name I just need it to do a save when the button is activated. Then the next person would do the same.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save As in VBA (2002)

    Dean, in that case you might also be interested in ways to get the username by VBA, such as the examples in the thread starting with <!post=this post,293049>this post<!/post>.

    (Tried to post this last night my time, but the Lounge froze up on me.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As in VBA (2002)

    John, Thanks again. I plugged in your formula as follows:-

    Private Sub CommandButton1_Click()
    Sheets("Details").Visible = True
    Sheets("Appraiser").Visible = xlVeryHidden
    Sheets("employee").Visible = xlVeryHidden
    Sheets("Summary sheet").Visible = xlVeryHidden
    Sheets("charts").Visible = xlVeryHidden
    Sheets("chart data").Visible = xlVeryHidden
    'If ActiveWorkbook.Saved Then'
    Do
    MsgBox "This workbook contains unsaved changes."
    fname = Application.GetSaveAsFilename(ThisWorkbook.Workshe ets("Details").Range("F8").Value, _
    "Microsoft Excel Workbook (*.xls), *.xls")
    Loop Until fname <> False
    ActiveWorkbook.SaveAs Filename:="Cocuments and SettingsOwnerMy DocumentsExcel" & fname
    'End If'

    End Sub

    When I run it I get the following error "Run-time Error '1004 - Application-Defined or object -definded error" when I try to save the file.

    Any thoughts

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

    Re: Save As in VBA (2002)

    The result of GetSaveAsFileName includes the path, not only the file name, so you should not prefix it with "Cocuments and SettingsOwnerMy DocumentsExcel".

    When you report an error, it is always useful to mention the exact line where it occurs.

  7. #7
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As in VBA (2002)

    Thanks Hans - You've done it again.
    I modified as below:-
    Private Sub CommandButton1_Click()
    Sheets("Details").Visible = True
    Sheets("Appraiser").Visible = xlVeryHidden
    Sheets("employee").Visible = xlVeryHidden
    Sheets("Summary sheet").Visible = xlVeryHidden
    Sheets("charts").Visible = xlVeryHidden
    Sheets("chart data").Visible = xlVeryHidden
    'If ActiveWorkbook.Saved Then'
    Do
    MsgBox "This workbook contains unsaved changes."
    fname = Application.GetSaveAsFilename(ThisWorkbook.Workshe ets("Details").Range("F8").Value, _
    "Microsoft Excel Workbook (*.xls), *.xls")
    Loop Until fname <> False
    ActiveWorkbook.SaveAs Filename:=fname
    'End If'

    End Sub

    How would this be modified if the user cancels the "Save As" if it happens now it loops between MsgBox and the 'save as' screen. Also if the file has already been created in the 'Save As' can the code be modified so that it will save the changes without opening the 'Save As" screen.

    I hope that all makes sence - Any thoughts

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

    Re: Save As in VBA (2002)

    Something like this:

    Dim fName As Variant
    ' Test if workbook is on disk
    If InStr(ActiveWorkbook.FullName, "") > 0 Then
    ' Yes, save it
    ActiveWorkbook.Save
    Else
    ' No, get file name
    fName = Application.GetSaveAsFilename
    ' User cancelled, so get out
    If fName = False Then Exit Sub
    ' Save with specified name
    ActiveWorkbook.SaveAs fName
    End If

  9. #9
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As in VBA (2002)

    Hans, Thanks - Sorry about my ignorance with this - still learning.

    Would your code replace some of the existing or be added to it some place?

    Either way would you mind explaining what I need to do

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

    Re: Save As in VBA (2002)

    The code i posted would replace this part:

    Do
    MsgBox "This workbook contains unsaved changes."
    fname = Application.GetSaveAsFilename(ThisWorkbook.Workshe ets("Details").Range("F8").Value, _
    "Microsoft Excel Workbook (*.xls), *.xls")
    Loop Until fname <> False
    ActiveWorkbook.SaveAs Filename:=fname

  11. #11
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As in VBA (2002)

    Thanks again Hans - will give it a shot!

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save As in VBA (2002)

    It does? My error.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Save As in VBA (2002)

    The online help for Excel 2002 VBA says that the result of GetSaveAsFileName may contain a path. In my limited testing, it always did...

Posting Permissions

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