-
Jan 24th, 2015, 10:20 AM
#1
Thread Starter
Hyperactive Member
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.
-
Jan 24th, 2015, 06:56 PM
#2
Re: How to *drag and drop files to Excel* to get their names?
-
Jan 24th, 2015, 07:53 PM
#3
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
-
Jan 24th, 2015, 10:44 PM
#4
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.
-
Jan 25th, 2015, 12:16 AM
#5
Thread Starter
Hyperactive Member
Re: How to *drag and drop files to Excel* to get their names?
Originally Posted by TnTinMN
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.
-
Jan 25th, 2015, 01:25 AM
#6
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
-
Jan 25th, 2015, 01:49 AM
#7
Thread Starter
Hyperactive Member
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
-
Jan 25th, 2015, 03:47 AM
#8
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
-
Jan 25th, 2015, 01:27 PM
#9
Re: How to *drag and drop files to Excel* to get their names?
Originally Posted by westconn1
...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.
-
Jan 25th, 2015, 06:09 PM
#10
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
-
Jan 25th, 2015, 08:49 PM
#11
Re: How to *drag and drop files to Excel* to get their names?
Originally Posted by westconn1
... 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.
-
Jan 25th, 2015, 09:51 PM
#12
Re: How to *drag and drop files to Excel* to get their names?
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
-
Jan 25th, 2015, 10:43 PM
#13
Registered User
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|