VBA Import data from xlsx file on sharepoint

[floggingmolly]

I have a spreadsheet on sharepoint and I need to import the data from the ALL sheet into a worksheet on my computer called ALIGNMENT. I tried a code I found online but it didn't work. Any help would be greatly appreciated. I would like for it to import the data from the sharepoint file then close that file once it's done.

 

[Norie]

Can you post the code you tried and tell us how it didn't work?

 

[floggingmolly]

Option Explicit

Public Sub OpenWebXLS()
' *************************************************
' Define Workbook and Worksheet Variables
' *************************************************
Dim wkbMyWorkbook As Workbook
Dim wkbWebWorkbook As Workbook
Dim wksWebWorkSheet As Worksheet

Set wkbMyWorkbook = ActiveWorkbook

' *************************************************
' Open The Web Workbook
' *************************************************
Workbooks.Open ("http://mysharepointsite.mysheet.xls")

' *************************************************
' Set the Web Workbook and Worksheet Variables
' *************************************************
Set wkbWebWorkbook = ActiveWorkbook
Set wksWebWorkSheet = ActiveSheet

' *************************************************
' Copy The Web Worksheet To My Workbook and Rename
' *************************************************
wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets(Sheets.Count)
wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "Alignment"

' *************************************************
' Close the Web Workbook
' *************************************************
wkbMyWorkbook.Activate
wkbWebWorkbook.Close

End Sub
[\code]

The lines of code below give an error. I'm not sure if it can be changed to import the data to the sheet I run the code from? 

wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets(Sheets.Count)
wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "MyNewWebSheet"

 

[Norie]

Are the real path/urls for the files pretty long?

 

[floggingmolly]

Are the real path/urls for the files pretty long?

The path isn't real long. Its like http://www.mysharepoint.com/mycompany/mydepartment

 

[floggingmolly]

The path isn't real long. Its like http://www.mysharepoint.com/mycompany/mydepartment

I got it to work. Thanks you for responding though.

 

[Norie]

No problem.

I work with Sharepoint regularly and the only time I've really had problems has been when the path/url was ridiculously long.

That was usually a company issue really, not a Sharepoint one.

 

[floggingmolly]

I'm getting an error at this line of code: wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets(Sheets.Count)
It opens the sheet from the sharepoint site, but then I get an error at this point. Any suggestions?

 

[floggingmolly]

No problem.

I work with Sharepoint regularly and the only time I've really had problems has been when the path/url was ridiculously long.

That was usually a company issue really, not a Sharepoint one.

I thought I had it working but I get an error on this line of code wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets(Sheets.Count) The sheet opens from sharepoint but it wont copy and save it. Any suggestions?

 

[ringleaderuk]

This line wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets(Sheets.Count) needs to change to
wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets(wkbMyWorkbook.Sheets.Count)