Need expertise help...

[kilosub]

Hi All,

How do I extract data from multiple files with a standard structure into a single sheet.

Raw input
C3 - text
C4 - text
C5 - text
C6 - text
C7 - text
C8 - numeric
C9 - numeric ( formatted as "000-000-0000")

Desire Output
B2 - extraction from file
B3 - extraction from file
and so on.

I have close to 300 files.

I hope I didn't confuse you all...

Thanks in advance.

Cheers,

Kilosub

 

[Sektor]

Oops, reverse thing:

Sub ExtractData()
    
    Dim j As Long
    Dim fso As New FileSystemObject, aFile As File
    Dim this As Worksheet, wkb As Workbook, sh As Worksheet
    
    j = 2
    
    Set this = ActiveSheet
    
    For Each aFile In fso.GetFolder(ThisWorkbook.Path).Files
        
        If fso.GetExtensionName(aFile.Name) Like "xls*" Then
        
            Set wkb = Workbooks.Open(aFile.Path)
            Set sh = wkb.Sheets(1)
            
            sh.Range("C3:C9").Copy
            this.Cells([B][COLOR="Red"]j, "B"[/COLOR][/B]).PasteSpecial xlPasteValues
            j = j + 1
           
            wkb.Close SaveChanges:=False
            
        End If
        
    Next

End Sub

 

[kilosub]

Oops! The reverse thing doesn't work.....

 

[Sektor]

It must work 'cause every time code finds Excel file, it advances row (variable j)...

 

[kilosub]

Confirm, its overlapping like the earlier result, after the modification. Not sure about "B" thing to me. We're close to the desire output. Below is the current and desire output.


Current Output
Name_1 | Name_2 | Name_3
Dept_1 | Dept_2 | Dept_3
Add_1a | Add_2a | Add_3a
Add_1b | Add_2b | Add_3b
Add_1c | Add_2c | Add_3c
12345 | 67890 | 20987
234567890 |435753267 |986246427


Desire Output
Name_1 | Dept_1 | Add_1a | Add_1b | Add_1c | 12345 | 234567890

Name_2 | Dept_2 | Add_2a | Add_2b | Add_2c | 67890 | 435753267

Name_3 | Dept_3 | Add_3a | Add_3b | Add_3c | 20987 | 986246427


Thanks.

 

[kilosub]

Hi,

Can anyone assist me to refine the coding? It is completed. The only thing the output require is by rows and not by column.

Thanks to sektor who provide the coding but with respect to sektor, I need to get these done by today.


Thanks in advance.