Integrating Excel Data From SharePoint To Access


Need to link a file in Sharepoint to Access for on-going importing…It’s not as easy as it sounds! If you want to link Sharepoint files to Access for on-going automatic updates, this nugget of code will do it! enjoy!

Private Sub Command0_Click()

Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean Dim lngCount As Long Dim objExcel As Object, objWorkbook As Object Dim colWorksheets As Collection Dim strPathFile As String Dim strPassword As String Dim datetoday As Date datetoday = Date MsgBox ("This takes about 10-15 seconds...wait for the finished message") ' Establish an EXCEL application object On Error Resume Next Set objExcel = GetObject(, "Excel.Application") If Err.Number <> 0 Then Set objExcel = CreateObject("Excel.Application") blnEXCEL = True End If Err.Clear On Error GoTo 0 ' Change this next line to True if the first row in EXCEL worksheet ' has field names blnHasFieldNames = False ' these are the current sharepoint file paths, select the one relevant to this database 'after importing, 2 queries are required - one to get just the data, a second to do a crosstab of the data. 'there is also 1 form created to show the current data ' use the actual path and filename of the file. disk or sharepoint ' code developed by Rivercity Technology Services (Jeff Shirley) strPathFile = "http://yoursite/Sites/sample/folder1/sample.xlsx" ' Replace passwordtext with the real password; ' if there is no password, replace it with vbNullString constant ' (e.g., strPassword = vbNullString) 'strPassword = "passwordtext" strPassword = vbNullString blnReadOnly = True ' open EXCEL file in read-only mode ' Open the EXCEL file and read the worksheet names into a collection Set colWorksheets = New Collection Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , _ strPassword) For lngCount = 1 To objWorkbook.Worksheets.Count colWorksheets.Add objWorkbook.Worksheets(lngCount).Name Next lngCount ' Close the EXCEL file without saving the file, and clean up the EXCEL objects objWorkbook.Close False Set objWorkbook = Nothing If blnEXCEL = True Then objExcel.Quit Set objExcel = Nothing ' Import the data from each worksheet into a separate table For lngCount = colWorksheets.Count To 1 Step -1 'MsgBox ("tbl" & colWorksheets(lngCount)) use this line for debugging to see what worksheet is being called 'comment out the docmd.deleteobject line first time running as there is no table to delete. then reenable it If IsTable("tbl" & colWorksheets(lngCount)) = True Then DoCmd.DeleteObject acTable, "tbl" & colWorksheets(lngCount) DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _ "tbl" & colWorksheets(lngCount), strPathFile, blnHasFieldNames, _ colWorksheets(lngCount) & "$" Else 'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _ ' "tbl" & colWorksheets(lngCount), strPathFile, blnHasFieldNames, _ ' colWorksheets(lngCount) & "$" DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _ "tbl" & colWorksheets(lngCount), strPathFile, blnHasFieldNames, _ colWorksheets(lngCount) & "$" End If Next lngCount ' Delete the collection Set colWorksheets = Nothing strsql = "INSERT INTO tblcogimportdates (COGImportDate) VALUES ('" & datetoday & "' );" DoCmd.SetWarnings False DoCmd.runsql strsql DoCmd.SetWarnings True MsgBox ("The data is imported.") End Sub

Related Posts

Access 2016 Relationships

Short blog today, this is a great read on relationships in Access 2016: Access 2016 Relationships Thanks to Microsoft for this one!

Microsoft Access Union Query from Hell. Skill Level: Demi-God

The worst query I ever had to write which powers a master report for the database user.  Here it is.  I don't think I can make it smaller, there were too many weird rules, aggregates, and translations in it.  And we needed to pull different columns based on criteria. ...