Dynamic Workbook Referencing

I have about 170 workbooks with different names but each contain two worksheets with similar names and table layouts. There is another workbook names 'Invoice' that I want to populate with information from any of the other 170 workbooks when they are opened.

For instance, If I open the 'Invoice' workbook and the workbook named 'Sally Adams', the information in the sheet named 'Folio' from Sally Adams workbook populates the 'Invoice' workbook. The formular in the 'Invoice' workbook is: [Sally Adams.xls]Folio'!$G$30

If I close Sally Adams' workbook and open another similar workbook named 'Carl Smith' the value in $G$30 should update the 'Invoice' workbook. Currently this is NOT so since the formular does not from: [Sally Adams.xls]Folio'!$G$30 to [Carl Smith.xls]Folio'!$G$30

Please I don't want to change the name of each of the files in the formular. I need any VBA code or Excel formulr that will change this based on the the workbook that is opened with the 'Invoice' workbook.

For a VBA solution, perhaps something like this:
Dim wkbInv As Excel.Workbook
Dim wkbOth As Excel.Workbook
Dim sWkbName As String

Workbooks.Open "C:\myPath\Invoice.xls"
Set wkbInv = ActiveWorkbook
' ...
' some For loop
'...
Workbooks.Open sWkbName
Set wkbOth = ActiveWorkbook
'...
wkbInv.Worksheets("Folio").Range("$G$30") = wkbOth.Worksheets("Folio").Range("$G$30")
' ...

I would recommend that you go to the edit menu in the Invoice workbook and change the links.