Excel - Read data from another xls-file without copying contents

user_7822155 0
Viewed 181

Currently I have one source file called SRC with and index Cisome columns Cn. This file is indexed without duplicates on the first column and then there is data which I want to read from one of the columns depending on the situation. (The data is prices, dates, etc. for each index).

I have some amount of destination Excel-files (call them, A, B, C, etc.) in which I have to lookup the corresponding column for each matching index in my source SRC.

Currently I am using a named range and VLOOKUP to find the matching index and the corresponding data:

VLOOKUP($A6;price_data;3;FALSE)

This solution works, but my problem is the file size. Using a named range or even a direct reference to another file causes Excel to include the data from my SRC to every one of my destination files A, B, C, ...

Question itself: How to lookup data from a source file without increasing the size of my destination files?

Additional test with two files: A) still has the named range linked to another file and B) I deleted the named range, did not modify any equations. Then renamed both files to .zip, to check the contents.

File A contains a directory xl/externalLinks with a file externalLink1.xml which is about the same size as my original SRC.

File B does not contain the xl/externalLinks and the size is very much smaller. A = 1,2 MB and B = 25 kB.

excel
2 Answers

One option can be to create a vba function and link it with file open. It will get/update the values when the file is opened. you would get what you want but it would get a little slower to open file.

EDIT1

This is what I have done

  1. Created a file named it main with one column containing IDs
  2. Created another file, named it details and created two columns in it product id and product name.
  3. Saved both
  4. Checked the file sizes
  5. Opened both and inserted vlookup in the main file that is fetching product names for IDs present in the main file from details file.
  6. Saved the main file and closed both
  7. Checked the file size again

Only the main file has different size

user_104981110

Find below the VBA solution, you can link this function with workbook open and it will update your data

I have updated the vba_lookup as well, it contained some errors which are fixed now.

I have also inserted a test call, you can check according to your data.

Function vba_lookup(lookup_value, lookup_range As Range, column_position As Integer)

    Dim found_value As Range
    Dim returning_value As Range

On Error GoTo NOTFOUND

    Set found_value = lookup_range.Columns(1).Find(lookup_value, LookAt:=XlLookAt.xlWhole)
    Set returning_value = found_value.Offset(0, column_position - 1)
    vba_lookup = returning_value.Value
    Exit Function

NOTFOUND:
vba_lookup = "#N/A"

End Function

Sub test_vlookup_from_other_file()

Call vlookup_from_other_file("Main", "F2:F11", "A2:A11", 2, "C:\Users\user\Desktop", "temp.xlsx", "Sheet3", "E8:F16")

End Sub


Sub vlookup_from_other_file(main_sheet_name As String, paste_col_range As String, search_col_range As String, column_position As Integer, file_directory_path As String, file_name As String, file_sheet_name As String, lookup_range As String)


    'Application.ScreenUpdating = False
    Dim this_workbook As Workbook

    Set this_workbook = Application.ThisWorkbook

    Workbooks.Open Filename:=file_directory_path & "\" & file_name 

    this_workbook.Activate

    lookup_range = "[" & file_name & "]" & file_sheet_name & "!" & lookup_range

    row_end = Range(paste_col_range).Rows.Count

    For row_num = 1 To row_end
        Sheets(main_sheet_name).Range(paste_col_range).Cells(row_num, 1).Value = vba_lookup(Sheets(main_sheet_name).Range(search_col_range).Cells(row_num, 1).Value, Range(lookup_range), column_position)
    Next

    Windows(file_name).Close

    'Application.ScreenUpdating = True

End Sub

The function arguments are as below

  • main_sheet_name: Sheet name of range where the function will paste the fetched values
  • paste_col_range: the column where the values will be pasted
  • search_col_range: column which would be searched
  • column_position: column position of the data column to be returned when match is found
  • file_name: the file name from where function will search for the values
  • file_directory_path: the directory path of the above file
  • file_sheet_name: the sheet name of the above file
  • lookup_range: the range in which function has to search for the values
user_104981110
Related