1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to save this open workbook to multi location (overwrite) without prompt?

Discussion in 'VBA Macros' started by Chirag R Raval, Aug 12, 2017.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear Sir,

    How to save this open workbook to multi location (overwrite) without prompt?

    I have a buyer master ,if , that updated in one this machines' location ,
    it need also to about 5 locations ..

    with this local machines'' 3 partition path + 2 another server location ..without prompt
    over write...

    if code available for do this ...help will be appreciated..

    Regards,
    Chirag Raval
  2. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear Sir,

    if main file is not open currently, also need to code for
    overwrite main location to 5 locations without prompt..but after finish
    msgbox display "Copy Done At ..locations"

    so any time we can over write some fix file on some fixed locations without
    open that file..

    Regards,
    Chirag Raval
  3. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear all,

    Though file not open can it will be possible to save(overwrite)
    That file on multi locations?multiple path? Without
    Face any dialog box?.

    Hope there are solution available there...

    Regards

    Chirag Raval
  4. Monty

    Monty Well-Known Member

    Messages:
    836
    Yes ofcourse.

    Before you save command..
    Use:

    Application.Displayalerts=False
    Application.Enablevents=False

    Activeworkbook.Saveas "Your location even if the file already exists it will simply overwrite without promot"

    Application.Displayalerts=True
    Application.Displayalerts=True
  5. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear sir,

    Thanks for effort...but..
    Active workbook means open workbook...
    & your code do that for just one time...on 1 location
    If need file overwrite On 7 locations means 7 time coding...!!!!
    Precise requirement is ....
    (1) without open file..(if already openopened .file first
    Over rite on it self...& then on 6 locations(destinations).
    (2) main file's location in drive "C" (1st sorce + dest.)
    D, E, F is 2nd , 3rd & 4th destinations all this 4
    Are local drives (in my pc)...
    Mapped server's 3 folders. Are 5th 6th & 7th locations...
    (3) no prompt displayed for over write but
    After end process must display message
    For (1) error for not reachable...on server & (2) can't do due to open..
    Server's file.. (If open by someone. May be
    There are also over write if code can do that...&
    This matter should also cover in message box)

    Hope I can described well whole requirement..

    Regards,

    Chirag Raval
  6. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear All,

    Can anyone focus on this?

    Regrads,
    Chirag Raval
  7. Monty

    Monty Well-Known Member

    Messages:
    836
    Please let's have sample workbook and if you have code to help you.
  8. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Below Obtained code from net..

    But don’t know how to configure it…

    Also attached sample Buyer Master which daily or randomly updated & need to same in 7 locations..


    Code (vb):

    Sub Save_To_Multi_Location_3(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.DisplayAlerts = False

        Dim thisPath As String
        Dim oneDrivePath As String

        thisPath = ThisWorkbook.Path & "\" & ThisWorkbook.Name
        oneDrivePath = "D:\BUYER MASTER\" & ThisWorkbook.Name

        ActiveWorkbook.SaveAs _
        Filename:=oneDrivePath

        Do
        Loop Until ThisWorkbook.Saved

        ActiveWorkbook.SaveAs _
        Filename:=thisPath

        Do
        Loop Until ThisWorkbook.Saved

        Application.DisplayAlerts = True
        Application.EnableEvents = True
        Application.ScreenUpdating = True
       
        Cancel = True

    End Sub
    please help

    Attached Files:

  9. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear All,

    Its now get amazing success after many struggle with above code.in general module.

    Code (vb):

    Sub Save_To_Multi_Location_3()

    Dim Cancel As Boolean
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.DisplayAlerts = False

        Dim thisPath As String '(C)
       Dim otherpath1 As String '(D)
       Dim otherpath2 As String '(E)
         Dim otherpath3 As String '(F)
         Dim otherpath4 As String '(SG-MAPPED Z:\BUYER MASTER)
           Dim otherpath5 As String '(INSTPRG-Y:\BUYER MASTER)
           Dim otherpath6 As String '(INSTPRG-Y:\SOFTWARES\BUYER MASTER)
           
       

        thisPath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
        otherpath1 = "D:\BUYER MASTER\" & ActiveWorkbook.Name
        otherpath2 = "E:\BUYER MASTER\" & ActiveWorkbook.Name
        otherpath3 = "F:\BUYER MASTER\" & ActiveWorkbook.Name
    otherpath4 = "Z:\BUYER MASTER\" & ActiveWorkbook.Name
    otherpath5 = "Y:\BUYER MASTER\" & ActiveWorkbook.Name
    otherpath6 = "Y:\SOFTWARES\BUYER MASTER\" & ActiveWorkbook.Name


        ActiveWorkbook.SaveAs Filename:=otherpath1
        ActiveWorkbook.SaveAs Filename:=otherpath2
        ActiveWorkbook.SaveAs Filename:=otherpath3
        ActiveWorkbook.SaveAs Filename:=otherpath4
        ActiveWorkbook.SaveAs Filename:=otherpath5
        ActiveWorkbook.SaveAs Filename:=otherpath6

        Do
        Loop Until ActiveWorkbook.Saved

        ActiveWorkbook.SaveAs _
        Filename:=thisPath

        Do
        Loop Until ActiveWorkbook.Saved

        Application.DisplayAlerts = True
        Application.EnableEvents = True
        Application.ScreenUpdating = True
       
        Cancel = True

    End Sub

     
    Many thanks to all ..for just study this matter...

    May be your all's concepts towards solutions are working here...this site is amazing environment...magical environment...

    Because this site force & encourage us to resolve twisting situations by self..

    again thanks to you all...

    Regards,
    Chirag Raval
  10. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear All,

    if this file is closed ...how to copy that file on 6 locations? without open it
    or open in background (hidden mode) & copy to 6 locations..?

    please help.

    Regards,

    Chirag Raval
  11. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear Sir,

    Main file is in "C" drive, folder name "Buyer Master", file name "SUITING-BUYER MASTER.xlsx"

    If it already open , code do it's work, if closed ...open it in background & do its works..

    if error...on like 3rd destination, Resume for next....& at after process finish display message how many location success...how many not...& why not ?with..reason

    hope someone could understand. There...
    Regards,

    Chirag Raval
  12. Monty

    Monty Well-Known Member

    Messages:
    836
    Hello..

    Happy to see you resolved it.

    For your latest queston...Just check before saving workbook to differnt locations...its better you check if the workbook open...
  13. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear Sir,

    thanks for your reply..& motivate ...

    I will try my best to struggle with it & revert back ..

    thanks again for you all for support..

    Reagrds,
    Chirag Raval

Share This Page