change file path to look at a cell in the sheet

[curtoliver68]

I have a form that has several modules that print screen to the desktop and modules that print a PDF to a determined path. I want to change that to look at a cell in the sheet so I can change between five different people and the folders they need to print to.

I want to change the path to look at cell V30 for the PDF and V31 for the Photos

 

[saboh12617]

Hello,

For the first screenshot, as you did for the previous lines, you just need to do

file_path=Range("V30").Value

For the second one, you need to write the full path to your photos folder
(by default on windows "C:\users\default\Pictures")

And you can refer to it in the same way, without using the shell shortcut :

Path = Range("V31").Value & "\" & Range("V3").Value & ".jpg"

 

[curtoliver68]

so I tried the file_path=Range("V30").Value, and now it says publishing, but nothing happens. The old way with the path it showed saving and showed the path where it was saving.

There is an xlookup formula in the cell. Would that create any problems?

 

[saboh12617]

No, formula is not a problem as long as the value in the cell is a correct path. It should be a full path, so something like "C:\users\default\Pictures".

 

[curtoliver68]

ha, I found the problem but am unsure what I need to do. I'm looking at cell V30 but it's on sheet19 not the current sheet

 

[saboh12617]

Okay, be careful about the sheets, it is always recommended to reference sheets when referring to their cells to avoid those errors.

If the value is on the sheet named "sheet19", the correction is the following

file_path=ThisWorkbook.Worksheets("sheet19").Range("V30").Value

I recommend you do the same correction on every one of the "Range" instruction you can find in the code. By default they all refer to the activesheet, which can change. When you explicitly write the sheet name (and the workbook), you avoid those errors.

 

[curtoliver68]

so would it look like something like this?

 

[curtoliver68]

So i added the line and changed the cell which will be the new location but a debug error

 

[saboh12617]

You did it correctly.

But with no information on the error I can not help you much. But I'm pretty sure your sheet name is not exactly "sheet19". Please copy the sheet name from the sheets list at the bottom and paste it in the code (between "").

If it is the correct name then it means your macro is not in the workbook where your sheets (and cells) are, in this case you should use

Workbooks("name of your file.xlsm").Worksheets("name of the sheet").Range("address of the cell")

But double check the sheet name, the error is probably there.

 

[curtoliver68]

Thank you so much for your help and patience. I made all the changes you suggested and all is working perfectly now.