Excel front end and Access back end on Sharepoint

Question

I have a VBA application that's built in Excel 2010 and uses Access 2010 as a back end to store all of the data. I need to move up to SharePoint to give users access to it but am starting to wonder if I'm going to have problems with this. If I put both files in a Document Library will that work? Or am I going to run into problems? Given that my Excel front end is fully developed and at this point I can't change it, are there other options for making this work other than just storing all of the data in Excel?

Answer 1

I am not entirely sure what you are trying to achieve, but you can just store the Excel and the Access file in a document library. Every user should have to download both files and execute the VBA code on their local machine. I am not aware of mechanism that would help you to link Access and execute VBA code and also show some Excel data based on this VBA code within SharePoint Web Library/View/Interface. SharePoint just do not have component that understands or executes VBA macros. The document library can be used to store documents and metadata for the documents. If you try to open the excel from the excel web viewer, it probably throw an error due to the fact your excel is macro enabled and the viewer does not support it.

Once I had to create excel report and display it directly in the excel web viewer and I had to write a C# program that would generate normal xlsx file with the already processed data and then upload it as plain xlsx so the excel web viewer can open it.

Answer 2

I found the best way to do this is to either have the users open it from SharePoint(pain) or just save the file on their computer. Then when Excel starts map a drive to the SharePoint site, transfer the Access database file from the map drive to the temp folder, and then delete the mapped drive. Then when changes are made just repeat the same process but copy the Access file to the mapped drive.

I should mention that in my environment users are infrequently updating this information so this works fine, however if users are frequently updating data this is not the best route.

Sub GetSharePointFile()
    Dim objNet as object
    Dim strDriveLetter as String
    Dim strSharePointDatabaseFolder as String
    Set objNet = CreateObject("WScript.Network")

    On Error GoTo AUTH_Connection:
    strDriveLetter = GetOpenDriveLetter
    strSharePointDatabaseFolder = "<SharePoint site>"
    objNet.MapNetworkDrive strDriveLetter, strSharePointDatabaseFolder

On Error GoTo ErrHandler:
    <code to copy file(s) from mapped drive to destination folder> 

    Exit Sub

AUTH_Connection:

    Dim xlApp As New Excel.Application
    Dim xlDoc As Workbook
    On Error GoTo ErrHandler:

    Set xlApp = CreateObject("Excel.Application")
    Set xlDoc = xlApp.Workbooks.Add
    ' Trying to upload the file below will force IE to open and prompt user for their Username and Password which will authenticate them
    xlDoc.SaveAs FileName:="<SharePointSite>", FileFormat:=xlWorkbookNormal, AddToMru:=False
    xlDoc.Close
    xlApp.Quit

    objNet.MapNetworkDrive strDriveLetter, strSharePointDatabaseFolder
    Resume Next
ErrHandler:
    MsgBox Err.Code, Err.Description
End Sub

Function GetOpenDriveLetter() As String
Dim colDisks As Variant
Dim objWMIService As Object
Dim objDisk As Variant

  'Get all used Drive-Letters
  Set objWMIService = GetObject("winmgmts:\\" & "." & "\root\cimv2")
  Set colDisks = objWMIService.ExecQuery("Select * from Win32_LogicalDisk")

  'Loop through all Drive-Letters
  For Each objDisk In colDisks
      For i = 67 To 90
          'If letter is in use store chr number as 'j' and exit loop
          If i = Asc(objDisk.deviceID) Then
              j = i
              Exit For
          'If not in use and the character value of i is not blank, then set and exit
          ElseIf i > j And Chr(i) <> "" Then
              GetOpenDriveLetter = Chr(i) & ":"
              Exit Function
          End If
      Next i
  Next objDisk
End Function