Read data from closed workbook

I have a pricelist that another workbook is referring to. But each time I open the workbook I need to open the price list to get it updated. I don't want that, as other users would not understand this.

After googling around I understand that I can do something with VBA so that it can be read when opened without being opened?

One that I tried was this, but it does not seem to work. Also I want to read the whole workbook not only rows/columns/sheets. Is there an easier way?

Option Explicit

Private Sub Workbook_Open()
    Call ReadDataFromCloseFile
End Sub

Sub ReadDataFromCloseFile()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    Dim src As Workbook
    
    ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
    Set src = Workbooks.Open("C:\Dropbox\5. Programmer\Excel\Prisliste Database.xlsm", True, True)
    
    ' GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK.
    Dim iTotalRows As Integer
    iTotalRows = src.Worksheets("sheet1").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count
   
    ' COPY DATA FROM SOURCE (CLOSE WORKGROUP) TO THE DESTINATION WORKBOOK.
    Dim iCnt As Integer         ' COUNTER.
    For iCnt = 1 To iTotalRows
        Worksheets("Sheet1").Range("B" & iCnt).Formula = src.Worksheets("Sheet1").Range("B" & iCnt).Formula
    Next iCnt
    
    ' CLOSE THE SOURCE FILE.
    src.Close False             ' FALSE - DON'T SAVE THE SOURCE FILE.
    Set src = Nothing
    
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Thanks but that didn't work either.

looking at the link the status says "Error: worksheet not found".

I'm new with VBA. But does that code search the whole worknook or only sheet 1? The workbook has several sheets.

Thanks

I just coded based on your code

        Worksheets("Sheet1").Range("B" & iCnt).Formula = src.Worksheets("Sheet1").Range("B" & iCnt).Formula

by the way, I just realized the col reference "B", so I edited the code.

Ok. How can it be coded to read the whole workbook not only sheets?

What are you really trying to do then?

I have a workbook that is linked to another workbook picking up data. And when I open the working sheet I always need to open the linked workbook to get it working. I was hoping to not needing to open that data workbook each time, just have it automatically look for the file and read the whole workbook.

What do you mean by "whole workbook"?

Can you just explain step by step when you do it manually.

Ok So I have 2 workbooks. Pricelist and Builder. Builder looks into Pricelist for data to calculate.

So I open Builder and the first thing I'm met with is an error:

We can't update some of the links in your workbook right now

So I press Continue and nothing works due to links not working.

So if I go to "edit links" it shows the price list but status is unknown, trying to check the status I get error that its not found. If I however open source I open the price list. Now that its open I can use the builder as intended.

I just dont want that error to show when using builder, and I dont want to open the source file each time. I only want to open the builder and have it working.

Is that possible?

In that case, when you open the Builder with the error message, open Pricelist.
If the link works, close Pricelist and the formula should automatically updates the full path.

Does that help?

Yes that works. But I dont want to open pricelist each time, as other users would not understand this.

Is there any way to avoid this with VBA?

After the link is automatically updated, you don't need to open the other workbook.
Make sure the link is now full path.

It is. But once I open it up on another PC I need to open the pricelist to have it synced again. I dont want that.

That means the path is changing, there's no way to update the link with unknown path.

No the path is the same. We share dropbox and have dropbox on C: on all PCs. So path is always the same. I just need to find a VBA code for it to use instead.

Then show us the current formula in the cell with the link that is throwing the error and the the full path of the Pricelist.

Here is one:

=TRANSPOSE(SORT(UNIQUE(FILTER((UNIQUE(FILTER('Prisliste Database.xlsm'!Produkt[Produkt];'Prisliste Database.xlsm'!Manufacturer[Produsent]=Bygger!$B2)));ISNUMBER(SEARCH(Bygger!$C2;(UNIQUE(FILTER('Prisliste Database.xlsm'!Produkt[Produkt];'Prisliste Database.xlsm'!Manufacturer[Produsent]=Bygger!$B2)))));"Not Found"))))

And under Data - Edit Links it shows the patch to the correct place. But each time I have to open the pricelist workbook to get the builder working. I just dont want to open the price list each time I want to use the builder workbook.

I don't think you can link to the "Table" in closed workbook.
So, better use range...