• 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...

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

Enter URL (path) into Excel Cell and not into VBA Code

Prakash M

New Member
Hello,

I have below source of code which copies files from Shared drive and pastes to Online SharePoint.

But every time, we need to change the path location in VBA code i.e. FromPath and ToPath (because of multiple folders)

Is there a way where users can simple enter the path location in excel spreadsheet, rather than going to Visual basic Window.

For "FromPath" - Enter path location in B1 Cell and for "ToPath" enter path location in B2 cell

Code:
Sub Rectangle4_Click()

  

    Dim fso As Object

    Dim FromPath As String

    Dim ToPath As String



    FromPath = "\\pu4\\"



    ToPath = "\Shared Documents\2020"



    If Right(FromPath, 1) = "\" Then



        FromPath = Left(FromPath, Len(FromPath) - 1)



    End If



    If Right(ToPath, 1) = "\" Then



        ToPath = Left(ToPath, Len(ToPath) - 1)



    End If



    Set fso = CreateObject("scripting.filesystemobject")



    If fso.FolderExists(FromPath) = False Then



        MsgBox FromPath & " Path doesn't exist"



        Exit Sub



    End If



    fso.CopyFolder Source:=FromPath, Destination:=ToPath



MsgBox " Hi you can find the files in " & ToPath



End Sub

Thanks,
Prakash
 
Last edited by a moderator:
Use Code tag to nest your vba code. I did it for you this time.

As for your question, just use cell value instead of hard coded string.

Ex:
Code:
FromPath = Sheets("YourSheetName").Range("B1").Value
 
Hi,

I update the VBA code as below but getting a debug at below line as Run Time Error 76. Path not found:

Code:
fso.CopyFolder Source:=FromPath, Destination:=ToPath
____________________________________

Code:
Sub Rectangle4_Click()
    
    Dim fso As Object
    Dim FromPath As String
    Dim ToPath As String

    FromPath = Sheets("Copy to SharePoint").Range("C3").Value

    ToPath = Sheets("Copy to SharePoint").Range("C7").Value

    If Right(FromPath, 1) = "\" Then

        FromPath = Left(FromPath, Len(FromPath) - 1)

    End If

    If Right(ToPath, 1) = "\" Then

        ToPath = Left(ToPath, Len(ToPath) - 1)

    End If

    Set fso = CreateObject("scripting.filesystemobject")

    If fso.FolderExists(FromPath) = False Then

        MsgBox FromPath & " Path doesn't exist"

        Exit Sub

    End If

    fso.CopyFolder Source:=FromPath, Destination:=ToPath

MsgBox " Hi you can find the files in " & ToPath

End Sub
 
@ Chihiro,

I was almost about to close this thread, but I have a new requirement now for the same.

This macro was copying the files to SharePoint from Shared Drive as per range mentioned in VBA.

I'm attaching excel file for your reference.

In the attached spreadsheet I've applied a drop down in cell B11, H11 an and J11. Upon selecting the required drop down from the available list, the path for "FromPath" and "ToPath" is getting updated in cell C3 and C7. Later I used to click on "COPY TO SHAREPOINT" button, which then runs the macro to copy the files to SharePoint.

This is like, if I need to copy "A" supplier files to SharePoint, I've to select "A" from the supplier name drop down. Similarly to copy "B" supplier files to SharePoint, I've to again select "B" supplier from drop down (this is one-by-one activity)

Now the requirement is to copy random supplier files to SharePoint.

For e.g. - if I want to copy files for supplier C, D, E and F from shared drive to SharePoint, I should have an option to select C, D, E and F and copy only that files to this respective folders on SharePoint at one go, rather than copying one by one.

UPDATE SUPPLIER NAME button is used to update any new supplier name created to Shared Drive, same gets updated in M column in spreadsheet

Note : I manually copy Outlook emails (because there is no uniqueness) to respective supplier name folders on Shared Drive.
My Shared Folder are - BackUp of Suppliers > A, B, C, D, E......... (supplier name folders) > 2018, 2019 and 2020 folders.

This year emails will be copied to 2020 folder.
 

Attachments

  • Emails to Sharepoint_v1.xlsm
    22.8 KB · Views: 0
Back
Top