Hi
I'm trying to connect to an external file called 'Employee List' and the worksheet is called 'All Staff Members' but it doesn't seem to connect?
I'm trying to connect to an external file called 'Employee List' and the worksheet is called 'All Staff Members' but it doesn't seem to connect?
VBA Code:
Option Explicit
'Add reference for Microsoft Activex Data Objects Library
Sub sbADO()
Dim sSQLQry As String
Dim sSQLSting As String
Dim ReturnArray
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String
DBPath = ThisWorkbook.FullName
'You can provide the full path of your external file as shown below
'DBPath = "C:\Users\me\DataNow\Home\Desktop\documents\projects\database\copy"
sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
Conn.Open sconnect
sSQLSting = "Select [ID], [Leave Reason], [Ops Director], [Forename], [Surname], [Date of birth], [Start Date],[Job Title] from [All Staff Members$] WHERE [ID] in ('" & _
Join(Application.Transpose(Range("A2:A50").Value), "','") & "')"
mrs.Open sSQLSting, Conn
'=>Load the Data into an array
'ReturnArray = mrs.GetRows
''OR''
'=>Paste the data into a sheet
ActiveSheet.Range("A2").CopyFromRecordset mrs
'Close Recordset
mrs.Close
'Close Connection
Conn.Close