none
Excel .xlsm spreadsheet on the web with database connection RRS feed

  • Question

  • I have an Excel 2013 spreadsheet (.xlsm) with extensive formatting, formulas, and functionalities that depend on macros.  On my local computer is it connected to an Access database.  The spreadsheet is a front end for submitting values to the database by clicking a button and then retrieving previous submissions using a different button.  That works well.

    My goal now is to post this spreadsheet on the web and provide the link to it to users of my choice.  They should be able to open and use the file in terms of entering, submitting, and retrieving data, but not save it.  I need to know, in general, what process would be used to connect this Excel spreadsheet to a database (Access or otherwise) that will allow this functionality over the web.  Essentially I want to use the Excel spreadsheet (and the macros in it) as a front end similar to using a web form to interact with a database.  How can this be accomplished?

    Below is the code for what is on my personal computer.

    [code]

    Const TARGET_DB = "C:\Users\cim\Desktop\Accounting.accdb"

    Sub PushTableToAccess1()
    If Range("J2") = 5 Then Call MessageBoxNoSubmit
    If Range("J2") = 5 Then Exit Sub
        Dim cnn As ADODB.Connection
        Dim MyConn
        Dim rst As ADODB.Recordset
        Dim i As Long, j As Long
        Dim Rw As Long
       
        Sheets("EX1").Activate
        Rw = Range("P65536").End(xlUp).Row


        Set cnn = New ADODB.Connection
        MyConn = TARGET_DB
       
        With cnn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Open MyConn
        End With


        Set rst = New ADODB.Recordset
        rst.CursorLocation = adUseServer
        rst.Open Source:="Exercise1", ActiveConnection:=cnn, _
                 CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
                 Options:=adCmdTable
      
        For i = 4 To Rw
            rst.AddNew
            For j = 16 To 22
                rst(Cells(3, j).Value) = Cells(i, j).Value
            Next j
            rst.Update
        Next i
       
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing


    End Sub
    Sub DownloadRegion1()
    If Range("C7") = "No previous takes" Then Exit Sub
    Range("X4:AE8").ClearContents


        Dim cnn As ADODB.Connection
        Dim rst As ADODB.Recordset
        Dim fld As ADODB.Field
        Dim MyConn
        Dim i As Long
        Dim ShDest As Worksheet
        Dim sSQL As String
       
        Set ShDest = Sheets("EX1")


        sSQL = "SELECT * FROM Exercise1 WHERE [ID] ='" & ThisWorkbook.ActiveSheet.Range("P4").Value & "' AND [Take] = " & ThisWorkbook.ActiveSheet.Range("C7").Value
       
        Set cnn = New ADODB.Connection
        MyConn = TARGET_DB
       
        With cnn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Open MyConn
        End With


        Set rst = New ADODB.Recordset
        rst.CursorLocation = adUseServer
        rst.Open Source:=sSQL, ActiveConnection:=cnn, _
                 CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
                 Options:=adCmdText


        i = 0
        With Range("X3")
            For Each fld In rst.Fields
                .Offset(0, i).Value = fld.Name
                i = i + 1
            Next fld
        End With
        
        '
        Range("X4").CopyFromRecordset rst


        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing


    End Sub

    [/code]

    Monday, August 4, 2014 3:20 PM

Answers

  • Hi,

    According to your description, I’m afraid you are not able to achieve you goal on Excel Web App because the VBA code and local data connection will not be available (the cache data will be still available).

    >>

    My goal now is to post this spreadsheet on the web and provide the link to it to users of my choice.

     They should be able to open and use the file in terms of entering, submitting, and retrieving data

    <<

    Based on your requirement, I will recommend you to use Access Web App.

    How to: Create and customize a web app in Access 2013

    Please correct me if I have any misunderstanding on your requirement, hope it will help.

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 6, 2014 3:11 AM
    Moderator

All replies

  • Where do you want to upload? To your website and store in your server?

    Why not use Access Web app to replace the workbook as the front end?

    Tuesday, August 5, 2014 9:03 AM
  • Hi,

    According to your description, I’m afraid you are not able to achieve you goal on Excel Web App because the VBA code and local data connection will not be available (the cache data will be still available).

    >>

    My goal now is to post this spreadsheet on the web and provide the link to it to users of my choice.

     They should be able to open and use the file in terms of entering, submitting, and retrieving data

    <<

    Based on your requirement, I will recommend you to use Access Web App.

    How to: Create and customize a web app in Access 2013

    Please correct me if I have any misunderstanding on your requirement, hope it will help.

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 6, 2014 3:11 AM
    Moderator