Anyway for Ado to Read Updated Data from a Read-Only Excel File Before Save? (Vba)

Is there a way for a temp file Excel Add-on to know if it's public read-only file has been updated since the end user has opened up Excel?

I would add an extra worksheet to your add in and use ExecuteExcel4Macro to check for version updates.

---

Public Sub CheckForUpdates()
    Const MasterPath As String = "C:\stackoverflow"
    Const MasterFileName As String = "MasterFile.xlam"
    Const WorkSheetName As String = "Settings"

    Dim Msg As String, Version As String
    Dim ButtonNumber As Long

    Version = getVersion(MasterPath, MasterFileName, WorkSheetName, 2, 2)

    With ThisWorkbook.Worksheets("Settings").Range("B2")
        If .Value <> Version Then
            Msg = getVersion(MasterPath, MasterFileName, WorkSheetName, 4, 2)
            ButtonNumber = IIf(getVersion(MasterPath, MasterFileName, WorkSheetName, 3, 2), vbCritical, vbInformation)
            MsgBox Msg, ButtonNumber, "Update Available"
        Else
            Application.OnTime Now + 4 / 24, "CheckForUpdates"
        End If

    End With

End Sub

Function getVersion(MasterPath As String, MasterFileName As String, WorkSheetName As String, RowNumber As Long, ColumnNumber As Long)
    If Right(MasterPath, 1) <> "\" Then MasterPath = MasterPath & "\"
    getVersion = ExecuteExcel4Macro("'" & MasterPath & "[" & MasterFileName & "]" & _
                                    WorkSheetName & "'!R" & RowNumber & "C" & ColumnNumber)
End Function

VBA - Create ADODB.Recordset from the contents of a spreadsheet

I had to install the MDAC to get the msado15.dll and once I had it I added a reference to it from (on Win7 64bit):

C:\Program Files (x86)\Common Files\System\ado\msado15.dll

Then I created a function to return an ADODB.Recordset object by passing in a sheet name that exists in the currently active workbook. Here's the code for any others if they need it, including a Test() Sub to see if it works:

Public Function RecordSetFromSheet(sheetName As String)

Dim rst As New ADODB.Recordset
Dim cnx As New ADODB.Connection
Dim cmd As New ADODB.Command

    'setup the connection
    '[HDR=Yes] means the Field names are in the first row
    With cnx
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source='" & ThisWorkbook.FullName & "'; " & "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
        .Open
    End With

    'setup the command
    Set cmd.ActiveConnection = cnx
    cmd.CommandType = adCmdText
    cmd.CommandText = "SELECT * FROM [" & sheetName & "$]"
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic

    'open the connection
    rst.Open cmd

    'disconnect the recordset
    Set rst.ActiveConnection = Nothing

    'cleanup
    If CBool(cmd.State And adStateOpen) = True Then
        Set cmd = Nothing
    End If

    If CBool(cnx.State And adStateOpen) = True Then cnx.Close
    Set cnx = Nothing

    '"return" the recordset object
    Set RecordSetFromSheet = rst

End Function

Public Sub Test()

Dim rstData As ADODB.Recordset
Set rstData = RecordSetFromSheet("Sheet1")

Sheets("Sheet2").Range("A1").CopyFromRecordset rstData

End Sub

The Sheet1 data:
Field1 Field2 Field3
Red A 1
Blue B 2
Green C 3

What should be copied to Sheet2:
Red A 1
Blue B 2
Green C 3

This is saving me a HUGE amount of time from querying against SQL every time I want to make a change and test it out...

--Robert

Is there a way to import an Excel spreadsheet as a recordset?

You can query the sheet by using SheetName$. (The dollar sign is required)

Dim db As ADODB.Connection, rs As ADODB.Recordset
Set db = New ADODB.Connection
Set rs = New ADODB.Recordset
ExelFileFullPath = "FilePath\filename.xlsx"
db.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & ExelFileFullPath & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;""")
SQL = "SELECT * FROM [SheetName$]"
rs.Open SQL, db, adOpenKeyset, adLockReadOnly

Your columns must have headers with Unique names to avoid issues.

---

Related Topics