首页 文章

使用VBA将文件夹中不同文件的数据复制到主表

提问于
浏览 次
0

我在主表上有以下代码，当我按下一个按钮时，它会通过我想要的文件夹并打开并关闭它应从中提取数据的工作表 .

这是我从主表上的按钮打开和关闭文件的代码 . 我需要帮助来编写空间的代码### CODE GOES GOES GOES GOES GOES GOES GOES GOES GOES GOES GOES GOES GOES GOES我一直在拔头发 .

Public Sub test()
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Dim Wb1 As Workbook, wb2 As Workbook
Path = "\\ttsnas02\user_mdocs$\tdf8273\Documents\Rob\External supplier timesheet\CSV Supplier Main\Inbox folder\"  'CHANGE PATH
Filename = Dir(Path & "*.xl??")

 Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
    Set wbk = Workbooks.Open(Path & Filename)
    '
    ' ###CODE GOES HERE
    '  
   wbk.Close True
   Filename = Dir

Loop
End Sub

你能帮我写一个代码吗？

“### CODE GOES HERE”的代码需要通过检查特定列中是否有值来从开始表行中获取数据 . 例如，如果L12中有数据，则在开始表中，它将复制主表a2 c2和e2中的J8 J9和L12 .

然后在开盘表中检查L13 . 如果有值，则将J8 J9和L13复制到a3 c3和e3 .

然后在打开的表格中检查L14 ......

直到L20

然后关闭打开的工作簿，它将打开文件夹中的下一个工作簿 . 检查同一个表：如果L12中有数据 . 然后在开始表中，它将复制J8 J9和L12放入下一个空行中的主表 .

这是供应商时间表

supplier timesheet

master sheet

vba excel-vba excel

1 回答

  • 0

    这应该做你想要的 . 代码用于master和打开索引1的第一个工作表 . 如果需要，更改索引：

    Public Sub test()
    Dim wksMaster As Worksheet
    Dim wks As Worksheet
    Dim rng As Range
    Dim i As Integer
    Dim wkb As Workbook
    Dim Filename As String
    Dim Path As String
    Dim Wb1 As Workbook, wb2 As Workbook

    Path = "\\ttsnas02\user_mdocs$\tdf8273\Documents\Rob\External supplier timesheet\CSV Supplier Main\Inbox folder\"  'CHANGE PATH
    Filename = Dir(Path & "*.xl??")

    ' bind the master worksheet to access it later on
    ' change index if needed
    Set wksMaster = ActiveWorkbook.Worksheets(1) ' or ThisWorkbook
    i = 2

    Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
        Set wkb = Workbooks.Open(Path & Filename)

        ' loop through range in worksheet with index 1 (the first)
        ' change index if needed
        With wkb.Worksheets(1)
            For Each rng In .Range("L12:L20")
                ' if there is a value in the cell
                If rng <> vbNullString Then
                    wksMaster.Range("A" & i) = .Range("J8")
                    wksMaster.Range("C" & i) = .Range("J9")
                    wksMaster.Range("E" & i) = rng
                    ' increment i
                    i = i + 1
                End If
            Next
        End With

        wkb.Close True
        Filename = Dir

    Loop

End Sub
    回复于

相关问题