adodb connection quirks

[vientito]

One part of my macro script will access another excel file on a shared server via ADODB jet engine. Everything seems to be working smoothly, except one small quirk. I can't seem to find any documentation of this so I am posting here to see if someone has come across this and found a solution.

When the target file, the excel file on shared server, is not open for editing, my script will access it and transfer the necessary data over to my macro script. However, if it is ever opened by someone at the time of me opening an ADODB connection to it, my macro excel session will open the target file in as "readonly" file. In other words, my macro script will lose its focus and a new file is activated.

This behaviour is largely unexpected and it causes my macro to fail.

ConnectionString = ""
            ConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & configF & ";" & _
            "Extended Properties=""Excel 12.0;HDR=YES"";"
      
        With Connection
            
            If (.State And adStateClosed) = adStateClosed Then
                .ConnectionString = ConnectionString
                .ConnectionTimeout = 1
                .Mode = adModeRead
                .Open     <---------------------- target file will pop up over my macro script session here
            Else
            
                Debug.Print "Connection still opened to Excel file"
            End If
            
            
            
        End With

Does someone know if there is any option or tricks that I can set in order that my macro script session will NEVER try to open the target file in its session if someone has that excel target file opened somewhere???

 

[ranman256]

It shouldnt matter is someone is in it or not, you wanted it open anyway, so read-only shouldnt matter.
If your script is loosing 'focus', make the next step after the open, to get back to the script wb, workbooks(main).activeate to get back to your code.

 

[VoG]

Cross posted adodb connection quirks

Please read Excelguru Help Site - A message to forum cross posters