Скопируйте значения из столбца S из многих файлов Excel в папку и вставьте в специальный файл vb.net

     Sub CopyData(ToFile As String, ToSheet As String, ToCol As String, FromFolder As String, FromSheet As String, FromCol As String)
        Dim Xl As New Microsoft.Office.Interop.Excel.Application
        Dim dWorkBook As Workbook
        Try
            Xl.Workbooks.Open(ToFile)
            dWorkBook = Xl.Workbooks(FileIO.FileSystem.GetName(ToFile))
        Catch ex As Exception
            MessageBox.Show(ex.Message & vbCrLf & "file not found or bad format or access error")
            Xl.Application.Quit()
            Xl.Quit()
            Exit Sub
        End Try
        Dim dSheet As Worksheet
        Try
            dSheet = dWorkBook.Sheets(ToSheet)
        Catch ex As Exception
            MessageBox.Show(ex.Message & vbCrLf & "sheet not found or bad name 'ToSheet'")
            Xl.Application.Quit()
            Xl.Quit()
            Exit Sub
        End Try
        If IO.Directory.Exists(FromFolder) = False Then
            MessageBox.Show("Bad path 'FromFolder'" & vbCrLf & FromFolder)
            Xl.Application.Quit()
            Xl.Quit()
            Exit Sub
        End If
        Dim sfiles As String() = IO.Directory.GetFiles(FromFolder, "*.xlsx", SearchOption.TopDirectoryOnly)
        If sfiles.Count = 0 Then
            MessageBox.Show("no excel files '*.xlsx' in directory 'FromFolder'" & vbCrLf & FromFolder)
            Xl.Quit()
            Exit Sub
        End If

        Dim ErrMsg As String = "Error list" & vbCrLf
        Dim faild As Integer = 0
        For Each X As String In sfiles
            Dim tmpWorkBook As _Workbook
            Try
                Xl.Workbooks.Open(X)
                tmpWorkBook = Xl.Workbooks(FileIO.FileSystem.GetName(X))
            Catch ex As Exception
                ErrMsg &= "bad format or access error " & X & vbCrLf
                faild += 1
                GoTo 1
            End Try
            Dim tmpSheet As _Worksheet
            Try
                tmpSheet = tmpWorkBook.Sheets(FromSheet)
            Catch ex As Exception
                ErrMsg &= "sheet not found or bad name  File: " & X & vbCrLf
                faild += 1
                tmpWorkBook.Close()
                GoTo 1
            End Try

            Dim ToRange As Range = dSheet.Range(ToCol & dSheet.Rows.Count).End(XlDirection.xlUp).Offset(1, 0)

            Dim FromRange As Range = tmpSheet.Range(FromCol & "1").End(XlDirection.xlDown)
            Dim tmpAddress As String = FromRange.Address
            FromRange = FromRange.End(XlDirection.xlDown)
            tmpAddress &= ":" & FromRange.Address
            If tmpAddress.EndsWith("1048576") Then
                ErrMsg &= "Column is empty :[ " & FromCol & " ]    File: " & X & vbCrLf
                tmpWorkBook.Close()
                faild += 1
                GoTo 1
            End If
            FromRange = tmpSheet.Range(tmpAddress)
            FromRange.Copy(ToRange)

            tmpWorkBook.Close()

1:
        Next


        dWorkBook.Close(True)
        Xl.Application.Quit()
        Xl.Quit()
        If ErrMsg.Length < 13 Then ErrMsg &= "No Errors" & vbCrLf
        ErrMsg = "Success :" & sfiles.Count - faild & vbCrLf & "Failed :" & faild & vbCrLf & vbCrLf & ErrMsg
        MessageBox.Show(ErrMsg)
    End Sub

использование

Это скопирует данные из столбца исходной таблицы A в столбец S листа1.

CopyData("c:\test.xlsx", "Sheet1", "S", "D:\folder", "sourceSheet", "A")