Extracting data in the same cell locations from multiple excel files into one single excel file

I have a bunch of excel files, each file contain a contact name, company name, phone number and email address. I would like to extract those information (contact name, company name, phone number and email address) from all the individual excel files sitting in a file folder and put them into one excel file called Customers under 4 headings (contact name, company name, phone number, email address).

All the information are located in the same cell locations for all the individual excel files.

How do I extract them based on those cell locations? and export them to one excel file called Customers?

I have tried excel get Data command but no success. I have limited knowledge of coding but it doesn't look like I have any non coding solution for this.

Yes, there is a low code/no code solution with PowerQuery (aka “Get Data”):

1. Select Data > Get Data > From File > From Folder.

2. Select the folder containing the files you want to combine.

3. A list of the files in the folder appears in the <Folder path> dialog box. Check that all the files you want are there.

4. For further steps choose Combine > Combine & Load.

5. In the Combine Files dialog box: select a file to use as sample data used to create the queries (in the Sample File box)

I am sure you are not the first with this question, so you may want to ask your favorite search engine for further details.

For example, there is:

https://support.microsoft.com/en-us/office/import-data-from-a-folder-with-multiple-files-power-query-94b8023c-2e66-4f6b-8c78-6a00041c90e4

assuming the range to copy for each file is B4 to B7
save the sub in the Customers workbook

Sub CopyValuesFromFiles()
    Dim sourceFolder As String
    Dim sourceFiles As Object
    Dim sourceFile As Object
    Dim wbSource As Workbook
    Dim wsDestination As Worksheet
    Dim destinationRow As Long
    
Application.ScreenUpdating = False
Application.DisplayAlerts = False

    ' Set the path to the source folder modify accordingly
    sourceFolder = "C:\Your\Source\Folder\Path"
    
    ' Set the destination worksheet modify sheet name accordingly
    Set wsDestination = ThisWorkbook.Worksheets("Customers")
    
    ' Initialize the destination row
    destinationRow = 2
    
    ' Create a FileSystemObject to work with files in the folder
    Set sourceFiles = CreateObject("Scripting.FileSystemObject").GetFolder(sourceFolder).Files
    
    ' Loop through each file in the folder
    For Each sourceFile In sourceFiles
        ' Check if the file is an Excel file
        If sourceFile.Name Like "*.xls*" Then
            ' Open the source workbook
            Set wbSource = Workbooks.Open(sourceFile.Path)
            
            ' Copy the values from B4 to B7
            wbSource.Worksheets(1).Range("B4:B7").Copy
            
            ' Paste the values to the destination worksheet
            wsDestination.Range("A" & destinationRow).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
            
            ' Update the destination row for the next set of values
            destinationRow = destinationRow + 1
            
            ' Close the source workbook without saving changes
            wbSource.Close SaveChanges:=False
        End If
    Next sourceFile
    
    ' Clear the clipboard
    Application.CutCopyMode = False
    
    ' Display a message when the copying is complete
    MsgBox "Copying customer information from files complete."

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

This can be done by using openpyxl. The documentation is pretty good. Be sure to get it a good read.

import os
import openpyxl

# The directory with all the spreadsheets
# All files assumed to be only spreadsheets
DIR = r"C:\Users\Todd Bonzalez\Desktop\Spreadsheets"

# The details from the spreadsheet will be stored in a list of dicts
records = []

# Iterate over every file in the directory, load the spreadsheet, record the 
# values and append to the records list

for filename in os.listdir(DIR):
    filepath = os.path.join(DIR, filename)

    wb = openpyxl.load_workbook(filepath)
    ws = wb[wb.sheetnames[0]] # Get the first sheet
    
    record = {}
    # key = column name in the output spreadsheet
    record["Contact name"] = ws["B4"].value
    record["Company name"] = ws["B5"].value
    record["Phone number"] = ws["B6"].value
    record["Email address"] = ws["B7"].value
    records.append(record)

# Create a new spreadsheet
wb = openpyxl.Workbook()
ws = wb.active

# Write header row
ws.append(tuple(record))

# Write records
for record in records:
    ws.append(tuple(record.values()))

# Save
wb.save("combined.xlsx")

# Launch the new spreadsheet
os.startfile("combined.xlsx")

You can do this without any Power Query or VBA or other code using the INDIRECT function. This function takes a string and returns the value that string would have if you entered it as a cell reference.

Steps would be:

1. Obtain a list of files (including paths) you want to extract from. On Windows you select them all in the file explorer, hold shift and right-click, then select "Copy as path". (On other operating systems I would suggest using the ls command.)
2. Put this list into an Excel spreadsheet and separate the paths into column A and the filenames into column B. (A formula could do this part as well.)
3. Then in C1 write a formula like: =INDIRECT("'"&A1&"["&B1&"]Sheet1'!$B$4")