Data auto update from branch file to main file while closing to specific target (1 workboof to another workbook)

[Mehmud Khan]

Hi Team,

Good day!


Need your valuable support in VBA file.

I am preparing a file, wherein 2 file are maintained.
1st is for main office
2nd is for Branch Office

--

In the Branch office file, path/File/Sheet/Range is given in cell B1, B2,B3 and B4 by order.

Let me explain step by step.

Step1
Branch office file is update by various users on daily basis.

Step2
Main office file is maintained for centralized data purpose.

step3
after update the branch file, once it close then all data from range (A7:C37) should auto update in main file.

Step4
VBA should follow, Path/File/Sheet/Range from above listed cells

step5
Main file is pw protected and pw is "ABC123" to edit.

step6
Forget about duplicate, while closing file data of active sheet from range (A7:C37) should past to Range D5 (mentioned in B4).

step 7
after update, msg of "data centralized in master-file successfully" should pop-up

step8
In case, Current sheet (mentioned in B3) is not available in main file then pop msg "contact admin, no user sheet found"

step 9
in case of any other error, pop msg "failed to sync data, contact admin" pop msg

I hope I am able to explain.

I know there are to many conditions, but not in hurry.
Try if anybody is free.



Thanks and Regards,
-Khan

 

[Marc L]

Hi, according to your attachment a VBA event procedure to paste only to ThisWorkbook module of Branch workbook :​

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Const C = 16
        If [COUNTBLANK(B1:B4)] Then [B1:B4].SpecialCells(4).Select: MsgBox "Fill all Main File B1:B4 cells !", C: Exit Sub
        V = [A1:B4]
        If Dir(V(1, 2), C) <> "." Then X = 1 Else If Dir(V(1, 2) & V(2, 2)) = "" Then X = 2
    If IsEmpty(X) Then
        Application.ScreenUpdating = False
        Workbooks.Open V(1, 2) & V(2, 2), 0, , , "ABC123"
        Y = Evaluate("ISREF('[" & V(2, 2) & "]" & V(3, 2) & "'!" & V(4, 2) & ")")
     If IsError(Y) Then
        X = 3
     ElseIf Y = False Then
        X = 4
     Else
        With ActiveWorkbook.Sheets(V(3, 2))
            .Protect "ABCDE", , , , True
            .Range(V(4, 2)) = ActiveSheet.[A7:C37].Value
        End With
            Save
     End If
        ActiveWorkbook.Close Not X
        Application.ScreenUpdating = True
    End If
        If X Then Cells(X, 2).Select: MsgBox V(X, 1) & " not found !", C Else MsgBox "Main file updated", 64
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Mehmud Khan]

Hi Mark,

Thanks a lot for your reply.

I tried pasting the below codes in "Branch File" but unfortunately, in my all attempts I am getting below error;




As I don't have good knowledge in VBA so I am not be able to edit the coding as well.

appreciable and I'll be thankful if you check the codes once more.

Thanks and regards,
-Khan

 

[Marc L]

Hi Marc

As on my side :​

​

​

​

So maybe the path separator as mandatory last character is missing on yours …​

It's what may occur when separating the full filename in two cells instead of a single.​

You may Like it !​

​

 

[Mehmud Khan]

Dear Marc,

You are awesome....
You are brilliant....

I hope you can feel the biggg smile on face....

You are really Excellent...

The issue was, I missed "/" in the path in cell B1.

Now everything worked fine....


Only 1 last request sir,
Once you get time please try to do it....

If the "MainFile"; no pw to open ; file is protected to modify with same pw.

Thanks you so much for your time sir..

Regards,
-Khan

 

[Marc L]

I missed "/"

Not / but \ as path separator under Windows !​

​

Clearly elaborate your need as I can't understand what you are asking for …​

​

 

[Mehmud Khan]

Hi,
I hit like to all threads

let me do some experiments then I’ll ask more questions…

thanks and regards
-khan

 

[Marc L]

I hit like to all threads

Thanks !​

​

 

