PDA

View Full Version : Continual Import of Excel data into Sharepoint list



davidboutche
08-10-2009, 07:00 AM
I have two excel spreadsheets. The first one holds my primary data.

The second spreadsheet is based on external data in sharpepoint are basically the same.

I'd like to use a macro that will copy the information from the first spreadsheet onto the next available line on the second sheet.

I have used the following which does this, but it places it outside of the 'square' that is controlled by sharepoint.

Sub copyalltoend()

Dim LastRow As Object
Dim ws As Worksheet


Workbooks.Open Filename:="V:/pndbook.xls"
Do

Rows("2:2").Select
Selection.Cut
Windows("Sharepnd.xls").Activate

Set ws = ActiveWorkbook.Worksheets("CAHW8F11")
Set LastRow = ws.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Insert
Windows("pndbook.xls").Activate
Selection.Delete Shift:=xlUp
Windows("pndbook.xls").Activate
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
Windows("pndbook.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close


End Sub

Sharepoint has also made it's own unique key field in the first columb that stops the data matching up.

Does anyone know of a solution?

The reason I'm doing this is so that multiple users can work on the data at anyone time as I believe if more than one person tries to open the spreadsheet in excel they will all be read only apart from one user. I'm open to any better solutions.

davidboutche
08-11-2009, 02:28 AM
The following code seemed to achieve what I wanted to:

Sub importdata()


Workbooks.Open Filename:="V:/pndbook.xls"
Range("A2:F2").Select
Do
Selection.Cut
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(0, 5)).Select

Windows("sharebook.xls").Activate
Range("b1").Select
Do
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)
ActiveSheet.Paste
Windows("pndbook.xls").Activate
Loop Until IsEmpty(ActiveCell)

Windows("pndbook.xls").Activate
ActiveWorkbook.Save
ActiveWindow.Close
ActiveSheet.ListObjects("List1").UpdateChanges xlListConflictDialog
End Sub