excel - How to combine information in two different cells when using ADODB to pull information from a closed workbook into a mat

I have a master workbook that I am trying to import data from a different target workbook using ADODB.

I am trying to combine the information from two cells in the target workbook and input the combined information into one cell on the master workbook.

I got the code from a youtube video from "WiseOwl" and have been messing with it to get it to suit my needs.

Any help is appreciated.

Option Explicit

Sub ImportDataFromFinishedTravelRequest() 
    Dim cn As ADODB.Connection 
    Dim file As FileDialog 
    Dim sItem As String 
    Dim GetFile As String 
    Dim rs As ADODB.Recordset 
    Dim unusedRow As Long
   
    'Determines the next empty row
    With Sheets("NEW TR Matrix")
        unusedRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    End With

    'Allows you to select which file to pull information from
    Set file = Application.FileDialog(msoFileDialogFilePicker)
    With file
        .Title = "Select a File"
        .AllowMultiSelect = False
        '.InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode: 
    GetFile = sItem 
    Set file = Nothing

    Sheet1.Range("A1").CurrentRegion.Offset(1, 0).Clear

    Set cn = New ADODB.Connection

    cn.ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & GetFile & ";" & _
    "Extended Properties='Excel 12.0 Xml;HDR=No';"

    cn.Open

        Set rs = New ADODB.Recordset
        rs.ActiveConnection = cn
        rs.Source = "SELECT * FROM [$J14:J14]" & "SELECT * FROM [$J15:J15]"
        rs.Open

        'Selects which cell in the mastersheet to copy info into
        Sheet1.Range("G" & unusedRow).CopyFromRecordset rs

        rs.Close

    cn.Close

End Sub

I have a master workbook that I am trying to import data from a different target workbook using ADODB.

I am trying to combine the information from two cells in the target workbook and input the combined information into one cell on the master workbook.

I got the code from a youtube video from "WiseOwl" and have been messing with it to get it to suit my needs.

Any help is appreciated.

Option Explicit

Sub ImportDataFromFinishedTravelRequest() 
    Dim cn As ADODB.Connection 
    Dim file As FileDialog 
    Dim sItem As String 
    Dim GetFile As String 
    Dim rs As ADODB.Recordset 
    Dim unusedRow As Long
   
    'Determines the next empty row
    With Sheets("NEW TR Matrix")
        unusedRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    End With

    'Allows you to select which file to pull information from
    Set file = Application.FileDialog(msoFileDialogFilePicker)
    With file
        .Title = "Select a File"
        .AllowMultiSelect = False
        '.InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode: 
    GetFile = sItem 
    Set file = Nothing

    Sheet1.Range("A1").CurrentRegion.Offset(1, 0).Clear

    Set cn = New ADODB.Connection

    cn.ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & GetFile & ";" & _
    "Extended Properties='Excel 12.0 Xml;HDR=No';"

    cn.Open

        Set rs = New ADODB.Recordset
        rs.ActiveConnection = cn
        rs.Source = "SELECT * FROM [$J14:J14]" & "SELECT * FROM [$J15:J15]"
        rs.Open

        'Selects which cell in the mastersheet to copy info into
        Sheet1.Range("G" & unusedRow).CopyFromRecordset rs

        rs.Close

    cn.Close

End Sub

• excel
• vba
• adodb

Share Improve this question Follow asked Jan 2 at 18:11 Teddy HannaTeddy Hanna 4111 silver badge66 bronze badges 1

• 1 "select * from [J14:J15]" then loop over the records and concatenate the values – Tim Williams Commented Jan 2 at 18:50

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

For example:

Sub TestReadCells()

    Dim oConn As New ADODB.Connection, sConn As String, strPath As String
    
    strPath = ThisWorkbook.FullName 'for example
    
    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & strPath & ";" & _
             "Extended Properties=""Excel 12.0 Xml;ReadOnly=True;HDR=No"";"
    
    oConn.Open sConn
    
    Sheet1.Range("A1").Value = ReadCell("J14", oConn) & " " & _
                               ReadCell("J15", oConn)
          
End Sub

'Get the value from a single cell on the first worksheet of the workbook
'  which `conn` is connected to
Function ReadCell(addr As String, conn As ADODB.Connection) As String
    Dim rs As ADODB.Recordset
    Set rs = conn.Execute("Select * from [$" & addr & ":" & addr & "]")
    If Not rs.EOF Then ReadCell = rs(0).Value
End Function

If the cells are contiguous, then it would likely be more efficient to open a single recordset and read the values from that recordset.