Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FileSave macro (Excel 97)

    I want to add a few toolbar buttons to save files having very long pathnames. I've tried the macro record and then edit approach to get to the dialog box for giving a SaveAs file name but can't seem to get it to work. I need to have the macro open the saveas dialog box after a path something like "Kurgroupthisyeargeneralledgermonthlclosingsmay03com panyxyz"...then give me the dialog box to save the given file name or alter it. I would have one per company. That would save lots of time in navigating to the specific folder. Thanks.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSave macro (Excel 97)

    Something like this should do:

    Sub GetSaveAsFileNameExample()
    Dim vFilename As Variant
    Dim sPath As String
    sPath = "Kurgroupthisyeargeneralledgermonthlclosingsmay03com panyxyz"
    vFilename = Application.GetSaveAsFilename(sPath & "YourFileNameGoesHere", , , "Please enter a filename")
    If TypeName(vFilename) = "Boolean" Then Exit Sub
    If vFilename = "" Then Exit Sub
    ThisWorkbook.SaveAs vFilename
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSave macro (Excel 97)

    The file save dialog box does not show the network path in the variable, rather it shows a path to the C: dirve. It is not replacing that pathway with the one in vFilename. Any ideas? I tried recording a simple one and noted that it had a ChDir from C: to F: Yet I could not do a ChDir (sPath) to work.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: FileSave macro (Excel 97)

    chDir will change the folder
    chDrive will change the drive.


    zeddy

  5. #5
    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: FileSave macro (Excel 97)

    Is the problem a typo?:

    sPath = "Kurgroupthisyeargeneralledgermonthlclosingsmay03com panyxyz"
    Is NOT a valid path

    Try it with this statement:
    sPath = "Kurgroupthisyeargeneralledgermonthlclosingsmay03com panyxyz"

    If the path is NOT valid it will default to C:. I used the code with a path on my network and it seemed to work as expected. I can't test with above, since I don't have all the folders and was too lazy to create them all.

    Steve

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSave macro (Excel 97)

    Thanks...what I did was copy the pathname from an explorer bar. That avoided any typos, and works well. How could I add the currnet FILENAME to the dialog box. In other words, very often the issue is to save the file as named to the location. Sometimes, however, one needs to add something like Amended or After adjustment to the file name. So if I could get the existing name in the dialog box, the users could have the option to press the save button OR amend the file name in some way. Thanks to all for getting us this far... <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  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: FileSave macro (Excel 97)

    activeworkbook.Name

    If you use this in the code instead of the prompted name:
    activeworkbook.Name &"-Modified"

    <pre>sPath = "Kurgroupthisyeargeneralledgermonthlclosingsmay03com panyxyz"
    vFilename = Application.GetSaveAsFilename _
    (sPath & activeworkbook.Name &"-Modified", , , "Please enter a filename")</pre>


    It will prompt for that name

    If the file has the path desired you could use:
    activeworkbook.FullName &"-Modified"

    and this will include the path. Then you can eliminate the "path" stuff from the code.
    <pre>vFilename = Application.GetSaveAsFilename _
    (activeworkbook.FullName &"-Modified", , , "Please enter a filename")</pre>


    Steve

  8. #8
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSave macro (Excel 97)

    When I use the Activeworkbook.name code, I get the current filename within quotes, including the .xls suffix.
    When I try to save a file down the pathway to the folder setup by the variable, ie K:.... , I get a Runtime 1004 "you cannot save this workbook with the same name as another open...." . I s it the quote situation making this happen? How can I strip out the "s and .xls? Maybe that will do it. I feel we're very close to a solution.Thanks.

  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: FileSave macro (Excel 97)

    You could use substitute function:

    application.WorksheetFunction.Substitute(activewor kbook.FullName,".xls","-modified.xls")

    Or MID and Length:

    mid(activeworkbook.FullName,1,len(activeworkbook.f ullname)-4)&"-modified.xls"

    Steve

  10. #10
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSave macro (Excel 97)

    vFilename = Application.GetSaveAsFilename(sPath & Mid(ActiveWorkbook.FullName, 1, Len(ActiveWorkbook.FullName), , , , "save")) is accepted by the compiler but that's all. I think only one should be used, but can't get the darn immediate windows to test my idea.

  11. #11
    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: FileSave macro (Excel 97)

    Full name has the path no need for sPath, you are doubling up on the path name!
    Your mid string does not do anything. I thought you wanted to add something before the ".xls"?
    Mid(ActiveWorkbook.FullName, 1, Len(ActiveWorkbook.FullName) = ActiveWorkbook.FullName

    How about
    Either Define path, get name:
    <pre>vFilename = Application.GetSaveAsFilename _
    (sPath & activeworkbook.Name,,,"Save")</pre>

    or Use current path and name:
    <pre>vFilename = Application.GetSaveAsFilename _
    (activeworkbook.FullName,,,"Save")</pre>

    or Define path, get name add "-Modified" before ".xls":
    <pre>vFilename = Application.GetSaveAsFilename _
    (mid(activeworkbook.FullName,1,len(activeworkbook. fullname)-4)&"-modified.xls",,,"Save")</pre>

    or get path/name add "-Modified" before ".xls":
    <pre>vFilename = Application.GetSaveAsFilename _
    (sPath & mid(activeworkbook.Name,1,len(activeworkbook.name)-4)&"-modified.xls",,,"Save")</pre>


    Steve

  12. #12
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSave macro (Excel 97)

    S: Thank you for your continued interest! The whole purpose of this utility macro is to put a new, lengthy pathway to a folder location into the file save as dialog box. The file arrives via email. So the saveas dialog box starts with that pathway to the OLE or whatever location. I want to put that lengthy pathway to the folder location PLUS the existing filename, which may or may not need to be changed. THe first suggested solution worked well with the only problem being that the filename dialog box had "quotes.xls" surrounding the entire file name, causing problems. See attached screenshot. My followup was basically asking how to get rid of those quotes and the .xls extension, then it ought to be fine. In execution, the filename is highlighted. I took that off so you could see the quotes. Thank you for your continued interest.

  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: FileSave macro (Excel 97)

    Explicitly tell excel it is a Workbook

    <pre>vfilename = Application.GetSaveAsFilename _
    (sPath & ActiveWorkbook.Name, "Microsoft Excel Workbook (*.xls), *.xls", , "Save")</pre>


    Steve

  14. #14
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSave macro (Excel 97)

    Working well..thanks. Now, I can modify the pathway to the multiple company & Period combos needed and have a toolbar button for each company. This will save lots of navigating effort when you're getting dozens of entries.

  15. #15
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSave macro (Excel 97)

    Here is the existing macro I have for this. I abandoned trying to get it to work for a while. Now, I have some time again.
    Sub Save453()
    Dim vFilename As Variant
    Dim sPath As String
    sPath = "F:FY2004General LedgerMonthly Closing3-June 03453 SOUTH"
    vFilename = Application.GetSaveAsFilename(sPath & ActiveWorkbook.Name,
    "Microsoft Excel Workbook (*.xls), *.xls", , "Save")
    If TypeName(vFilename) = "Boolean" Then Exit Sub
    If vFilename = "" Then Exit Sub
    End Sub

    I'm not at the office, but I think it also stops at the last If vFilename="" Then ExitSub line as well. Which of the two is the correct one?
    Again, I want to use the existing filename and just change the pathway from the existing OKL...etc e-mail folder pathway to a network location such as shown. Yet, when I run this, it doesn't seem to work. Can any errors be pointed out? I will work with some of the suggested alternatives. Thanks alot.

Page 1 of 2 12 LastLast

Posting Permissions

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