External reference to xls file of same name on different users desktops regardless of user

[dshandyman]

Hello : )

My name is Dave Schneider

I'm gonna keep this short.

20 users that export data in *.xls format to their desktops and each name the exported file simply exported.xls.

I have code that is able to link to my *.xls file on my desktop using a command button to fetch the values. (example below of just one line) works like a champ!

Worksheets("Daily Report").Range("B19").Value = "='C:\Users\dschneid\Desktop\[exported.xls]Sheet1'!R7C5"

But since each user has a different username & path to desktop, my code does not work for all. What do I put in code that tells excel to fetch the values from exported.xls on desktop regardless of user?

Would really appreciate help.

[TMS]

Try:

Please Login or Register  to view this content.

[dshandyman]

Try:

Please Login or Register  to view this content.

TMS,

Thank you so much for your reply. Your code worked good on my computer. When I tried it on another computer with a different username, for every entry, it pops up a windows dialog box to point to the file it's referring to. So for 30 entries, you have to point to the file 30 times. If you hit cancel, it just puts REF in the cell that didn't get linked.

[TMS]

Can you upload sample workbooks?

[dshandyman]

Can you upload sample workbooks?

Absolutely!

[TMS]

Ok, will have a look tomorrow.

[TMS]

I don't know why it's not working for them, other than it may be opening in protected mode because the file has been emailed to them.

It worked fine. Maybe make sure they save the attachment to their Desktop first and, to be on the safe side, open it and enable editing.

You don't really need 30 lines of code to populate the table, assuming that it is just a block of data that you are transferring in order to chart it. You can just use this one line:

Please Login or Register  to view this content.