using a dialog box to select the file for consolidation

  • Re: using a dialog box to select the file for consolidation


    i can give you the a copy of the workbook i use i.e the master book.
    but i do not have a copy of the input file as it is on a desktop without access which makes this difficult .....i am uploading my workbook presently

  • Re: using a dialog box to select the file for consolidation


    Sheet layouts in "Master" is different

  • Re: using a dialog box to select the file for consolidation


    is the blue colour going to be a problem ?
    i was asked to make it more presentable


    this is what i was using if it helps you.... minus the file open dialog box the reference and the path has to be set here always.................



  • Re: using a dialog box to select the file for consolidation


    hello jindon it is not copying with the actual data.......


    i have removed the blue fill and the bold formatting but it is not copying........



    the both the input and the output file headers are removed of header formatting now..... what could be the reason. it works with the workbook i gave you though... so i think it is the problem of you unable to see my data... which i am not allowed to take.....


    i can get the headers though is that fine...?
    RMG Dashboard.xlsm


    sheet 3 contains how the headers actually look of my input file..
    also if there is a way it could be done without being bothered by cell colour and things like bold, italics and so on it would be helpful(copy between files should be done only on the cell text headers)


    thank you for being extremely patient with my problem

  • Re: using a dialog box to select the file for consolidation


    I have tested the code with the files that you have uploaded in you post #22 and it is working.


    If you are talking about different files and you want to adjust the code, don't even to try to think that way.


    If you can, you could write it without any help.

  • Re: using a dialog box to select the file for consolidation


    fn = "'" & Left$(fn, InStrRev(fn, Application.PathSeparator)) & _ "[" & Mid$(fn, InStrRev(fn, Application.PathSeparator) + 1) & "]sheet1'!"
    x = ExecuteExcel4Macro(fn & "r1c1") x = ExecuteExcel4Macro("match(""" & .Cells(1, i).Value & _ """," & fn & "r1:r1,0)") If IsNumeric(x) Then y = ExecuteExcel4Macro("counta(" & fn & "c" & x & ":c" & x & ")") For ii = 2 To y .Cells(ii, i).V

  • Re: using a dialog box to select the file for consolidation


    First, you should get vb help file or internet abount the key word that you don't know for yourself.

    e.g ExecuteExcel4Macro, InStrRev, Application.PathSeparator. etc

  • Re: using a dialog box to select the file for consolidation


    i hear you jindon.....
    but i have no way of anticipating the kind of formatting they use, if you could take a look at my code post #24
    cause it seems to work with any formtaing and that is something that would help me....

  • Re: using a dialog box to select the file for consolidation


  • Re: using a dialog box to select the file for consolidation


    mate i was looking to avoid giving the reference using an open file dialog box so nobody has to step in the code and do it everytime.......

  • Re: using a dialog box to select the file for consolidation


    apologies for the mistake......but i have mentioned why i wanted to do this in the question.
    no matter.... how do i change it.

  • Re: using a dialog box to select the file for consolidation


    ill make it short and brief....


    the code that i have matches headers between two workbooks and copy pastes the data.....
    now this works but the problem is the user will have to go inside the code and give the name of the workbooks and the sheets and i want to avoid it using a file open dialog box....
    in my code global headcount is the name of my file and headsheet from where i want to copy the data, and dashboard is the name of the workbook and sheet which i wan to paste them tooo.


    hope that helps jindon..... this is the only problem i am facing....
    please advise

  • Re: using a dialog box to select the file for consolidation


    actually if you post #1 you will see i have mentioned the problem completely.... but i now realise the title has been poorly framed as jindon has said....
    so ill post another question

Participate now!

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