VBA Copy data from file stored at onedrive

[hrayani]

Hello Friends,

I am using the below code to open onedrive file and copy data from that file and paste into the excel file

Sub call_file_from_cloud()

    On Error GoTo ErrMsg
   
    Workbooks.Open Filename:= _
        "https://d.docs.live.net/xxxxxxxxxxxxxxxx/Documents/One%20Drive.xlsx", ReadOnly:=True
           
    Cells.Select
    Selection.Copy
   
    Windows("call macro from.xlsm").Activate
   
    Range("A1").PasteSpecial Paste:=xlPasteValues
   
    Windows("One Drive.xlsx").Activate
    Worksheets("Sheet2").Select
    Range("A1").Copy
   
    Windows("call macro from.xlsm").Activate
    Range("B1").PasteSpecial Paste:=xlPasteValues
   
    Windows("One Drive.xlsx").Activate
    Application.CutCopyMode = False
   
    Windows("One Drive.xlsx").Close
   
Exit Sub

ErrMsg:
    MsgBox ("Please sign in to your microsoft account to call data from CLOUD."), , "SIGN IN REQUIRED"

End Sub

The code requires a sign in to access the file.
The data is in my account and the other person is not able to open the file from his account.
So in order for the other person to access the data (open the file) he needs my id & pw to open the file.

Is there a way to bypass the sign in method. Like some way that would not require to sign in

If not then is there a a way that I can open the file URL of that file and copy data from that web browser in which the file is present and copy data from there and paste it in excel.

Regards,

Humayun

 

[PritishS]

Hi,
My approach to your problem would be-
1. Download that excel file from your onedrive/sharepoint to user's desktop. You can get many code by little google "macro to download excel file from one onedrive".
This way you can set your user id and password inside your code.
2. After downloading that file to user's desktop, open it and then do the copy pasting job to your file.
3. Close that file you have downloaded and delete it from user's desktop ( using kill command)

Hope this give you some idea.

Regards,
PritishS

 

[hrayani]

Hi Pritish,

Thanks for the reply & sorry for coming back late.....

I tried searching on google for that a little.... but did not find anything yet. Will search more.

Meanwhile, please let me know that can’t we set the user ID & PW in the current code at the start & set the code to sign out of that account at the end of the code. In said case I will not have to download the file and then delete that file.
If its possible then can you please help me in that. I don’t know that much of VBA.

Regards,

Humayun

 

[hrayani]

Please if anyone can add the username & password at the start of the code in post # 1 to sign in and sign out at the end of the code