none
Excel VBA ADODB.connection: problem reading data readonly and not to activate/pop-up/edit source file RRS feed

  • Question

  • Case:

      I am writing a VBA script in MS Excel 2013 (in Windows 7 32bit) to access a closed excel workbook file (source) in a shared folder in the network from another excel workbook file (target) using ADODB to just read and get some rows of data in read-only mode in the background without opening/activating the source file, that is without popping open the source file to the foreground, or denying access to other users, or corrupt/edit the source file.


    Problems:

    1. When the source file is put in a shared folder that the accessing user has the write permission to it, the source file is accessed in write and edit mode, though the “mode” is set to “read” in the connection string.
    2. When the source file is put in a shared folder that the accessing user has just read permission to it, although the source file seems to be accessed in read-only, if the owner (the local user who has shared the folder) tries to open the file while the connection state is open, an alert stating “SourceFile.xlsx is locked for editing by ‘another user’.” is show and prompted to open in read-only mode.
    3. When the source file is put in a shared folder that the accessing user has just read privilege to it, and the source file is already opened by another user, although the source file seems to be properly accessed in read-only mode but the source file pops-up and is actually opened in foreground and its workbook's sheets are activated on top of the accessing/target workbook (that contains the VBA code.)

    Test: I am providing the simplified, but fully commented, VBA code in the accessing/target file in the following in which it reads data from a Sourcefile.xlsx in the network:

     

    Public Sub ADODBTEST()
    
        'the paths to a shared file in local network pcs
        Dim szSourceFile As String
        'you might want to comment/uncomment one of the following szSourceFile paths for testing purposes
        szSourceFile = "\\NetworkPC\READONLYACCESS\SourceFile.xlsx" 'A source file shared in a folder giving read-only access
        'szSourceFile = "\\NetworkPC\WRITEACCESS\SourceFile.xlsx" 'A source file shared in a folder giving write access
    
       'the connection string that sets the Mode=Read
        Dim szConnect As String
        szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & szSourceFile & ";" & _
                    "Mode=""Read"";" & _
                    "Extended Properties=""Excel 12.0;HDR=No;"";"
    
        'creating the rsCon connection object
        Dim rsCon As Object
        Set rsCon = CreateObject("ADODB.Connection")
        'opening a connection to the SourceFile.xlsx through szConnect connection string
    '***THE LINE WHERE ALL THE PROBLEMS HAPPEN: might be a good idea to set a breakpoint here
        rsCon.Open szConnect
    
    'THE REST OF THE CODE IS MOSTLY FOR OUTPUT DEMONSTRATION
        'SQL code needed to read data from SourceFile.xlsx
        Dim szSQL As String
        szSQL = "SELECT * FROM A1:A1;"
        
        'creating rsData object
        Dim rsData As Object
        Set rsData = CreateObject("ADODB.Recordset")
        
        'opening and reading data according to szSQL and rsCon
        rsData.Open szSQL, rsCon, 0, 1, 1
    
        'Outputing some data for more clarification
        Dim szData As String
        szData = rsData.Fields(0).Value
        
        'in case the source file pops-up in foreground and is activated
        'if VBA does not reference ThisWorkbook.Worksheets(1) the read data
        'unwantedly is copied to the source file that is activated
        ThisWorkbook.Worksheets(1).Cells(1, 1).Value = "Connection String (Mode=Read): " & rsCon.ConnectionString
        ThisWorkbook.Worksheets(1).Cells(2, 1).Value = szData
    
        'cleaning the connection and data
        rsData.Close
        Set rsData = Nothing
        rsCon.Close
        Set rsCon = Nothing
        
    End Sub
    

    Debug:

    You could set a breakpoint on rsCon.Open szConnect and as the code executes it, the above mentioned problems are observed according to the settings.

    I have also tested this in MS Excel 2007.

     

     


    Tuesday, August 25, 2015 3:53 AM