mapping user defined Path & FileName using VBA

Dear experts
I have the following vba which exports worksheet data to my desktop. Path and the filename are hard coded in the macro but there may be instances when end users may need to change the path and the name of the output file.
I have defined the same path in cell $D$2 in another worksheet (say Sheet 6) with the file name in cell $D$3. Furthermore to make the output files unique I want to have them date/time stamped so I have specified this in $D$4
Please could you help modify the following code that would help achieve the desired results.

Thank you in advance
D

Please Login or Register  to view this content.

Like this, though I'd be tempted to use named ranges rather than cell references

Please Login or Register  to view this content.

Thx Kyle123

Unfortunately I have a run-time error '1004':
Method 'SaveAs' of object@_ workbook failed.

Any idea?

D

I need a bit more to go on than that. What's the resulting full fine name and path?

Hi there..

I have the following values in Sheet6 but will be renamed it to the actual name but for this example, call it TEMPS

Cell D2 = Export PATH: C:\Documents and Settings\" & Environ("UserName") & "\Desktop\
Cell D3 = File Name: SPD_Other
Cell D4 = Date & Time 25/05/2016 11:18

D

You can't write VBA in a worksheet cell. It has to be a proper path

Hi

Cell D2 had the path i.e. C:\Documents and Settings\" & Environ("UserName") & "\Desktop\ for now and user may set it to another path when folder are created
Cell D3 contains the file name called SPD_Other
Cell D4 has date and time of 25/05/2016 11:18

Does this clarify the matter
D

No, it doesn't. Again, you cannot write via in worksheet cell and expect it to work:

This:
& Environ("UserName") &

is VBA, you need a proper file path:
C:\Documents and Settings\kyle\Desktop\

yes. & Environ("UserName") would translate to who the user will be

d

Ok, I'm going to leave this thread. You don't understand the point I'm making and I'm not quite sure how else to explain it.

Hopefully someone else will help you

Thx Kyle for you help so far.

have a great day
D