VBA代码来整合.csv数据 [英] VBA Code to consolidate .csv data

问题描述

我正在尝试从多个csv文件中整合特定的日期范围。我想做的是选择此范围并将其粘贴到单独工作簿中的主工作表中。 .csv文件都安排在一个文件夹中，所有文件都有相同格式的1页。该范围可以是动态的，因此该代码将需要能够选择单元格下方的所有行，或者能够从较大的范围中删除空白行。我将不胜感激任何帮助。

I am trying to consolidate a specific range of date from many csv files. What I want to do is to select this range and paste it into a master sheet in a separate workbook. The .csv files are all arranged in one folder and all have 1 sheet in the same format. The range can be dynamic so this code will need to be able to select all the rows below a cell or be able to delete blank rows from a larger range. I would appreciate any help.

谢谢

推荐答案

有些指针如何解决方案：

Some pointers how to go about the solution:

首先，使用 FileSystemObject 。

Set fso = CreateObject("Scripting.FileSystemObject")
set fld = fso.GetFolder("path\to\my\folder")
For Each file in fld.Files
    If file.Name Like "*.csv" Then LoadFile file.Name
Next

声明 fso ， fld ， file as Object 。 LoadFile 将是您必须编写的一个函数，它处理一个文件。它将看起来像这样：

Declare fso, fld, file as Object. LoadFile will be a function you have to write, which processes a single file. It will look approximately like this:

Sub LoadFile(filename as String)
    dim buffer() as variant
    dim wb as workbook, ws as worksheet
    dim i as Long, beginrow as long, endrow as long

    Set wb = Workbooks.Open(filename)
    Set ws = wb.Worksheets(1)  ' .csv always has 1 worksheet
    buffer = ws.Range("A1:A10000")  ' put a sensible upper bound here
    for i = 1 to 10000
         ' replace (..first..) and (..last..) with your search interval
         if buffer(i, 1) <= (..first..) Then beginrow = i
         if buffer(i, 1) < (..last..) Then endrow=i
    next
    ' now beginrow and endrow hold the interval to cut
    ws.Cells(beginrow, 1).Resize(endrow-beginrow+1, column_count).Copy destination:=(... to be determined ..)
    wb.Close
End Sub

该函数打开文件;然后在第一列中搜索要复制的间隔;然后复制单元格并关闭文件。

The function opens the file; then searches the first column for the interval to copy; then copies the cells and closes the file.

代码不能按原样运行，但希望能给您正确的想法。

The code is not runnable as-is, but should hopefully give you the right ideas.

这篇关于VBA代码来整合.csv数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！