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
