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

Jeff Shirley

Jeff Shirley

Founder & CEO
Jeff brings over 27 years of experience to the table, along with numerous awards, certifications, and real-world implementations.  His database solutions are currently running in industries including government, mining, agriculture, finance, education, science, research, non-profits, and healthcare businesses around North America and serve thousands of users day to day.  Jeff is a five-time MVP Award recipient for Microsoft Access, acknowledging contributions to community projects, evangelism, and educational outreach on Microsoft technologies.  Today there are less than 50 Access MVP’s worldwide. While Jeff focuses day to day on business management, IoT development, IT integrations and design, database architecture or other typical complex tasks, he also is a co-owner of Blue Heron Gardens, a commercial apiary running over 200 beehives.  Fresh honey is a small perk for many of our clients!

Mitch Redekopp

Mitch works with our clients day to day ensuring web development and IT projects are done to exceed customer expectations.  His background includes formal training in IT, Microsoft and CompTIA certifications, real-world business experience, and a true dedication to ensuring clients receive timely and professional support on their projects with Rivercity Technology Services.  Given the most difficult requests for IT solutions, Mitchell can assist our clients in finding a path to the best options which deliver results for our clients.  Mitchell is an avid soccer player and Manchester City fan, be sure to talk soccer with him when you can.