Need help combining data from Sharepoint stored Excel sheets

[khorse69]

Hi,

Hoping anyone can give me some ideas on how to centralize excel data.
Basically we have assessment sheets that are stored using Sharepoint, each manager 'Checks out' their individual assessment sheets to complete their assessments and then 'check in' the document once the assessment is complete.

There are about 20 excel sheet assessments for each manager, and the data is dynamic and changes daily. I did test and was able to link locally on my PC but when I attempted to link the sharepoint had issues. My main problem is that the data is stored via Sharepoint, when I attempt to 'Link to the data source by creating a linked table' and link to the sharepoint stored data it gives me an error that the format is not compatible. This would be because our sharepoint link is 'https' format not 'http', I'm wandering if there is anyway around this?

I have seen the option to create a sharepoint list, but unfortunately I don't have admin rights to sharepoint, so I'm looking for a simple way to consolidate all the data into Access.

 

[Micron]

Can't tell if the 'assessment sheets' are Excel workbooks or Sharepoint lists. If the latter, I have no suggestions because of my limited use of SP. I can tell you that I have pushed Access data into Excel workbooks using Automation. The code first tested if the wb could be checked out and IF yes, checked it out, pushed the data, then checked it back in - if that helps. The point being, you can use automation to pull data from Excel into Access if need be, but maybe you'll get a better suggestion - one that solves the linking issue.

 

[khorse69]

Hi Micron, these are Excel Workbooks. That is exactly what I need, automation that checks out the workbooks pulls the data into access and then checks the work books back in. How do I go about setting up this automation?

 

[Micron]

I'm on the road for a few days and the conditions are not so good right now. Not sure how soon I can get into that. Google automation of you can't wait or maybe someone else can step up sooner.

 

[khorse69]

no worries Micron, I have been googling a lot, but i'm also happy to wait until your in a better position to post up the details.
No rush so take your time, and thanks again!

 

[Micron]

I used a form "frmWait" to keep the user informed of event progress. Db form tab view option seems to allow this form to maximize so MoveSize method was used to control size and position. What's here should allow you to check in/out a workbook on a Sharepoint site. The method of getting the data into Access would be up to you; TransferSpreadsheet ought to be the simplest, otherwise I think you're looking at having to select and copy spreadsheet cells and update an Access recordset. This has been modified in an attempt to suit your purpose, so it's not entirely tested. The check in/out code calls the Pause function.

Dim xlx As Object, xlw As Object
Dim blnCanCheckout As Boolean, blnWeCheckedOut As Boolean
Dim svWrkBookPath As String

On Error GoTo errHandler
svWrlBookPath = "path to workbook file"
DoCmd.OpenForm "frmWait"
With Forms!frmWait
   .lblmsg.Caption = "PUT YOUR MESSAGE HERE..."
   'size & position form (using db tabs option makes this form expand to full screen)
   .Move Left:=4500, Top:=2000, Width:=5300, Height:=3000
   .Repaint
End With

'open an Excel application...
Set xlx = CreateObject("Excel.Application")
xlx.Visible = False
'now test to see if we can check out the workbook (exlusive use)
blnCanCheckout = xlx.Workbooks.CanCheckOut(svWkBookPath)

If blnCanCheckout = False Then
  msg = "Put your message here that the workbook can't be checked out." & vbCrLf
  msg = msg & "Operation cancelled."
  MsgBox msg, vbOKOnly, "FILE NOT AVAILABLE"
  DoCmd.Close acForm, "frmWait"
  Set xlx = Nothing
  Set xlw = Nothing 
  Exit Sub
End If

If blnCanCheckout Then
  Set xlw = xlx.Workbooks.Open(svWkBookPath)
  xlx.Workbooks.CheckOut svWkBookPath
  blnWeCheckedOut = True
  Pause (2) 'allow time for server to check out workbook before doing anything with it
**now push/pull data or use transferspreadsheet method & change form labels to suit

With Forms!frmWait
  .lblMsg.Caption = "Workbook will now be saved, checked in and closed."
  .Repaint
End With
Pause 3
xlw.CheckIn svWkBookPath'check in the workbook
blnWeCheckedOut = False 'record that the workbook is no longer checked out
If CurrentProject.AllForms("frmWait").IsLoaded Then DoCmd.Close acForm, "frmWait", acSaveNo
   MsgBox "All done!"
End If

exitHere:
'next If block may not be req'd to reverse settings (done as a precaution anyway)
If blnWeCheckedOut = True Then 'error could route to here after check out
   xlw.CheckIn svWkBookPath 'check in the workbook
   blnWeCheckedOut = False
End If
If CurrentProject.AllForms("frmWait").IsLoaded Then DoCmd.Close acForm, "frmWait", acSaveNo
xlx.ScreenUpdating = True
xlx.DisplayAlerts = True
xlx.Quit 'if checkout unsucessful, file not opened; no need to close or check in
    
exitByPass:
Set xlw = Nothing
Set xlx = Nothing
    
Exit Sub

errHandler:
Select Case Err.Number
   Case 70, 1004, -2147417848, -2147417851 'automation errors of one sort or another
       msg = "Cannot  transfer data; another user has file open." & vbCrLf
       msg = msg & "or it cannot be checked out. Operation canceled."
       MsgBox msg 
       Resume exitHere
   Case 462
   'in rare case, you could close Excel while debugging code & before reaching the xlx
   'handling after exitHere, causing a continuous loop
       Resume exitByPass
   Case Else
       MsgBox "Error " & Err.Number & ": " & Err.Description
       Resume exitHere
End Select

Public Function Pause(intSecs As Integer)
Dim Start As Variant
Start = Timer
Do While Timer < Start + intSecs
    DoEvents
Loop
End Function

 

[khorse69]

thanks Micron, will try out the code and let you know how it goes.

Thanks Again really appreciate the help!

 

[Micron]

you're welcome. Hope it helps. Perhaps you noticed that the message about saving the workbook before checking in doesn't really apply in your case. I didn't catch that the code doesn't support that message after I edited it for you. I removed the saving part because it didn't seem to apply, but forgot to alter the sample message.
Good luck!