Transferring Data from multiple workbooks

Objective - extracting data from multiple workbooks (5 in total); pasting the data into a new workbook.

Problem/Issue: 1) After running the below VBA code it's able to copy data from all the 5 workbooks but while pasting it's pasting data for only one of them. 2) Pop-up window for Clipboard is full. I've written a code to clear the clipboard but it doesn't seem to function as I still get the pop-up window.

VBA Code:

Sub LoopThroughDirectory()

Dim MyFile As String
Dim erow

Dim MyPath As String

MyPath = "Directory path"

MyFile = Dir(MyPath)

Do While Len(MyFile) > 0
If MyFile = "filename.xlsb" Then
End If

       Workbooks.Open (MyPath & MyFile)
       Range("A3:CP10000").Copy
       ActiveWorkbook.Close

'calculating the empty row

 erow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row 

a = ActiveWorkbook.Name
b = ActiveSheet.Name

Worksheets("Raw Data").Paste Range("A2")

Application.CutCopyMode = False ' for clearing clipboard

MyFile = Dir

Loop

End Sub

I tried two other commands below as well, but they seem to just return no data at all.

ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow + 1, 1), Cells(erow + 1, 30)) `pasting the data`
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A2")`pasting the data`

Update. Here is the current code:

Sub LoopThroughDirectory()

Dim MyFile As String
Dim erow As Long
Dim MyPath As String
Dim wb As Workbook

MyPath = "C:\Users\username\Downloads\PROJECTS\Project Name\Input file\"
MyFile = Dir(MyPath)

Do While Len(MyFile) > 0
    If InStr(MyFile, "post_level.xlsb") > 0 Then

        Set wb = Workbooks.Open(MyPath & MyFile)
        Range("A3:CP10000").Copy

        'calculating the empty row
         erow = ThisWorkbook.Worksheets("Raw Data").Cells(Rows.Count, "A").End(xlUp).Row

        ThisWorkbook.Worksheets("Raw Data").Paste (ThisWorkbook.Worksheets("Raw Data").Range("A" & erow + 2))

        Application.DisplayAlerts = False
        wb.Close False
        Application.DisplayAlerts = True
    End If
    MyFile = Dir
Loop

    ActiveWindow.Zoom = 90

End Sub

Update2.

Sub LoopThroughDirectory()

Dim MyFile As String
Dim erow As Long
Dim MyPath As String
Dim wb As Workbook

MyPath = "C:\Users\username\Downloads\PROJECTS\ProjectNameFolder\SubFolder\MainFolder\Input file"
MyFile = Dir("C:\Users\username\Downloads\PROJECTS\ProjectNameFolder\SubFolder\MainFolder\Input file\*.*")

Do While Len(MyFile) > 0
    If InStr(MyFile, ".csv") > 0 Then

        Set wb = Workbooks.Open(MyPath & MyFile)
        Range("A3:CP10000").Copy

        'calculating the empty row
         erow = ThisWorkbook.Worksheets("Raw Data").Cells(Rows.Count, "A").End(xlUp).Row

        ThisWorkbook.Worksheets("Raw Data").Paste (ThisWorkbook.Worksheets("Raw Data").Range("A" & erow + 2))

        Application.DisplayAlerts = False
        wb.Close False
        Application.DisplayAlerts = True
    End If
    MyFile = Dir
Loop

End Sub

Answers


I hope I can help... There are multiple errors in your code, and I am not sure if I fixed them the way you'd want.

It would be useful to mention just one main mistake. You cannot have these 2 lines together:

If MyFile = "filename.xlsb" Then
End If

Between these lines you must put every procedure that you want to do IF he If condition is met. In the original case, if there was a file named "filename.xlsb", nothing would have happened, as you immediately closed the code block...

Try something similar to the following code. It worked for me to import data from all the files in the directory C:\Temp\ which have the extension of .xlsb

Sub LoopThroughDirectory()

Dim MyFile As String
Dim erow As Long
Dim MyPath As String
Dim wb As Workbook

MyPath = "C:\Temp\"
MyFile = Dir(MyPath)

Do While Len(MyFile) > 0
    If InStr(MyFile, ".xlsb") > 0 Then

        Set wb = Workbooks.Open(MyPath & MyFile)
        Range("A3:CP10000").Copy

        'calculating the empty row
         erow = ThisWorkbook.Worksheets("Raw Data").Cells(Rows.Count, "A").End(xlUp).Row

        ThisWorkbook.Worksheets("Raw Data").Paste (ThisWorkbook.Worksheets("Raw Data").Range("A" & erow + 2))

        Application.DisplayAlerts = False
        wb.Close False
        Application.DisplayAlerts = True
    End If
    MyFile = Dir
Loop

End Sub

Need Your Help

Map an array of indexes to an array of coordinates

python numpy matplotlib scikit-image

I'm playing a bit with scikit-image marching cubes algorithm. Here is a simplified version of the example given in the docs.

Can I trigger a webcontrol panel's OnInit event without a page?

panel initialization web-controls

I am working with someone else's code. The code was originally designed so that data would dynamically create controls (and sub-controls of those controls...) on the OnInit event on numerous web co...