从多个Excel文件复制列的数据并将其粘贴到新的Excel文件中 [英] Copy column's data from multiple excel files and paste it in new excel file

问题描述

我想从文件夹中的excel文件中复制特定列，然后将所有值粘贴到新的excel工作表中.

I want to copy a specific column from the excel files located in a folder and paste all the values in a new excel sheet.

已完成-

1. 我能够遍历文件夹中的所有文件.
2. 我能够从特定列复制数据.

无法完成:

1. 无法粘贴复制的数据.
2. 我只想复制不同的值.
3. 我想复制列直到行在那里.好像有7个行，然后复制column的7个值.我的复制命令正在复制所有直到Excel工作表最后一行的值.

我的代码(VBScipt)-

My code (VBScipt)-

strPath="C:\Test"

Set objExcel= CreateObject("Excel.Application")
objExcel.Visible= True

Set objExcel2= CreateObject("Excel.Application")
objExcel2.Visible= True

objExcel2.Workbooks.open("C:\Test\New Folder\4.xlsx")

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder (strPath)

For Each objFile In objFolder.Files
If objFso.GetExtensionName(objFile.Path) = "xlsx" Then
    objExcel.Workbooks.Open(objFile.Path)

    Set Source=objExcel.Activeworkbook.Sheets(1).Columns("G")
    Source.Copy
    Set dest=objExcel2.Activeworkbook.Sheets(1).Columns("A")
    dest.Paste
    objExcel.Activeworkbook.save
    objExcel.Activeworkbook.close
    objExcel2.Activeworkbook.save
    objExcel2.Activeworkbook.close



End If

Next

推荐答案

我认为PasteSpecial将有助于在vb脚本中进行粘贴.最好在PasteSpecial中使用-4163参数，以确保仅粘贴值.下面的代码在Microsoft Visual Studio 2012中为我工作.添加注释仅是为了了解程序在代码中的位置.希望这会有所帮助.

I think PasteSpecial will help with the pasting in vb script. It is best to use the -4163 argument in PasteSpecial to ensure that only the values are pasted. The code below worked for me in Microsoft Visual Studio 2012. Added comments just to know where the program is in the code. Hope this helps.

Imports System.Data.OleDb
Imports System.IO
Imports System.Text

Public Class Form1
 Dim objCSV, objExcel, objSourceWorkbook, objDestWorkbook, objCSVWorkSheet, objXLSWorkSheet, srcCPUXrange, srcCPUYrange, srcMEMYrange, dstCPUXrange, dstCPUYrange, dstMEMYRange
   Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

 'Create and open source CSV object
    Label1.Text = "Setting Source"
    objCSV = CreateObject("Excel.Application")
    objCSV.Visible = True
    objSourceWorkbook = objCSV.Workbooks.Open("C:\SourceFile.csv")
    Label1.Text = "Source set"

    'Create and open destination Excel object
    Label1.Text = "Setting Destination"
    objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    objDestWorkbook = objExcel.Workbooks.Open("C:\DestinationFile.xlsx")
    Label1.Text = "Destination Set"

    'Select desired range from CSV file
    Label1.Text = "Copying Data"
    objCSVWorkSheet = objSourceWorkbook.Worksheets(1)
    objCSVWorkSheet.Activate()
    objSourceWorkbook.Worksheets(1).Range("A1").EntireColumn.Copy()
    Label1.Text = "Data Copied"

    'Paste in Excel workbook 
    Label1.Text = "Pasting Data"
    objXLSWorkSheet = objDestWorkbook.Worksheets(1)
    objXLSWorkSheet.Activate()
    objDestWorkbook.Worksheets(1).Range("A2").PasteSpecial(-4163)
    Label1.Text = "Data Pasted"    


  End Sub
End Class

这篇关于从多个Excel文件复制列的数据并将其粘贴到新的Excel文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！