Find Dropbox Path on OS X using Macro in Excel 2011

Hi,

I currently have an order form which uses a macro to store the xls and csv in dropbox. This form is used on several macs, and currently, I have to make a specific excel file for each mac with the specific paths for that particular mac in the macros.

I don't think I explained that very well so, for example this saves the file as a CSV:-

Quote

Sub SaveItAsCSV()
Dim FName As String, FPath As String

FName = ActiveWorkbook.Sheets("CUSTOMER").Range("C12").Value
FPath = "Macintosh HD:Users:Office2-Spare:Dropbox:Operations:Phone Orders:"

ActiveWorkbook.SaveAs Filename:=FPath & FName & ".csv", FileFormat:=xlCSV

End Sub

Display More

So for each mac that uses this file I have to edit their copy, say from Office2-Spare to read Despatch as that is their user name.

I have tried using

FPath = "Macintosh HD:Users:$USER:Dropbox:Operations:Phone Orders:"

but it didn't like it.

Perhaps there is a way of finding the Dropbox location on each mac when the macro is run, but I wouldn't even know where to start with that...

Can anyone help me?? I have trawled the internet but come up with nothing!

Thanks in advance

Re: Find Dropbox Path on OS X using Macro in Excel 2011

Hi,

Thanks for the quick reply! I have tried what you suggested, and it still needs a debug and throws up Run-time error '1004', the file could not be accessed. When I click debug, this is what I get...

[ATTACH=CONFIG]69599[/ATTACH]

Does it mean anything to you?

Thanks for your help, I'm completely bewildered!

Quote from S O;773421

Hi BeckeyB, welcome to Ozgrid

Try the below:

Code

Sub SaveItAsCSV()
Dim FName As String, FPath As String, Username As String


FName = ActiveWorkbook.Sheets("CUSTOMER").Range("C12").Value
Username = MacScript("set uName to short user name of (system info)" & vbCrLf & "return uName")
FPath = "Macintosh HD:Users:" & Username & ":Dropbox:Operations:Phone Orders:"


ActiveWorkbook.SaveAs Filename:=FPath & FName & ".csv", FileFormat:=xlCSV


End Sub

Display More

Re: Find Dropbox Path on OS X using Macro in Excel 2011

Hi,

I have been trying to get this to work and tried both these approaches but both give the same result of "Run-time error '5': Invalid procedure call or argument"

Sub SaveItAsCSV()
    Dim FName As String, FPath As String, Username As String
        
    FName = ActiveWorkbook.Sheets("CUSTOMER").Range("C12").Value
    Username = MacScript("whoami")
    FPath = "Macintosh HD:Users:" & Username & ":Dropbox:Operations:Phone Orders:"
     
    ActiveWorkbook.SaveAs Filename:=FPath & FName & ".csv", FileFormat:=xlCSV
End Sub

Sub SaveItAsCSV()
    Dim FName As String, FPath As String, Username As String
    
    FName = ActiveWorkbook.Sheets("CUSTOMER").Range("C12").Value
    Username = MacScript("who | grep console | awk '{print $1}'")
    FPath = "Macintosh HD:Users:" & Username & ":Dropbox:Operations:Phone Orders:"
     
    ActiveWorkbook.SaveAs Filename:=FPath & FName & ".csv", FileFormat:=xlCSV
End Sub

What am I doing wrong? :hammerhe:

Re: Find Dropbox Path on OS X using Macro in Excel 2011

Tidied it up a bit more, but I'm getting the original 1004 error now...

Sub SaveItAsCSV()
    Dim FName As String, FPath As String
        
    FName = ActiveWorkbook.Sheets("CUSTOMER").Range("C12").Value
    FPath = MacScript("get path to home folder") & "Dropbox:Operations:Phone Orders:"
     
    ActiveWorkbook.SaveAs Filename:=FPath & FName & ".csv", FileFormat:=xlCSV
    
End Sub

It's highlighting the last line when I debug it. WHAT is going on??! Please can ANYONE give me a clue as to what is wrong?

Re: Find Dropbox Path on OS X using Macro in Excel 2011

Quote from BeckeyB;773513

Tidied it up a bit more, but I'm getting the original 1004 error now...

It's highlighting the last line when I debug it. WHAT is going on??! Please can ANYONE give me a clue as to what is wrong?

Right, I'm getting to the bottom of this last one, The code

MacScript("get path to home folder")

then returns

Quote

alias "Macintosh HD:Users:Office2-Spare:"

which is being added as the text string.

I need to get rid of the alias and quotes bit and just retain the value. Is that possible?

Re: Find Dropbox Path on OS X using Macro in Excel 2011

GOT IT!

Sub SaveItAsCSV()


Dim FName As String, FPath As String, Username As String
     
   FName = ActiveWorkbook.Sheets("CUSTOMER").Range("C12").Value
 
    ActiveWorkbook.SaveAs Filename:=MacScript("Set thePath to (get path to home folder) as string") & "Dropbox:OPERATIONS:Phone orders:" & FName & ".csv", FileFormat:=xlCSV


End Sub

At last! What a slog, but at least I learned a bit on the way!