Consulting

Results 1 to 2 of 2

Thread: Not Working : VBA : Create folder and access files from One Drive

  1. #1
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location

    Not Working : VBA : Create folder and access files from One Drive

    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!
    Attached Files Attached Files
    Last edited by anish.ms; 04-11-2021 at 12:09 PM.

  2. #2
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Currently I have hardcoded like below and replaced
    ThisWorkbook.Path
    with
    ODLocation
    ODLocation = Environ("USERPROFILE") & "\Company Name\Central Backup - One Drive - IA Database\Action Plan Tracker\FY 2020-21 Q4"

Posting Permissions

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