CODEWITHSUNDEEP
excelvbasharepoint
NewInvestor
NewInvestor

Reputation: 11

Open Excel file with changing name, from SharePoint

I am trying to open an Excel file on SharePoint and copy data into another spreadsheet.

The code worked when the files were saved on my laptop.

It does not work when I use http for the SharePoint location. I suspect it has to do with the Dir function.

The name of the Excel file will change with a different month each time I run the macro.

Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook
Dim FolderPath As String
Dim file As String
Set currentworkbook = ThisWorkbook

FolderPath = ("C:\Users\XXXXXX\Desktop\XX\")
file = Dir("C:\Users\XXXXXX\Desktop\XX\" & "WAS*.xls")
Do While file <> vbNullString
    Set sourceworkbook = Workbooks.Open(FolderPath & file)
    file = Dir()
Loop
sourceworkbook.Sheets("ABC").Range("A1:A15").Copy
currentworkbook.Sheets("END").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
sourceworkbook.Close SaveChanges:=False

Application.ScreenUpdating = True

Upvotes: 0

Views: 207

Answers (1)

Herberts
Herberts

Reputation: 68

I don't know if in your environment you have the necessary kind of authentication, but have you tried mapping the Sharepoint folder location to one of your network drives? Then you can you can access your file just as it was in any other folder.

Upvotes: 1

Related Questions