Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Post Simple SAVEAS to another computer question

    I need to have my business quote form PRINT AND SAVE macro automatically save the new quote file to a different PC on the network. For years I've only had quotes being written on my main OFFICE PC. Now they will be getting written on both the OFFICE PC and the OFFICE2 PC but should all be saved in one location of the original OFFICE PC.


    The following code has worked correctly for years by saving the file on the same PC that it was created on:


    ' THE FOLLOWING STATEMENT SAVES THE NEW QUOTE FILE
    Range("A1").Select
    Set Workbook = ActiveSheet
    ' Do
    fName = Application.GetSaveAsFilename(".xls")
    ' Loop Until fName <> False
    Workbook.SaveAs Filename:=fName



    Now though if the quote has been created on OFFICE2 it needs to get saved on OFFICE instead. Running the RECORD macro feature I get the following:

    ChDir "\\Office\d\MS Office Documents\Quotes"
    ActiveWorkbook.SaveAs Filename:= _
    "\\Office\d\MS Office Documents\Quotes\Temp network.xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False


    This works except it doesn't give me the option of entering a file name.

    I've tried blending the 2 sets of code by inserting the fName descriptions but have not been successful. If the option to enter a filename works then I remain on the OFFICE2 PC for the save location. If the file is getting saved on the OFFICE PC where I want then its defaulting to "Temp network.xls" as the file name.

    I'm close but just can't seem to find the right way to do this.

    As always your help is enormously appreciated.

    BH

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Hi BH,

    The changes made in blue should resolve your issue. It will save to your local computer by prompting for a name then save to your networked computer using the new path but same name.

    Code:
    '.....
    Range("A1").Select
    Set Workbook = ActiveSheet
    fname = Application.GetSaveAsFilename(".xls")
    Workbook.SaveAs Filename:=fname
    s = Split(fname, "\")
    fname = s(UBound(s))
    ChDir "\\Office\d\MS Office Documents\Quotes"
    ActiveWorkbook.SaveAs Filename:= _
    "\\Office\d\MS Office Documents\Quotes\" & fname, FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    '....
    End Sub
    HTH,
    Maud

    Note: this was untested

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Maudibe View Post
    Hi BH,

    The changes made in blue should resolve your issue. It will save to your local computer by prompting for a name then save to your networked computer using the new path but same name.

    Code:
    '.....
    Range("A1").Select
    Set Workbook = ActiveSheet
    fname = Application.GetSaveAsFilename(".xls")
    Workbook.SaveAs Filename:=fname
    s = Split(fname, "\")
    fname = s(UBound(s))
    ChDir "\\Office\d\MS Office Documents\Quotes"
    ActiveWorkbook.SaveAs Filename:= _
    "\\Office\d\MS Office Documents\Quotes\" & fname, FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    '....
    End Sub
    HTH,
    Maud

    Note: this was untested

    Thanks Maud,

    What if I don't need to save it on the local computer? Just delete the local computer top 4 lines of code? Or do I need those to predefine fName? I'd just try it but I'm at home now instead of at the office.

    LIKE THIS BELOW?

    Code:
    '.....
    
    s = Split(fname, "\")
    fname = s(UBound(s))
    ChDir "\\Office\d\MS Office Documents\Quotes"
    ActiveWorkbook.SaveAs Filename:= _
    "\\Office\d\MS Office Documents\Quotes\" & fname, FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    '....
    End Sub
    Thanks,
    BH

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Correct!

    Code:
    '.....
    fname = ThisWorkbook.Name
    ChDir "\\Office\d\MS Office Documents\Quotes"
    ActiveWorkbook.SaveAs Filename:= _
    "\\Office\d\MS Office Documents\Quotes\" & fname, FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    HTH,
    Maud

    Note: again, untested but should work fine

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    BH,

    in your original code, you were opening a save as dialogue box and prompting for a new name to save it to your local computer. If you need to still change the name, then why not just do a normal File > Save As > navigate to your network folder > Save?

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Maudibe View Post
    BH,

    in your original code, you were opening a save as dialogue box and prompting for a new name to save it to your local computer. If you need tostill change the name, then why not just do a normal File > Save As > navigate to your network folder > Save?
    That's what I was trying to do in the macro unsuccessfully. When I was adding the network path it was changing how it was working. I know a little more now about how it should be worded though so will try again.

    Thanks,
    BH

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Glad I was able to help. You may want to keep in mind the you can pull the name for it to be saved directly from a cell or a form.

    Change fname = ThisWorkbook.Name to

    fname = Range("B3")

    OR

    fname= Userform1.textbox1.value

  8. #8
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Maudibe View Post
    Hi BH,

    The changes made in blue should resolve your issue. It will save to your local computer by prompting for a name then save to your networked computer using the new path but same name.

    Code:
    '.....
    Range("A1").Select
    Set Workbook = ActiveSheet
    fname = Application.GetSaveAsFilename(".xls")
    Workbook.SaveAs Filename:=fname
    s = Split(fname, "\")
    fname = s(UBound(s))
    ChDir "\\Office\d\MS Office Documents\Quotes"
    ActiveWorkbook.SaveAs Filename:= _
    "\\Office\d\MS Office Documents\Quotes\" & fname, FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    '....
    End Sub
    HTH,
    Maud

    Note: this was untested


    Drats, didn't work. As written above it prompts to save on the local HDD but then fails. It also fails without the first 4 lines of code. It's saying something like S=split <empty> when I point at that line highlighted in yellow after the failure.

    I don't know if it matters but this is for Excel XP. I've maintained using it over the years because of the hassle it would be to deal with the macros on more recent versions.

    Thanks,
    BH

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    BH,

    Please use one of the following codes to save your workbook to a network folder. Not sure if your intention is to prompt for a name.

    Code:
    Sub Save_File()
    'SAVE FILE TO NETWORK FOLDER AS .XLS
    '------------------------------------
    'SAVE WITH SAME NAME AS CURRENT WORKBOOK NAME
        fname = ThisWorkbook.Name
        ActiveWorkbook.SaveAs Filename:="\\Office\d\MS Office Documents\Quotes\" & fname, FileFormat:=xlExcel8
    
    '------------------------------------
    'SAVE WITH A PROMPTED NAME
        fname = InputBox("Enter file name with no extension", "Save File as .xls")
        ActiveWorkbook.SaveAs Filename:="\\Office\d\MS Office Documents\Quotes\" & fname, FileFormat:=xlExcel8
    
    '------------------------------------
    'SAVE WITH NAME USING A CELL VALUE
        fname = Range("B3") 'ex: cell B3= "Yearly Quotes" without quotes
        ActiveWorkbook.SaveAs Filename:="\\Office\d\MS Office Documents\Quotes\" & fname, FileFormat:=xlExcel8
    
    '------------------------------------
    'SAVE WITH NAME USING VALUE FROM TEXTBOX ON A FORM
        fname = Userform1.textbox1.Value
        ActiveWorkbook.SaveAs Filename:="\\Office\d\MS Office Documents\Quotes\" & fname, FileFormat:=xlExcel8
    End Sub
    Last edited by Maudibe; 2016-11-20 at 08:35.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Maudibe View Post
    BH,

    Please use one of the following codes to save your workbook to a network folder. Not sure if your intention is to prompt for a name.

    Code:
    Sub Save_File()
    'SAVE FILE TO NETWORK FOLDER AS .XLS
    '------------------------------------
    'SAVE WITH SAME NAME AS CURRENT WORKBOOK NAME
        fname = ThisWorkbook.Name
        ActiveWorkbook.SaveAs Filename:="\\Office\d\MS Office Documents\Quotes\" & fname, FileFormat:=xlExcel8
    
    '------------------------------------
    'SAVE WITH A PROMPTED NAME
        fname = InputBox("Enter file name with no extension", "Save File as .xls")
        ActiveWorkbook.SaveAs Filename:="\\Office\d\MS Office Documents\Quotes\" & fname, FileFormat:=xlExcel8
    
    '------------------------------------
    'SAVE WITH NAME USING A CELL VALUE
        fname = Range("B3") 'ex: cell B3= "Yearly Quotes" without quotes
        ActiveWorkbook.SaveAs Filename:="\\Office\d\MS Office Documents\Quotes\" & fname, FileFormat:=xlExcel8
    
    '------------------------------------
    'SAVE WITH NAME USING VALUE FROM TEXTBOX ON A FORM
        fname = Userform1.textbox1.Value
        ActiveWorkbook.SaveAs Filename:="\\Office\d\MS Office Documents\Quotes\" & fname, FileFormat:=xlExcel8
    End Sub
    Maud,

    Thanks. Will try in the morning.

    I need to prompt for a name when the save is made so your 2nd suggestion should do the trick. Even better than before it would look like as now I think it is defaulting to the .xls file extension for us.

    Thanks again,
    BH

  11. #11
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Maud,

    That was close. A little editing and now this finally does the trick and brings it back to the original local folder to open the next file. Thanks for all your help.
    BH


    ' THE FOLLOWING STATEMENT SAVES THE NEW QUOTE FILE TO A NETWORK LOCATION
    Range("A1").Select
    Set Workbook = ActiveSheet
    fname = Application.GetSaveAsFilename("\\Office\d\MS Office Documents\Quotes\" & fname & (".xls"))
    Workbook.SaveAs Filename:=fname


    ' This changes to the AAQuote Local Directory
    ChDir "D:\MS Office Documents\Quotes"

Tags for this Thread

Posting Permissions

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