VBA to save to Dropbox on any users computers

[Mrsbex]

Can any of you clever people out there help me please? I have reached the end of my tether...

I have this code to try and save a workbook as an XLS file to folder in Dropbox using text from the worksheet as a filename. I had it running beautifully on Macs but we are now moving over to PCs and of course the macro is not working...

Sub SaveIt()
    Dim FName As String
    FName = ActiveWorkbook.Sheets("Data").Range("D2").Value
ActiveWorkbook.SaveAs Filename:="%USERPROFILE%\Dropbox\OPERATIONS\Phone Orders" & FName & ".xls", FileFormat:=xlExcel8


End Sub

When I run it, it tries to apply the Dropbox path twice and loses the end of it too...

The error message I am getting, on my PC for instance, is...

Run-time error '1004':

Microsoft Excel cannot access the file 'C:\Users\Beckey_2\Dropbox\OPERATIONS\Phone Orders\%USERPROFILE%\Dropbox\OPERATIONS\'.

I have tried putting in a ChDir line:

ChDir "%USERPROFILE%\Dropbox\OPERATIONS\Phone Orders

but then I get:

Run-time error '76':
"Path not found" error message.

What can I do to fix this?

Thanks in advance,
Beckey.

 

[Nutster]

What is in 'Data'!D2? Does it contain only the filename, or the complete path? Does it ever get overwritten with the path?

I would probably use

ActiveWorkbook.Worksheets("Data").Cells(2,4).Value

for that, but that should work properly.

 

[Mrsbex]

Thanks Nutster, I changed it to your suggestion but it doesn't make any difference. It seems to be the path that is the issue It still takes the path from the last thing you opened, and tries to append the dropbox path stated in the macro to the end of it.

It appears to be taking everything in the quotes literally...

Oh well, I'll keep plodding on till I find a solution

 

[Fluff]

How about

Filename:= Environ("USERPROFILE") & "\Dropbox\OPERATIONS\Phone Orders\"

 

[Mrsbex]

YES IT WORKED! Thank you!!!!! You're my hero lol

How about

Filename:= Environ("USERPROFILE") & "\Dropbox\OPERATIONS\Phone Orders\"

 

[Fluff]

Glad we could help & thanks for the feedback