1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Send a file to the dropbox with a macro

Discussion in 'VBA Macros' started by stefanoste78, Nov 8, 2018.

  1. stefanoste78

    stefanoste78 Member

    Messages:
    128
    On dropbox there is the possibility of requesting files by creating a link for sending.

    There is someone who already has experience in sending files with this system automatically with a macro.

    the link I generated for sending is:
    https://www.dropbox.com/request/dA4PwiYqMIc9pXRN4vGf
  2. stefanoste78

    stefanoste78 Member

    Messages:
    128
    Can be this the code:

    Code (vb):
    'THIS REQUIRES A REFERENCE TO MICROSOFT XML, V2.6 OR HIGHER
    'In VBA, go to Tools, References, then select the Microsoft XML from the list
    'store the folder as a variable so you don't have to recheck it each time
    Public strDropBoxFolder As String
    'This locates and returns the full path of the DropBox folder
    Public Function DropBoxFolder() As String
    Dim DBhost As String
    Dim strInput As String
    Dim DBPath As String
    On Error GoTo Error_Handler
    'If the folder has already been found, don't recheck it, just use the stored variable
    If strDropBoxFolder <> "" Then
    DropBoxFolder = strDropBoxFolder
    Exit Function
    End If
    'otherwise, go find it.
    ' find the host.db file
    DBhost = CStr(Environ("USERPROFILE") & "\AppData\Roaming\Dropbox\host.db")
    If Dir(DBhost) <> "" Then
    Open DBhost For Input Access Read As #1
    'read the host.db file
    Line Input #1, strInput
    Close #1
    'decode from Base64
    strInput = StrConv(DecodeBase64(strInput), vbUnicode)
    'ignore the first line and extract the file path by looking drive name such as C:\
    DBPath = Mid(strInput, InStr(strInput, ":\") - 1)
    'return the result
    DropBoxFolder = DBPath
    'store the result for future use
    strDropBoxFolder = DBPath
    Else
    'file not found and error are handled the same way
    Error_Handler:
    On Error Resume Next
    strDropBoxFolder = ""
    End If
    End Function
    Private Function DecodeBase64(ByVal strData As String) As Byte()
    Dim objXML As MSXML2.DOMDocument
    Dim objNode As MSXML2.IXMLDOMElement
    ' help from MSXML
    Set objXML = New MSXML2.DOMDocument
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.Text = strData
    DecodeBase64 = objNode.nodeTypedValue
    ' thanks, bye
    Set objNode = Nothing
    Set objXML = Nothing
    End Function
  3. stefanoste78

    stefanoste78 Member

    Messages:
    128
  4. charlesdh

    charlesdh Member

    Messages:
    73
    Hi,
    If you have "Dropbox" on your desk top and have it in your system tray.
    See images on how to set it up.
    I recorded a macro that will save the file to "DropBox".
    You need to change this code to your system.
    Code (vb):

    Sub dropboxTest()
    '
    ' Macro1 Macro recorded''

        ChDir "C:\Users\charlesharmon\Dropbox"
        ActiveWorkbook.SaveAs Filename:= _
            "C:\Users\charlesharmon\Dropbox\Booktestdropbox1.xlsm", FileFormat:= _
            xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End Sub
     
    You need to be logged in "DropBox".
    Charles

    Attached Files:

  5. stefanoste78

    stefanoste78 Member

    Messages:
    128
    Thank you for your availability.
    My goal is to make sure that multiple users can automatically send me the processed results of a macro that I will send to them via email.
    I understood that it is not possible to directly send the extracted data to a master file on the claud dropbox etc. It is possible to do this with a database and sql but you need specific knowledge.
    The alternative is to send the file directly or with a macro that uses the dropbox file request link or with an ftp server.
    I believe that a macro that performs a similar task is more difficult to create a macro that extrapolates data from web pages.
  6. vletm

    vletm Excel Ninja

    Messages:
    4,661
    stefanoste78
    Why Your goal is to send files direct to Dropbox?
    Why You won't sync files from Your computer to Dropbox?
    ( as well as term 'You' could be figure as 'multiple users'. )
  7. stefanoste78

    stefanoste78 Member

    Messages:
    128
    Well! Thanks also to you for the answer.
    The system that I would like to create is composed of 2 parts:
    1) users start the macro that makes calculations and then send the file to my online dropbox;
    2) another macro moves the user files (which were sent to my dropbox) from dropbox to a folder on my desktop, in such a way, to free the dropbox memory.
    The reasons why I would like the user files to be automatically saved on my dropbox:
    1) not all users could know how to send a file to the dropbox;
    2) some may not want to do it because it could be complex depending on their knowledge;
    3) Automation with a macro is always better than any manual procedure.
  8. vletm

    vletm Excel Ninja

    Messages:
    4,661
    stefanoste78
    As I wrote ...
    If Dropbox works as OneDrive ...
    then all users could use those files as from own computer
    Then no need to send any files anywhere!
    ... or why to use Dropbox at all? Why not use email to send file?

    2nd 1) if someone don't know ... then someone would teach how.
    2nd 2) if someone don't want ... then someone would motivate someone
    2nd 3) as written above ... why to use that way with Dropbox?
    Automation would be useful ... if it would work.
  9. stefanoste78

    stefanoste78 Member

    Messages:
    128
    Not everyone is ready to learn, especially those who are elderly. I can not learn how to create macros :)
    You wrote well ... if you can automate this system it would be ideal. It seems strange not being able to upload a file, starting from a link. Perhaps the dropbox system is closed.
  10. vletm

    vletm Excel Ninja

    Messages:
    4,661
    stefanoste78
    Age is only a number!
    Why everyone should create macros for this case?
    As I tried to wrote...
    I've use OneDrive ... not Dropbox.
    Of course, those files would use 'in OneDrive' but
    it has been much more smoother to use files from own computer.
    And those files could use even with out connection to OneDrive.
    Nothing won't be strange! Everywhere are rules, which would tell how to work!
    > You didn't answer questions ... hmm?
  11. charlesdh

    charlesdh Member

    Messages:
    73
    Hi,
    Here's another 2 cent msg.
    If the people have a file that you provided and it contains a macro to update the data. You then want the result sent to you. Why not put in the Macro so that it sends the file to you vie Email when they click a command button "Done" ?

    Charles
  12. stefanoste78

    stefanoste78 Member

    Messages:
    128
    Right observation Charles.
    I thought about this solution, but the use of the email has limitations of sending (200 per day). If I had to exceed this limit my email would be blocked.
  13. stefanoste78

    stefanoste78 Member

    Messages:
    128
    To respond to vletm.
    I do not need to use Dropbox, I could use google drive, onedrive or others.
    I am interested in a system that automatically transfers data from any user who will use the macro to me, avoiding any kind of obstacle, even linked to people
  14. vletm

    vletm Excel Ninja

    Messages:
    4,661
    stefanoste78
    Okay, You answered about email-possibility finally ...
    How many emails or files would need to send or something per day?

    Do You need as one row per 'file' or many?
    Do You add or how do You work with those 'sendings' to the master file or what?

    Have You tried to use eg DropBox as it would use as I've tried to explain/ask?
    ... like from own computer?
  15. alphamax

    alphamax New Member

    Messages:
    8
  16. charlesdh

    charlesdh Member

    Messages:
    73
    Hi,
    Will the people that you need the files from be using the same file you send to them for updates?
    Would it be possiple to post a file?


    Charles

Share This Page