Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004

    Unanswered: Macros in two different workbooks

    This is probably very straightforward but it's got me stumped..

    I have a blank questionnaire set up as an Excel template. I also have a completely separate workbook which I use to collate the information (text and numbers) gathered by the questionnaires. Until now I have done this manually but have written a button-operated macro in the questionnaire workbook which opens the other database file, adds the new data and sorts it alphabetically.

    The problem is that I need to save the questionnaire (the Excel template) to my hard drive under an individual file name every time I complete one and if I don't want to export the data before saving, when I later retrieve the individual workbook and try to transfer the data using the macro,the macro looks for the template filename which, of course, has been overwritten. I have tried to use ThisWorkbook and ActiveWorkbook to sort it out but no luck so far.

    Any help would be gratefully received.


  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland
    Why don't you store the name of the workbook as a public variable if using a macro straight through without stopping.

    If you have to reopen the workbook manually then instead of just opening it use application.getopenfilename, Storing the filename as a variable and trimming off the path by using a mid function to refrence the workbook.

    Or when you open the workbook you could store the name of the workbook when opened immediatly by using variable =

    or if you know what position the workbook is in you could refrence the workbook by its position.

    Hope this Helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts