Rivercity Technology Services LTD Logo
HomeAbout Us
Technology Services
Icon showing a support technician
IT Support Services
Cybersecurity Risk Management at one predictable flat rate.
Icon of light bulbs on a laptop screen
IT Consulting
Business optimization through the smart use of technology.
Icon showing a hand holding a phone
Business Phone Services
VoIP Telephone solutions from RCT. 
Icon showing a database and a cloud
Backups & Recovery
Cloud & On Premise - ready to recover!
Icon showing computer code on a monitor
Software Development
Web & Mobile App development
Icon showing website wireframes
Website Development & Hosting
Web design and full hosting & maintenance packages!
Icon showing an envelope being opened
Modern Email Management
Microsoft 365 email provisioning, security & management.
“You’re giving me the ‘it’s not you, it’s me’ routine? I invented ‘it’s not you, it’s me.’ Nobody tells me it’s them not me; if it’s anybody, it’s me.”
- George Costanza
Learning CenterContact Us
Book A Consultation
Illustration of a person writing a blog post with a pencil

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) www.rivercitytech.ca 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
Article Written by Jeff Shirley
Related Articles
Featured image
Your Guide to Managed Website Hosting
Creating a website is only the first step. Keeping it online and accessible to visitors 24/7 involves a lot of additional work. Hosting your site means choosing...
2022 - Copyright, All Rights Reserved
crossmenu