Hacking cross workbook references, how to connect local references

  • So I have a program that exports a calendar in HTM, for multiple rate types.
    I can import individual pages data as if it was another workbook (scraping from locally generated htm files)
    I want to do so elegantly. I have a top row of header information


    the link for the scrape looks like this in cell a2, then repeats across and down and works
    ='D:\IQ\[IQ_rates_ufo.htm]IQ_rates_ufo'!A1


    ufo is specific, there are others, I would copy worksheets and have it pull the code
    I want to, in my scraping workbook, have UFO be on my top row (cell a1)
    and have what file is scraped be contained in a formula, I thought it would be
    ='D:\IQ\[IQ_rates_"&$a$1&".htm]IQ_rates_"&$a$1"'!A1
    but no good.


    I've tried every text connector I can think of such as
    ='D:\IQ\[IQ_rates_"+$a$1+".htm]IQ_rates_"+$a$1"+'!A1




    How?