Results 1 to 1 of 1
  1. #1
    Join Date
    Nov 2013
    Posts
    1

    Question Unanswered: Download Excel file from Sharepoint w/o mapped drive

    Windows 7
    Office 2010



    Hello, I am giant n00b and have a very elementary understanding of VB and Access in general. I have built an Access database for my job that basically keeps track of all of our annual training and makes it presentable for highers to see. To keep my database up to date with the latest information I have it currently linked to 3 excel files that I pull from elsewhere on a regular basis. I want to be able to distribute this to the highers so they will have the reports on demand.

    My original plan was to use our SharePoint page to host the excel files as lists. BUT I quickly learned that SharePoint lists have limitation and two of my files are in the 12,000 lines realm. SO I thought I would just have my user form download the files and import them to tables in the database. Below is the code I used and it works with files elsewhere on the web but not with my SharePoint page.

    My SharePoint page uses smart card verification with a pin and I believe this to be what is causing the problem. I don't believe I am allowed to map to the SharePoint but I may be overlooking something as I am even newer to SharePoint than Access.

    Other ideas I have come up with are to use Access to open IE to my download URL(code below) but the problem with that is forcing the user to save the file in a location that Access knows where it is at. Also thought about however not ideal to make a distro list in Outlook and just email the files to users and have Access scan the users inbox for e-mails with my subject line but I have yet to figure out exactly how to make that the e-mail code to work.


    Code:
    Option Explicit
    '************************************************DOWNLOAD CODE*****************************************************
    
    Private Declare Function URLDownloadToFile Lib "urlmon" _
       Alias "URLDownloadToFileA" _
      (ByVal pCaller As Long, _
       ByVal szURL As String, _
       ByVal szFileName As String, _
       ByVal dwReserved As Long, _
       ByVal lpfnCB As Long) As Long
       
    Private Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" _
       Alias "DeleteUrlCacheEntryA" _
      (ByVal lpszUrlName As String) As Long
       
    Private Const ERROR_SUCCESS As Long = 0
    Private Const BINDF_GETNEWESTVERSION As Long = &H10
    Private Const INTERNET_FLAG_RELOAD As Long = &H80000000
    '************************************************DOWNLOAD VIA URL CODE*****************************************
    
    Private Sub Command2_Click()
    Call NavigateToURL("https://sharepointurl/tblAlphaRoster.xlsx")
    
    End Sub
    Private Sub Form_Load()
       
    End Sub
       
    Private Sub Command1_Click()
    '******************************************DOWNLOAD NO PROMPT CODE****************************************
       Dim sSourceUrl As String
       Dim sLocalFile As String
       Dim hfile As Long
       
     sSourceUrl = "https://sharepointurl/tblAlphaRoster.xlsx"
       sLocalFile = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & "DBDL1.xlsx"
       
       Dim temp As String
         
       If DownloadFile(sSourceUrl, sLocalFile) Then
       
          hfile = FreeFile
          Open sLocalFile For Input As #hfile
          Close #hfile
          
       End If
       
         sSourceUrl = "https://sharepointurl/tblAlphaRoster.xlsx"
       sLocalFile = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & "DBDL1.xlsx"
       
       Call DeleteUrlCacheEntry(sSourceUrl)
       
       If DownloadFile(sSourceUrl, sLocalFile) = True Then
             hfile = FreeFile
             Open sLocalFile For Input As #hfile
                
             Close #hfile
       End If
    '************************************************IMPORT CODE*****************************************************
    
    'Code to import excel file to a table in the database
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "TestTable", Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & "DBDL1.xls", True
    Kill (Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & "DBDL1.xlsx")
    Last edited by USMCamp0811; 11-03-13 at 19:44.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •