Results 1 to 13 of 13

Thread: How to *drag and drop files to Excel* to get their names?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    341

    How to *drag and drop files to Excel* to get their names?

    Need to VBA my way out

    Os: win7 or winxp
    From: win explorer
    To: an open workbook of excel
    File type: any
    Aim: to get the full file path string to the activesheet.

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: How to *drag and drop files to Excel* to get their names?

    Mission: unclear

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to *drag and drop files to Excel* to get their names?

    you would have to use an API getcommandlineargs, though i believe there is some bug with this and need to use getcommandlineargsex

    i remember doing an example on this some years ago, i will see if i can find a link
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: How to *drag and drop files to Excel* to get their names?

    Pete & Bryce,

    I think the OP wants to be able to drag & drop files onto a worksheet, but only extract the name of the dropped file and add that string to the Worksheet.

    AFAIK, there is no drag&drop support available except for the ActiveX (MSForms) controls. The MSForms DataObject only supports a GetText function and has no support for the "FileDrop" format.

    It may be possible to write a control in VB.Net that could be added to the worksheet and used as a drop target.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    341

    Re: How to *drag and drop files to Excel* to get their names?

    Quote Originally Posted by TnTinMN View Post
    I think the OP wants to be able to drag & drop files onto a worksheet, but only extract the name of the dropped file and add that string to the Worksheet.
    .
    Sorry for my unclear mission description but TnTinMN is right to the point. However after reading through the post the chance of coming up a solution seems to be slim.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to *drag and drop files to Excel* to get their names?

    if you create an application with events object
    you can run an ontime procedure from the application workbook open event, that will return the name of any file dropped onto an open excel window, assuming you only want the file name, you can also close the newly opened workbook

    if the file dropped onto excel can not be opened by excel, an error will occur, the filename will still be returned, but i was unable to find any way to prevent the error dialog

    Code:
    Public Sub test()
    Set wb = ActiveWorkbook
    mypath = wb.FullName
    wb.Close False
    MsgBox mypath
    Application.DisplayAlerts = True
    End Sub
    test is called from an ontime statement
    you can do what ever you want with the returned result instead of the messagebox
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    341

    Re: How to *drag and drop files to Excel* to get their names?

    Dear Pete, would you specify "if you create an application with events object". Please give me more details so I can run tests on your code. I know there is the "open" event for excel.workbook but not sure how it is made use of for getting names of the dropped-in files.

    Thank you

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to *drag and drop files to Excel* to get their names?

    "if you create an application with events object"
    with events variables must be dimensioned in object modules, not general modules, best to use the thisworkbook module

    Code:
    Public WithEvents app As Application
    Private Sub app_WorkbookOpen(ByVal wb As Workbook)
    Application.OnTime Now + TimeSerial(0, 0, 1), "test"
    End Sub
    the 1 second delay seems to be essential, for a lot of code run in workbook open, you are welcome to test skipping this and put all the code directly into this procedure

    somewhere before you need to use the app object you must set it to the application
    Code:
    Set app = Application
    this can be in the workbook open event of the workbook containing the code, or from any other event or run manually

    I know there is the "open" event for excel.workbook
    this can not be used directly for what you are wanting to do, as it only works for the workbook containing the code, that is why you need to use events of the application object

    Note: if your code does not finish running in any procedure, for any reason the app object will loose its reference and need to be again set to the application, so appropriate error handling should be used for all code run in that instance of excel, else remember to run the initialising code again
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: How to *drag and drop files to Excel* to get their names?

    Quote Originally Posted by westconn1 View Post
    ...will return the name of any file dropped onto an open excel window, assuming you only want the file name, you can also close the newly opened workbook
    Pete,

    I tried your technique and for many file types it appears to work, but it fails for images (bmp, jpeg, tiff). It appears that if the file is a type that Excel can open through the Open dialog that it will work and this makes sense to me. It also works for file types that are in reality text files (i.e.: .reg).

    Did you have success with image files?

    I have created an ActiveX control in VB.Net that can be placed on a worksheet and used as drop target; the control exposes a DroppedFile event that passes the name of the file. If anyone is interested in trying that route, I can upload it. You will need Visual Studio to compile the code as forum rules preclude uploading executables.

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to *drag and drop files to Excel* to get their names?

    Did you have success with image files?
    i had not tested any, and no they do not work, excel must treat them differently

    have tested with most types of text files, dat file exe file zip file, and some others, the last 2 listed give error, but still return file path
    your solution is probable better, but i do not feel like downloading VS just to test, down side would be that the sheet with the control would need to be topmost, pity can not do with toolbar button, maybe one can be subclassed?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: How to *drag and drop files to Excel* to get their names?

    Quote Originally Posted by westconn1 View Post
    ... down side would be that the sheet with the control would need to be topmost, pity can not do with toolbar button, maybe one can be subclassed?
    I have no clue how to make it a toolbar button. However, it can be placed on a UserForm and the UserForm could be shown modeless.

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to *drag and drop files to Excel* to get their names?

    lots of choices
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13
    Registered User
    Join Date
    Jan 2015
    Posts
    6

    Re: How to *drag and drop files to Excel* to get their names?

    I always wish that this can be just a drop and play function in Microsoft Office to make it more simpler and easier.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width