Hello,

I use the following code to create a connection to another workbook.
But what if I want the user to be able to choose which workbook he or she needs to connect to?

I want "strDBPath = "P:\SE\AVK\C. Projektmodell\Pärmen\APL\APL.xlsx" to be equal to the file the user chooses form a filedialog.

Anyone know if this is possible?


Thanks!

Public DBCONT As Object

Public Function connectSS()
    Set DBCONT = CreateObject("ADODB.Connection")
    Dim strDBPath As String
    '********IMPORTANT********
    'Update the below to represent your database path
    strDBPath = "P:\SE\AVK\C. Projektmodell\Pärmen\APL\APL.xlsx"
    Dim sConn As String
    sConn = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBPath & _
                        ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
    On Error GoTo Error
    DBCONT.Open sConn
    DBCONT.cursorlocation = 3
    
    Exit Function

Error:
    Call closeSS
    MsgBox "Kan inte koppla upp mot servern! - Säkerställ att du är uppkopplad mot P:// servern. "
End Function


Public Function closeSS()
    On Error Resume Next
    DBCONT.Close
    Set DBCONT = Nothing
    On Error GoTo 0
End Function

Function SanitizeSQL(ByVal sqlstr As String) As String
    SanitizeSQL = Replace(sqlstr, "'", "''")
End Function