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

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,900
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,358
Messages
5,528,219
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top