how do i change the file path for vba worksheets when transfering files to another pc?

[confused monkey]

hello all, i have about 50 macro enabled worksheets and around the same for xlsx and open them through a button system using vba. if i transfer these files to another pc, then i cannot open them in the same way as the file path changes : Workbooks.Open Filename:="c:\users\laboratory\documents\worksheets\worksheets\external mic\imperial digital external mic.xlsx" the blue part obviously need to be changed to accomodate the new pc, so is the a way to put in a variable so it references another sheet/macro to change the first part of the filepath? cheers.

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[TMS]

http://www.excelforum.com/tips-and-t...excel-vba.html

Please Login or Register  to view this content.

Or just

Please Login or Register  to view this content.

Please note ... Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[TMS]

@zbor. Thanks, and sorry. Tried to delete the response myself but you beat me to it

[zbor]

I've just soft delete it so I can restore it once user change a title.
Don't want to waste your effort.

[confused monkey]

sorry about that. changed now.

[confused monkey]

cheers for the response. i'm not too up on the jargon in the code (self taught and struggling). is there anyway you can explain what each line is and where to implement the macro? sorry for being a retard

[TMS]

Just look at the short example at the end. It gets the my document path and puts it in a string. So use that when you save the file instead of hard coding the whole file path.

[confused monkey]

am i putting this in the button click macro to open the sheet or the sheet itself? i could send an attachment that would better explain if thats any good to you?

[TMS]

Please Login or Register  to view this content.

It does rather assume that the rest of the folder structure exists under the My Documents folder.

[confused monkey]

cheers, i can see what it's doing. where do i paste this though? you'll need to use simple terms as i'm a simpleton

[TMS]

Replace the file name in the Workbooks.Open with the modified file name

[confused monkey]

that works a treat. you sir are an effing genius. thanks a lot for taking the time to help me. have a great weekend and thanks again.

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.