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

 

[kilosub]

Hi All,

Please advice can this be done as I am not sure at all... help

Thanks in advance.

 

[Sektor]

You want all cells from source file to be in one cell in target? And I assume you want to extract just numeric data. Right?

 

[kilosub]

Hi Sektor,

Thanks for your reply.

I need to pull the raw data which is from c3 to c9 and place into single sheet begin from B2 to B9. Next record will be on C2 to C9 and until all files data is extract out.

The data format as below:
C3 - text
C4 - text
C5 - text
C6 - text
C7 - text
C8 - numeric
C9 - numeric ( formatted as "000-000-0000")


Hope is not confusing for you...


Thanks again.

 

[Sektor]

Looks for Excel files in folder where macro is located. You can change path by altering ThisWorkbook.Path.

P.S. Set reference: Tools -> References -> Microsoft Scripting Runtime

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(2, j).PasteSpecial xlPasteValues
           
            wkb.Close SaveChanges:=False
            
        End If
        
    Next

End Sub

 

[kilosub]

Run the macro and error message pop up as below:

"fso As New FileSystemObject" - compile error, user define type not define.

btw, all the files are in one folder call d:\data, will that be ok for extraction?


Thanks.

 

[Sektor]

Did you CAREFULLY read my post? Read P.S.
And change this:

fso.GetFolder("[B]D:\data\[/B]").Files

 

[kilosub]

Sorry, my mistake.

Ok. I have made the amendment to it and run good but the extraction output is same as the raw data formatand overlapping existing data. Errgh, my fault on way of explanation.., sorry. Can you do it as below:

Raw Data from --- > Desire Output
C3 --> B2
C4 --> C2
C5 --> D2
C6 --> E2
C7 --> F2
C8 --> G2
C9 --> H2

Next new record at row B3, B4, B5 and so on


Sorry again for my careless mistake

Thanks a lot

 

[Sektor]

Oops, missed addition...

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(2, j).PasteSpecial xlPasteValues
            [B][COLOR="Red"]j = j + 1[/COLOR][/B]
           
            wkb.Close SaveChanges:=False
            
        End If
        
    Next

End Sub

 

[kilosub]

Ok.. extraction went well will dummy testing. can all the data be in row to row as the current situation the data are column to column


Thanks!!