[Mehmud Khan]

Hi Marc,

Good day!

Once again need your help with a changes scenario.

All conditions remain same, only the data should paste as value instead of same format. (from Branch file to MainFile)

as always best regards,
-Khan

 

[Marc L]

So first in Branch cell B4 enter at least the correct destination range D5:F35 then try this mod :​

​

If IsError(Y) Then X = 3 Else If Y = False Then X = 4 Else Range(W) = ActiveSheet.[A7:C37].Value: Cancel = X: Save​

​

 

[Mehmud Khan]

Hi Marc,

Good Day!

Thanks you so much for you reply.

I understand below and tried with changed VBA but unfortunately no data is getting past in MainFile.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Const C = 16
        Cancel = True
        If [COUNTBLANK(B1:B4)] Then [B1:B4].SpecialCells(4).Select: MsgBox "Fill all Main File B1:B4 cells !", C: Exit Sub
        V = [A1:B4]
        If Dir(V(1, 2), C) <> "." Then X = 1 Else If Dir(V(1, 2) & V(2, 2)) = "" Then X = 2
    If IsEmpty(X) Then
        Application.ScreenUpdating = False
        Workbooks.Open V(1, 2) & V(2, 2), 0, , , "ABC123"
        W = "'[" & V(2, 2) & "]" & V(3, 2) & "'!" & V(4, 2)
        Y = Evaluate("ISREF(" & W & ")")


       If IsError(Y) Then X = 3 Else If Y = False Then X = 4 Else Range(W) = ActiveSheet.[A7:C37]: Cancel = X: Save


        ActiveWorkbook.Close Not X
        Application.ScreenUpdating = True
    End If
        If X Then Cells(X, 2).Select: MsgBox V(X, 1) & " not found !", C Else MsgBox "Main file updated", 64
End Sub

I request you to give your valuable feedback.

 

[Marc L]

Should work adding Value property like the post #10 now updated …​

​

 

[Mehmud Khan]

Hi Marc,

I have already taken your so much time....
Thanks a lot for each and every second...

I tried with post#10 VBA...
It copies as value, but unfortunately only cell "A7" are updating (in MainFile) instead of range "A7:C37"

Any feedback sir??

will be thankful for your solution.


Regards,
-Khan

 

[Marc L]

As it well works on my side so just well read & follow post #10 direction …​

​

 

[Mehmud Khan]

Hello Marc,

Thanks a lot for your support.

I checked post#10 and understand the target range. Everything good and file working excellent now.

Only one more support, once if you available then please check below request.


As Range[B1:B4] is mandatory to update in correct form. While closing file giving me error of blank cell [from range B1:B4] if anything not updated.
But at the same time excel is not allowing to close the file as well.

What I want, if cell is blank or file is facing any issue while updating the data in "MainFile" then excel should pop (which currently working) also user can be able to close the file.

I hope you understand.

Thanks in advance Mr Ninja!

Best Reagrds,
-Khan

 

[Marc L]

In this case just remove both Cancel = statements …​

​

 

[Mehmud Khan]

Hello Marc,

as always, need your assistance one more time in this project.

The main files, worksheet (mentioned in B3 cell) is always pw protected.

while updating the data from BranchFile to MainFile, vba should unprotect the sheet (pw= ABCDE” than paste the data. Then after paste, again protect the sheet with same pw.

thanks in advance sir…

best regards
-mehnud khan

 

[Marc L]

So I need to know which options are chosen when protecting the worksheet​

or if the cells are not locked so need to modify the VBA procedure …​

​

 

[Mehmud Khan]

greetings from Mumbai!

It will be a general lock…
I mean no special criteria is choosen while lock the sheet. The entire sheet will be protected, not specific range.

when BranchFile excel open the main file and go to sheet (mentioned in cell) then before paste VBA should unprotect the MainFile active sheet.

Thanks for your valuable time sir.

thanks and regards,

 

[Marc L]

As a reminder all such important informations must be in the initial post !​

The reason why post #2 is now updated …​

​