get.file in a vlookup macro?

  • I am new to this and wondering if a macro can be created to ask for the table reference much like the get file but within a vlookup formula?


    My problem is that the table range's filename is different each time I want to run the macro. Can I get it to prompt me for the file path somehow?


    I am at home at the moment but if more info is needed I will post back later today when I have access to the workbook.

  • I am not so sure this can be done but perhaps I can restate the question.


    I have a report I produce on a weekly basis.
    A couple of the columns lookup info from another weekly spreadsheet.


    I would like to automate the production of this report.


    The problem is that the file I need to lookup the info from changes it's name every time that worksheet is produced.
    For example Last Friday's report name was Status050203.xls
    This Friday's report will be named Status050903.xls


    Naturally, If my recorded macro reads Status050203.xls when I run it then it will be looking for bad info.


    Is there such a possibility that a wild card can be used and then it would prompt me?


    Do you know where I am trying to go with this?


    A couple of "I don't think it can be done" would suffice for my query.


    Thanks for reading.

  • Hi Report_2


    Try this Macro, should do as you want.

  • Hi Report_2,


    If your not familiar with VBA as per Daves reply you could try the example below.


    I assume that the report is generated from a master or template ??


    If not you will need to play around with the file association a bit to see what happens.


    AJW




    Can't get the image to attach see below.

  • Quote

    Originally posted by Dave Hawley
    Hey Tony! I like the way you use the colours to help read your example, very simple yet very neat!


    Dave....KISIS


    No I'm not getting fresh ;)


    KISIS = Keep It Simple I'm Stupid.


    Regards


    Tony


    PS I'm still trying to wrangle that code review job for you. One of the joys of working for a large company is their responsiveness...... :P

  • Both methods can be incorporated into my projects.
    I got more than I have asked for.


    I will try them both today when I get to work. :yes:


    Thank you very much.

  • A small correction to AJW's nifty VLOOKUP illustration. The meaning of the last value is not correct as stated. A Value of FALSE means an exact match is required. TRUE looks for the largest value less than or equal the first value in VLOOKUP and assumes the first column of data is in ascending order.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!