+ Reply to Thread
Results 1 to 19 of 19

Macro doesn't work when filename changes!

  1. 07-05-2010, 01:01 PM #1
    CristinaS
    CristinaS is offline
    Registered User
    Join Date
    07-05-2010
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Talking Macro doesn't work when filename changes!

    Good day everyone!
    I hope someone can help me out...

    I am currently trying to run a macro on a spreadsheet to consolidate information.
    It works fine on the original file...
    This file will become a template of sorts which will allow other users to run the macro to expedite the workload.... So each individual who saves the template wherever they want can still run this macro.

    When I change the directory or filename the 1004 error appears... as it cannot locate the original file...
    Please Login or Register  to view this content.
    Is there a way I can change the path to "This workbook"??? I'm not sure what the programming should be...
    I'm self taught at excel... and vbasic is waaaay beyond my expertise...
    I actually just taught myself macros and consolidation last week....

    I'm not sure if I'm explaining myself clearly or not....

    I want the macro to run if the file is elsewhere or named something else....

    Thanks for your help!!
    Last edited by CristinaS; 07-05-2010 at 04:29 PM. Reason: add code tags
    Reply With QuoteRegister To Reply
  2. 07-05-2010, 01:02 PM #2
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Macro doesn't work when filename changes!

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

    Added this time
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example
    Reply With QuoteRegister To Reply
  3. 07-05-2010, 01:06 PM #3
    CristinaS
    CristinaS is offline
    Registered User
    Join Date
    07-05-2010
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro doesn't work when filename changes!

    I'm sorry
    I'm not sure how to do that either...
    Thanks for your help though!
    Reply With QuoteRegister To Reply
  4. 07-05-2010, 01:07 PM #4
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Macro doesn't work when filename changes!

    I'm not sure what you are doing,maybe
    Please Login or Register  to view this content.
    As for addingcode tags read the Forum Rules & the Forum How To's & FAQs
    Reply With QuoteRegister To Reply
  5. 07-05-2010, 01:10 PM #5
    CristinaS
    CristinaS is offline
    Registered User
    Join Date
    07-05-2010
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro doesn't work when filename changes!

    ok
    So
    The file is save on my pc....as TRIAL1.xls... we can all see that...
    I will be distributing this worksheet to numerous users who will then save it in their own directory under their own filename.

    I would like to be able to change the file path and name at any given time and have the macro still work.... without having to change the code everytime... I'm apparently the most advanced excel user in the office... and I'm not that advanced....
    Reply With QuoteRegister To Reply
  6. 07-05-2010, 01:11 PM #6
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Macro doesn't work when filename changes!

    Like this?
    Please Login or Register  to view this content.
    I'm not sure how to do that either...
    Everything you need to know is in the forum rules.
    Last edited by shg; 07-05-2010 at 01:13 PM.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  7. 07-05-2010, 01:18 PM #7
    CristinaS
    CristinaS is offline
    Registered User
    Join Date
    07-05-2010
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro doesn't work when filename changes!

    I tried to input that code shg...

    I am now getting an error...
    Cannot open consolidation source file [TRIAL 1.xls]IVR Info
    Reply With QuoteRegister To Reply
  8. 07-05-2010, 01:34 PM #8
    CristinaS
    CristinaS is offline
    Registered User
    Join Date
    07-05-2010
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro doesn't work when filename changes!

    what about something like this?

    could one put

    Please Login or Register  to view this content.
    into my code for me? I'm not sure how to!?!?!
    IVR Info is the sheet name....

    Would that work??

    Please Login or Register  to view this content.
    Last edited by CristinaS; 07-05-2010 at 01:49 PM.
    Reply With QuoteRegister To Reply
  9. 07-05-2010, 01:35 PM #9
    CristinaS
    CristinaS is offline
    Registered User
    Join Date
    07-05-2010
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro doesn't work when filename changes!

    Sorry....
    Again i quoted.... didn't use code....
    Where is code in quick reply???
    Reply With QuoteRegister To Reply
  10. 07-05-2010, 01:43 PM #10
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Macro doesn't work when filename changes!

    Please edit your post, click Go Advanced, select the code, and press the button with the # sign.

    I am now getting an error...
    Cannot open consolidation source file [TRIAL 1.xls]IVR Info
    The file has to be in the same directory as the workbook.
    Reply With QuoteRegister To Reply
  11. 07-05-2010, 02:05 PM #11
    CristinaS
    CristinaS is offline
    Registered User
    Join Date
    07-05-2010
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro doesn't work when filename changes!

    what about something like this?

    could one put

    Please Login or Register  to view this content.
    into the code???
    would this work?
    If so.... could someone put it into my code for me???? I'm not sure where to put a space or apostrophe or whatever i may need...
    I've attempted it without success... I keep getting a runtime error 13...

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 07-05-2010, 02:27 PM #12
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Macro doesn't work when filename changes!

    No I don't know what you're trying to do.

    Tak a breath and explain.
    Reply With QuoteRegister To Reply
  13. 07-05-2010, 02:29 PM #13
    CristinaS
    CristinaS is offline
    Registered User
    Join Date
    07-05-2010
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro doesn't work when filename changes!

    i would like to allow any user of this file to be able to change the filepath and/or filename and have the macro still function.
    Last edited by CristinaS; 07-05-2010 at 02:36 PM.
    Reply With QuoteRegister To Reply
  14. 07-05-2010, 02:43 PM #14
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Macro doesn't work when filename changes!

    Maybe like this -- I can't test it.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  15. 07-05-2010, 02:52 PM #15
    ganero
    ganero is offline
    Registered User
    Join Date
    07-02-2008
    Location
    Indonesia
    Posts
    5

    Re: Macro doesn't work when filename changes!

    how bout this : 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  16. 07-05-2010, 03:06 PM #16
    CristinaS
    CristinaS is offline
    Registered User
    Join Date
    07-05-2010
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro doesn't work when filename changes!

    unfortunately that didn't work either as the information I want to consolidate is in the same workbook under the IVR Info tab...
    i tried that code and it asked me to find the information i'm looking to consolidate...

    i'll break it down a little more

    i copy and paste information onto the IVR Info worksheet
    I then want to click on the A1 cell on the Consolidated tab and hit the macro hotkey...
    It will autopopulate the information from the cells for me at that point, creating a summarized report... on another worksheet that's formatted all pretty...
    The macro works fine if I record it in the original file. Some of our company users do not have the same access to the same drives on the server,...
    I will be distributing the workbook to numerous people via email and allowing them to save it as whatever they would like whereever they choose or have access to on the server.
    Some individuals may opt to save the file under a different file name prior to copying the information onto the IVR Info tab, thus ensuring they do not overwrite the original file...

    I would like to run this macro no matter what the file path or file name is...
    I would think that the "thisworkbook" function may be the way to go? Just a guess...

    I'm very unfamiliar with macros, but I would think one could program the macro to recognize that the file path and/or file name has changed....
    Reply With QuoteRegister To Reply
  17. 07-05-2010, 03:16 PM #17
    CristinaS
    CristinaS is offline
    Registered User
    Join Date
    07-05-2010
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro doesn't work when filename changes!

    Quote Originally Posted by ganero View Post
    how bout this : 

    Please Login or Register  to view this content.
    I tried this one as well...
    unfortunately it did not work either
    it was unable to find IVR Info
    Reply With QuoteRegister To Reply
  18. 07-05-2010, 03:28 PM #18
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Macro doesn't work when filename changes!

    OK, once more:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  19. 07-05-2010, 04:25 PM #19
    CristinaS
    CristinaS is offline
    Registered User
    Join Date
    07-05-2010
    Location
    Calgary, AB, Canada
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro doesn't work when filename changes!

    YAY!!!
    IT worked!
    So I'm seeing it as just removing the file path and file name.... if that's correct then perfect!
    Thank you SOOO MUCH!!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1