Import data into excel from another workbook

[ancientone]

Hi Guys, I am in need of some assistance here. I've been searching for code, but haven't found exactly what I needed. Basically I have a large amount of excel documents that hold individual PC information in each one and each file is named after the PC. I need to be able to pull in each sheet and have the data copied into the excel sheet for inventory purposes. I would prefer if I could pull in each PC files information while having the PC data file closed and just using the file name to target the file. The inventory excel sheet will need to pull in the information into certain cells then go to the next tab so I can pull in the next document or something close to that.

I've attached examples.
The PC file was actually a .csv but it wouldn't upload so I saved it as an excel file.

Inventory Workbook.xlsx is the Inventory workbook all the files will need to be pulled into.

PC1234.xlsv is an example of a PC which needs to be pulled in.

There will be multiple PC "sheets" IE: PC1235, PC1233 etc..

Thanks for your assistance.
Ancient

[ancientone]

Nothing?.,.

[jaslake]

Hi ancientone

I'm unable to see any relationship between the two files you posted. Column headings show no relationship; data in "Inventory Workbook.xlsx " shows no relationship to data in "PC1234.xlsv ".

Demonstrate a before and after...perhaps I can help.

John

[ancientone]

I guess I didn't show that well. Basically from the PC file I need that data imported from specific cells to specific cells in the inventory workbook. I have multiple PC files so I need a way to change the target file and pull the same information again just in a new tab. Does that make sense?

[jaslake]

Hi ancientone
There's many examples on the web to do what you describe; this is just an example.

Option Explicit
' Adapted from http://www.mrexcel.com/forum/showthread.php?t=298659
'Description: Combines all files in a folder to a master file
Sub MergeFiles()
    Dim ThisWB As String
    Dim shtDest As Worksheet
    Dim Filename As String, Wkb As Workbook
    Dim Dest As Range
    Dim MyPath As String
    Dim WS_Count As Long
    Dim I As Long
    ThisWB = ActiveWorkbook.Name
    MyPath = ActiveWorkbook.Path
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set shtDest = ActiveWorkbook.Sheets(1)
    Filename = Dir(MyPath & "\*.xlsx", vbNormal)
    If Len(Filename) = 0 Then Exit Sub
    If Filename Like "PC*" Then
        Do Until Filename = vbNullString
            If Not Filename = ThisWB Then
                Set Wkb = Workbooks.Open(Filename:=MyPath & "\" & Filename)
                ActiveSheet.UsedRange.Offset(1, 0).Copy
                Set Dest = shtDest.Range("A" & shtDest.Cells(Rows.Count, 1).End(xlUp).Row + 1)
                Dest.PasteSpecial
                Application.CutCopyMode = False    'Clear Clipboard
            End If
            Filename = Dir()
            Wkb.Close False
        Loop
    End If
    Range("A1").Select
    Columns.AutoFit
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

This particular code assumes all workbooks are in the same folder. It does open the source files but immediately closes the file after the extraction. Let me know if I can help in adapting it to your needs.

John

[ancientone]

Each "PC" file will have the data in the same cells that I need transferred to the "Main Workbook" however I need to transfer to specific cells in the workbook in one row then go down a row to insert the next PC information. Is that possible? I can code but I just need a basic template to follow.