VBA REFERENCE FORMULA EXCEL

stpath = "R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\Cells(5,13)\All"

stpath = "R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\Cells(5,""M""))\All"

Both attach no file


stpath = "R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\Cells(5,13)\All"

    stfilename1 = str & " GB-Corp (Jan-15) - Managed Region .pdf"
    stfilename2 = str & " GB-Corp (Jan-15) - Booked .pdf"
    stfilename3 = str & " GB-Corp (Jan-15) - Managed .pdf"

stfilename1  is blank once I change from hardcoding to above

Thanks!

Change to

stpath = "R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\" & Cells(5,13) & "\All"

stpath = "R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\" & Cells(5,"M") & "\All"

stpath = "R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\" & Cells(5,13) & "\All"

Sorry to be a pain but my VB is weak

I changed to the below and they attached nothing

    stfilename1 = str & " " & Cells(15, 14) & " (Jan-15) - Managed Region .pdf"
    stfilename2 = str & " " & Cells(15, 14) & " - Booked .pdf"
    stfilename3 = str & " " & Cells(15, 14) & " - Managed .pdf"


Thanks

I don't know what you mean by "they attached nothing".

If after those lines you have

Msgbox stfilename1

then you will see what is there.

So line of code

stfilename1 = str & " GB-Corp (Jan-15) - Managed Region .pdf"

Recognises the file in the folder and attaches

stfilename1 = str & " " & Cells(15, 14) & " (Jan-15) - Managed Region .pdf"

Does not recognise any folder in that file

Cell N5 in the drop down in excel = GB-Corp

Sorry - I thought you said N15.

Use cells(5,14) instead of cells(15,14).

It is in the format cells(rownumber, columnnumber).

So I could automate the date as well I amended to

 stpath = "R:\SPM\Horis Info\Horis_Project\GBM\" & Cells(5, 15) & "\Output\" & Cells(5, 13) & "\All"

Where 5,15 = Jan-15 in excel

Again the vba doesn't recognise the file now

Il uplaod

Burst-Tool-V1.xlsm

You need to format the date

 stpath = "R:\SPM\Horis Info\Horis_Project\GBM\" & format(Cells(5, 15),"mmm-yy") & "\Output\" & Cells(5, 13) & "\All"

This outputs

R:\SPM\Horis Info\Horis_Project\GBM\Jan-15\Output\Europe\All

Seamus,

This is a part of bigger code so help me understand that this path will it changes for the respective person in the next line??

In additional can you help me understand how does the path will look like??

Saurabh...

Hi Saurabh,

Have you opened the above file?

And i would use rather this code..

Since assuming this can change from person to person...

stpath = "R:\SPM\Horis Info\Horis_Project\GBM\" & Format(Cells(cell.Row, 15).Value, "mmm-yy") & "\Output\" & Cells(cell.Row, 13).Value & "\All"

Select allOpen in new window

Saurabh...

The above works perfect Saurabh

Are you able to replicate for line

stfilename1 = str & " GB-Corp (Jan-15) - Managed Region .pdf"

So GB-Corp is found in cell N5 and Jan-15 is found in cell O6

Thanks!

For this will use...

stfilename1 = str & " "& cells(cell.row,14).value & " (" & format(cells(Cell.row,15).value,"mmm-yy") & ") - Managed Region .pdf"

Select allOpen in new window

Again the idea to use it this way is that lets say in next row if you get a new input it will pick that input rather then picking from one cell at a time...

Saurabh...

Sorry, I might have confused things here Saurabh

From my attached file the code ran perfectly for the first recipient Zimmer, but the other recipients received no files?

Thanks
Burst-Tool-V1.xlsm

Thanks guys!

Saurabh, im about to post a question relating to your code which you may be able to help me with!