Excel backup on network drive

aliaslamy2k

Active Member
Joined
Sep 15, 2009
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
Dear All,

I have created a workbook which will be used by multiple users in network drive. I am using below VBA code for a backup but this creates a back for any one user, because all other users are having different path for same network drive. E.G. D:\ , Z:\ , M:\ , L:\ H:\ ...but all users are accessing same network drive with different path name.Please advise to modify the below code so that I should get back up of my workbook from all users?


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ThisWorkbook.SaveCopyAs "X:\BACK UP\BACK UP" & ThisWorkbook.Name
End Sub

Kind Regards,
AB:confused:
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Use UNC instead:

Code:
Private Declare Function SetCurrentDirectoryA _
    Lib "kernel32" ( _
    ByVal lpPathName As String) _
As Long
'Use fChDirUNC Like ChDir
Public Function fChDirUNC(ByVal szPathToSet As String) As Long
    fChDirUNC = SetCurrentDirectoryA(szPathToSet)
End Function

if fChDirUNC returns 0, it didn't find the path.
 
Upvote 0
Code:
Private Sub Btn_Click()
 
If Not theNetwork(" \\server\folder\ ") = 0 Then

    Set h = CreateObject("Word.Application")

    helper.Documents.Open theNetwork(" \\server\folder\ ") & _ 
         "path\path]path\Filename.doc"

    h.Visible = True
 
End If
 
End Sub

I don't understand how to implement this.

I gave it a lot, but I just can't figure out how changing the default directory helps me access a file when I can't use the path in the filename argument.
 
Upvote 0
theNetwork:

Code:
Private Function theNetwork(ByVal thePath As String)
 
theNetwork = SetCurrentDirectoryA(thePath)
 
End Function


Declaraction at the top of the module:

Code:
Private Declare Function SetCurrentDirectoryA Lib "kernel32" (ByVal thePath As String) As Long
 
Upvote 0
Code:
If If Not theNetwork(" \\server\folder\ ") = 0 Then

   chdir " \\network\path\ "
 
    Set helper = CreateObject("Word.Application")
    helper.Documents.Open "Documentation.doc"

    helper.Visible = True
 
End If

Also tried it like this, with no success.
 
Upvote 0
See shg's solution here, post #12; that should work for you.
His enumerates all the drives on your machine and loops through them, then chooses the one that fits your path, if any do.
 
Upvote 0

Forum statistics

Threads
1,224,584
Messages
6,179,691
Members
452,938
Latest member
babeneker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top