anish.ms
04-11-2021, 11:59 AM
Dear Experts,
Request your help in the following issue-
I have a code below which works well when accessed from a local drive but it's not working when accessed from a central backup located in One Drive. Request your support in solving this problem.
The purpose of this tracker is to store/access files quickly which are saved against each line item through a FileDialogFilePicker.
The OneDrive location is "https://companyname-my.sharepoint.com/personal/central_bkp_companyname_com/Documents/Central BKP/Action Plan Tracker/FY 2020-21 Q4" and it varies in each quarter
Even if the excel file is accessed from following location in the local drive (OneDrive Sync location), FileDialogFilePicker locates to the above network location.
C:\Users\anish.ms\Company Name\Central Backup - One Drive - IA Database\Action Plan Tracker\FY 2020-21 Q4
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
ay = Target.Row
ax = Target.Column
With ActiveSheet
Select Case ax
Case 23
If ay = 1 Then Exit Sub
If Target.Value = Empty Then Exit Sub
If .Cells(ay, "A") <> Empty Then
chk_a = .Cells(ay, "A")
chk_b = .Cells(ay, "B")
aps = Application.PathSeparator
RootDir = ThisWorkbook.Path & aps & "Follow-up Audit Files"
isRootDir = GetAttr(RootDir)
If isRootDir <> 16 Then MkDir (RootDir)
VDir = ThisWorkbook.Path & aps & "Follow-up Audit Files" & aps & chk_b
isVDir = GetAttr(VDir)
If isVDir <> 16 Then MkDir (VDir)
UCN = ThisWorkbook.Path & aps & "Follow-up Audit Files" & aps & chk_b & aps & chk_a
isUCN = GetAttr(UCN)
If isUCN <> 16 Then MkDir (UCN)
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.InitialFileName = UCN
.Show
End With
End If
End Select
End With
End Sub
Thanks!
Request your help in the following issue-
I have a code below which works well when accessed from a local drive but it's not working when accessed from a central backup located in One Drive. Request your support in solving this problem.
The purpose of this tracker is to store/access files quickly which are saved against each line item through a FileDialogFilePicker.
The OneDrive location is "https://companyname-my.sharepoint.com/personal/central_bkp_companyname_com/Documents/Central BKP/Action Plan Tracker/FY 2020-21 Q4" and it varies in each quarter
Even if the excel file is accessed from following location in the local drive (OneDrive Sync location), FileDialogFilePicker locates to the above network location.
C:\Users\anish.ms\Company Name\Central Backup - One Drive - IA Database\Action Plan Tracker\FY 2020-21 Q4
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
ay = Target.Row
ax = Target.Column
With ActiveSheet
Select Case ax
Case 23
If ay = 1 Then Exit Sub
If Target.Value = Empty Then Exit Sub
If .Cells(ay, "A") <> Empty Then
chk_a = .Cells(ay, "A")
chk_b = .Cells(ay, "B")
aps = Application.PathSeparator
RootDir = ThisWorkbook.Path & aps & "Follow-up Audit Files"
isRootDir = GetAttr(RootDir)
If isRootDir <> 16 Then MkDir (RootDir)
VDir = ThisWorkbook.Path & aps & "Follow-up Audit Files" & aps & chk_b
isVDir = GetAttr(VDir)
If isVDir <> 16 Then MkDir (VDir)
UCN = ThisWorkbook.Path & aps & "Follow-up Audit Files" & aps & chk_b & aps & chk_a
isUCN = GetAttr(UCN)
If isUCN <> 16 Then MkDir (UCN)
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.InitialFileName = UCN
.Show
End With
End If
End Select
End With
End Sub
Thanks!