I finally figured out how to embed freeform sheet data in modules (which is useful when embedding SAP and other live data in your model) and I thought that was worth sharing with the forum.
First, set up a freeform sheet and embed your live report there.
Then use the following two formulas to extract the sheet name (which will be used in the indirect formula in the next step):
Cell 1 (assume this is B10) is an assumption Heading cell which references the sheet name on the freeform sheet.
In Cell 2 enter the following formula to extract the sheet name: =MID(FORMULATEXT(B10),2,FIND("!",FORMULATEXT(B10))-2)
Then you can use the sheet name derived in Cell 2 in any formula by using any combination of Indirect. I find using an Offset is very handy if the length of your live report is not static.
Hope this helps somebody out there