Search code examples
excelvbaoledbfileserver

VBA connection to another workbook on a fileserver

I want to import data from one workbook to another and have done so with the help of the OLEDB driver. However it only works on my local machine. When I tried to test it in the actual production environment it failed to connect to the source file. Both the running file and the source file exist on a fileserver with authentication requirements, but I have read/write access, yet I'm unable to open the source file.

Everytime I try to fetch data from the excel workbook I get an error saying that the file I'm trying to connect to is already open and cannot be accessed. I know for certain that no one is using the source file. I've tried using different way of writing the filepath, and each variant gives me different error. ("\FILERP\..." as well as a drive letter "G:...")

Below you can find one of the string connections that I've tried without success.

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & filepath & ";" & _
                "User ID=" & userName & ";" & _
                "IMEX=1;" & _
                "Mode=Read;" & _
                "Extended Properties=""Excel 12.0 Xml;" & _
                "HDR=NO;"""

Some examples of errors that I get:

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & filepath & ";" & _
                "Extended Properties=""Excel 12.0 Xml;HDR=No;"""

Runtime error '-2147467259 (80004005)' "Can't update. The database or object is write protected."

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & filepath & ";" & _
                "Integrated Security=SSPI;" & _
                "Extended Properties=""Excel 12.0 Xml;HDR=No;"""

Runtime error '-2147217887 (80040e21)' "Multiple-step OLE DB operation generated errors. Check each OLE DB status value"

    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & filepath & ";" & _
                "User Id=" & userName & ";" & _
                "Password=****;" & _
                "Extended Properties=""Excel 12.0 Xml;HDR=No;"""

Runtime error '-2147217843 (80040e4d) "Can't start the program. The information file for workgroups is missing or is exclusively opened by another user."

Solution

  • After a long time of trying different things, the answer was actually super easy, and embarrassing.

    In my filepath I thought it was \FILEREP\ when it actually is \FILERP\

    So my lesson to all is, make sure you have the right file path before you start looking at other things! :)

    In the end all I needed was the standard connection string seen below!

    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & filepath & ";" & _
                "Extended Properties=""Excel 12.0 Xml;HDR=No;"""