Changing email addresses in modules

Tonysdilemma

New Member
Joined
Aug 2, 2020
Messages
28
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
You can use the replace feature in the VB Editor. Ctrl H will open it.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
You could also set up a Public Constant and use that to change the file path quickly.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
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.
 

Tonysdilemma

New Member
Joined
Aug 2, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You can use the replace feature in the VB Editor. Ctrl H will open it.
Thank you very much. Didn't think you could use find and replace in VB editor. Works like a charm
 

Tonysdilemma

New Member
Joined
Aug 2, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,237
Messages
5,641,026
Members
417,188
Latest member
DrTees

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
Top