Set the save path based on a cell value

[Yeoman.jeremy]

Hi there,

Is there a way to set the save path as a cell value?

At the moment i set the path using

ChDir "C:\Users\owner\Desktop\Development"

in the vb coding, but it gets tedious, as i have to change this for 4 different modules in at least 3 computers in each store, in approximately 18 stores everytime i make an update to my workbook.

I want the directory to be written into a cell, which this coding points to so i don't have to search throuhg my modules to find the specific piece of code, instead i can just change the directory each computer


help[ much apppreciated,

 

[Archer47]

[QUOTE

ChDir "C:\Users\owner\Desktop\Development"

,[/QUOTE]

If I understand what you want to do then yes you can.
You could set a public string variable like strFilePath.
then you would set strFilePath = to whatever cell you want.

then your code would look like something like
ChDir strFilePath

Also instead of a cell you could just put this line of code in the initialize event if you know the path.

public strFilePath as string (I think this has to go in a module though)
strFilePath ="whatever the path is"

and then replace all of the previous code with ChDir strFilePath
That way you should only have to change it in one place.
Hope that helps

 

[Yeoman.jeremy]

Sounds like a good idea, but i already use strFilePath for different parts of the codes in the modules

 

[ron2k]

Hi there,

Is there a way to set the save path as a cell value?

At the moment i set the path using

ChDir "C:\Users\owner\Desktop\Development"

in the vb coding, but it gets tedious, as i have to change this for 4 different modules in at least 3 computers in each store, in approximately 18 stores everytime i make an update to my workbook.

I want the directory to be written into a cell, which this coding points to so i don't have to search throuhg my modules to find the specific piece of code, instead i can just change the directory each computer


help[ much apppreciated,

Something like:

ChDir "C:\Users\owner\Desktop\" & Worksheets("Sheet1").Range("A1").Value

 

[Yeoman.jeremy]

Something like:

ChDir "C:\Users\owner\Desktop\" & Worksheets("Sheet1").Range("A1").Value

The point is that the names of computers, and their user log ins is all different, so i would still have to go in and change the "C:\Users....." etc

I did try using

ChDir Sheets("Job Sheet").Range("E9")

but it came up with the error "path not found"

I'll try adding .value on the end though, and see if it recognises it then

Edit: still says "path not found

 

[Archer47]

The point is that the names of computers, and their user log ins is all different, so i would still have to go in and change the "C:\Users....." etc

In this case you can use
activeworkbook.path


ChDir activeworkbook.path & Worksheets("Sheet1").Range("A1").text
you might need a "/" before worksheets

 

[ron2k]

I'll try adding .value on the end though, and see if it recognises it then

Edit: still says "path not found

Yes, you need the .Value (or .Text) argument. And ensure that 'Job Sheets'!E9 contains


C:\Users\owner\Desktop\Development
And nothing else. No " or ' before or after it.

 

[Yeoman.jeremy]

In this case you can use
activeworkbook.path


ChDir activeworkbook.path & Worksheets("Sheet1").Range("A1").text
you might need a "/" before worksheets

Awesome!
It's now saving it without an error prompt (without the "/" - with it. it gave an error)

although there is still one slight problem.
the cell value is

C:\Users\owner\Desktop\Development\Tested Results\
Yet it saves the file in the 'development' folder.
Any ideas?

 

[ron2k]

Awesome!
It's now saving it without an error prompt (without the "/" - with it. it gave an error)

although there is still one slight problem.
the cell value is

C:\Users\owner\Desktop\Development\Tested Results\
Yet it saves the file in the 'development' folder.
Any ideas?

What's the piece of code that saves the workbook? The bug may be there

 

[Archer47]

Not Sure but I'll give you some testing code
Are you writing in the cell value or getting it from somewhere else?

try this for testing

dim strTempPath as string
dim strTempPath2 as string
dim strSavePath as string
strTempPath = Worksheets("Sheet1").Range("A1").text
strTempPath2 = activeworkbook.path
strSavePath = strTempPath & strTempPath2
ChDir strSavePath

let me know what strSavePath equals