Changing email addresses in modules

Tonysdilemma

New Member
Joined
Aug 2, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
I have designed a parcel collection system within Excel.
I have 80 modules written up.
One off the modules is coded as follows

VBA Code:
Sub SaveSheetToPDF1()

Dim ws As Worksheet

Set ws = ActiveSheet

Sheet7.Unprotect Password:="pixieland"

ws.Unprotect Password:="pixieland"

ws.Range("A1:I12").ExportAsFixedFormat xlTypePDF, Filename:= _

"C:\Users\JustOne\Desktop\Rabbits\Parcels Collected\", Openafterpublish:=False



Sheet7.Range("B3:D3").ClearContents

Sheet7.Range("F3:H3").ClearContents

ws.Range("C6:E6").ClearContents

ws.Range("G7:H12").Delete

ws.Range("C9:E12").ClearContents

ws.Protect Password:="pixieland", UserInterfaceOnly:=True

Sheet7.Protect Password:="pixieland", UserInterfaceOnly:=True

Sheet7.Range("A3").Value = Sheet7.Range("A3").Value Mod 99 + 1.01

Sheet7.Protect Password:="pixieland", DrawingObjects:=False, Contents:=True, Scenarios:=True

Sheet7.Activate

End Sub

I want to use the 80 modules on another computer. Everything will remain the same except for the file where it is saved when a parcel is collected. At the moment the current file to which it is saved is "C:\Users\JustOne\Desktop\Rabbits\Parcels Collected\"
Is there a way where the file name in the modules can be changed without writing it up 80 times?
 
Last edited by a moderator:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
You can use the replace feature in the VB Editor. Ctrl H will open it.
 
Upvote 0
You could also set up a Public Constant and use that to change the file path quickly.
 
Upvote 0
Use Find/Replace to replace every occurence of 'C:\Users\JustOne\Desktop\Rabbits\Parcels Collected\' in the project with a variable, let's say, strSavePath.

In a standard module declare and set the value for strSavePath.
VBA Code:
Public Const strSavePath = "C:\Users\JustOne\Desktop\Rabbits\Parcels Collected\"
Now the original code will work and when you move it over to another computer all you need to is change the value of strSavePath.
 
Upvote 0
Use Find/Replace to replace every occurence of 'C:\Users\JustOne\Desktop\Rabbits\Parcels Collected\' in the project with a variable, let's say, strSavePath.

In a standard module declare and set the value for strSavePath.
VBA Code:
Public Const strSavePath = "C:\Users\JustOne\Desktop\Rabbits\Parcels Collected\"
Now the original code will work and when you move it over to another computer all you need to is change the value of strSavePath.
Works like a charm. Thank you
 
Upvote 0
If it's just the username that you need to change you can use
VBA Code:
Environ("userprofile") & "\Desktop\Rabbits\Parcels Collected\"
This will then work on any machine
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top