Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save As (Excel 2000)

    Good Mornig,

    I hope all had a very nice holiday weekend.

    I'm approaching the end of my Excel experience (vba) and was hoping someone could help with one more detail. I'm going to use the current spreadsheet as a template. Many cells are automatically updated. Everything is working beautifully. What I would like to do next (This can be done nicely in Access--dog gone it I know it will ....just not Excel....wish I could persuade these folks to use Access....Oh well...)...is, at the CLOSE of the worksheet, do an automatic FILE SAVE AS (file dialog box to appear) which is set to an automatic path. I hope I'm clear here. Maybe better said. Once the user chooses to close the file, whether by the "x" in the upper right corner or "File Close", the "File Save" dialog box appears (with the path already set to say, c:my documentsthis location).

    My prefernce would be that the user wouldn't name the doc at all, rather the file would do the naming. i.e. The spreadsheet stores the patients name (initials), the date and the users name (initials). My prefence would be that the file name would be PatientsInitialsdateUsersInitials (with the date not have any / or -) in a predefined path, but not sure that can be accomplished or I might be asking too much.

    I would be so totally thrilled to just get the dialog box to appear with the predefined path.

    Thanks in advance,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Save As (Excel 2000)

    You can show the Save As dialog with a predefined path/filename as follows:

    Dim strFileName As String
    strfileName = ... ' assemble file name here
    Application.Dialogs(xlDialogSaveCopyAs).Show strFileName

    You could use the Workbook_BeforeClose event to save the document, but be aware that this will do so every time the workbook is closed, not only the first time. If you create a real Excel template (with extension .xlt), you could test if the workbook has been saved. The following code must go into the ThisWorkbook module; it gets the initials from cells A1 and A2 in Sheet1, and uses the date in yyyymmdd format, and stores the workbook in a network folder F:Excel. Adapt as needed.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim strPath As String
    Dim strFilename As String
    If InStr(ThisWorkbook.FullName, "") = 0 Then
    strPath = "F:Excel"
    strFilename = ThisWorkbook.Worksheets("Sheet1").Range("A1") & _
    Format(Date, "yyyymmdd") & _
    ThisWorkbook.Worksheets("Sheet1").Range("A2") & ".xls"
    'ThisWorkbook.SaveAs strPath & strFilename
    Application.Dialogs(xlDialogSaveAs).Show strPath & strFilename
    End If
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As (Excel 2000)

    OK....can't, for the life of me figure out what I've done wrong! I've saved the file as a true .xlt (template), I've put the following code in "ThisWorkbook" but nothing happens when I close it. ..............and I make a changes on purpose to test it.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim strPath As String
    Dim strFilename As String
    If InStr(ThisWorkbook.FullName, "") = 0 Then
    strPath = "Cocuments and Settingsrnewt01my documents"
    strFilename = ThisWorkbook.Worksheets("PatientInfo").Range("C1") & Format(Date, "yyyymmdd") & ThisWorkbook.Worksheets("PatientInfo").Range("D1") & ".xls"
    'ThisWorkbook.SaveAs strPath & strFilename
    Application.Dialogs(xlDialogSaveAs).Show strPath & strFilename
    End If

    End Sub

    What have I gone and done this time?

    Thanks for any input!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As (Excel 2000)

    It sounds like you put the code into a normal module in ThisWorkbook, not in the module behind the ThisWorkbook Object. In the VB Editor, right click on the ThisWorkbook object in the Project Explorer and then click on View Code in the pop up menu. In the left drop down list select Workbook. Paste the code here.
    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As (Excel 2000)

    OK...no luck....I've checked it just as you suggested. To verify It's where it's suppose to be:

    In the left drop down: it shows "ThisWorkbook"
    In the right dowp down: it shows "BeforeClose"

    Not sure what else to do...any other suggetions?
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Save As (Excel 2000)

    Post a stripped down version of your template (take out all the confidential stuff), zipped if necessary.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As (Excel 2000)

    Could you post a copy of the workbook with any confidential stuff removed?
    Legare Coleman

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As (Excel 2000)

    Sure ... here it is.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  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 As (Excel 2000)

    When I open the file attached, and then close it I get the save as dialog box with the name "mp20040601rn" as the default. Isn't that what you expect?

    Steve

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

    Re: Save As (Excel 2000)

    Since this is a template, you are supposed to create a new workbook from it. Right-click the template and select New. See what happens then. If I do that, a new workbook is created, and when I close it, the code fires, and as long as I entered some valid data, the intended file name is suggested.

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As (Excel 2000)

    OK...C...it was me being stupid....thank you guys....so much....have a nice day!!!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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