Activate a workbook on OneDrive

[tucsondonpepe]

Good afternoon,

I want to activate an Excel file that resides on OneDrive. This code opens the file.

Sub Copy_Data_From_Excel_File_On_OneDrive()

Set objLogExcel = CreateObject("Excel.Application")
objLogExcel.Visible = True

Set wkbStockTransTable = objLogExcel.Workbooks.Open _
(FileName:="Sign in to your Microsoft account")

Workbooks("9-2022 Summer Season Games Won (A).xlsx").Activate

Sheets("Sheet1").Select
Range("A1").Select

Columns("A:I").Copy

Sheets("Sheet2").Select
With ActiveSheet.Columns("A:I")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With

End Sub

The Set .... is being changed when I post this. This is what it looks like

The file opens but the "Workbooks("9-2022 Summer Season Games Won (A).xlsx").Activate" line produces the Run-time error '9': subscript out of range.

 

[Anthony47]

The name of the workbook seems to be "9-2022 Summer Season^GGames Won (A).xlsx"; but you can refer it as wkbStockTransTable, don't forgetting targetting the new Excel session
Thus, for example:

objLogExcel.Workbooks("9-2022 Summer Season^GGames Won (A).xlsx").Activate

objLogExcel.wkbStockTransTable.Activate

objLogExcel.wkbStockTransTable.Sheets(1).Range("A1:B1")

 

[tucsondonpepe]

Anthony,

Thank you for the information. I will be testing it in my macros soon.

Can you provide a link so I can study how to interact with the OneDrive?

Thanks.

Joe

 

[Anthony47]

Can you provide a link so I can study how to interact with the OneDrive?

I'm sorry, but don't have any suggestion for you (and indeed the code I suggested is not peculiar to OneDrive)