Importing from sharepoint using VBA almost there (I think)

[praktikant]

Hi all,

I've been working on a excel workbook, where I want to import a workbook from a specific excel file, that is located in a sharepointfolder, into another excel file (named Import.xlsm).

I'm able to import the workbook, if source-file is on my C-drive and .xls format.

Private Sub CommandButton1_Click()

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "C:\Users\John\Desktop\NewFolder\"
fileName = Dir(directory & "*.xl??")

Do While fileName <> ""

Workbook.Open (directory & fileName)

For Each sheet In Workbooks(fileName).Worksheets
total = Workbooks("import.xlsm").Worksheets.Count
Workbooks(fileName).Worksheets(sheet.Name).Copy _
after:=Workbooks("import.xlsm").Worksheets(total)

Next sheet

Workbooks(fileName).Close

fileName = Dir()

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

I've inserted the code that works and marked the path red. My problem is at when I try to use the path from sharepoint: http://infonet/Workspace/FU/Shares%20documents/Maps/GanttCharts.xlsm

It doesn't work when it's from the sharepoint (I've tried to change the format from .xslm to xls)

Can anyone help me?

The objective is that the user of Import.xlsm, can click on the macro button, and import the Ganttcharts-sheets from sharepoint without getting access to the source-file.

 

[praktikant]

I'm not even sure, if it's possible to import from sharepoint with VBA