adodb connection quirks

vientito

New Member
Joined
Apr 25, 2013
Messages
28
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.

Code:
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???
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,173
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,297
Messages
5,836,479
Members
430,434
Latest member
whatabout

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top