CODEWITHSUNDEEP
excelvbaspreadsheetuserform
Groumpf
Groumpf

Reputation: 31

Adding a sheet or data from one workbook to another via variable

I have this project which main purpose is to modify and use an excel sheet. Everything is done through userform for the sake of user-friendliness. The sheet to be imported will be in a workbook sent by an external source, by different means (mail, retrieved from the network...) and will have a different name each time. I'm looking for the most user friendly way to get the data from this sheet.

The most user friendly way I could figure, is to temporarily hide the userforms and ask the user to open the workbook from which the datas are needed. Then, to have the code copy the sheet I need among my main project sheets (if it passes a series of tests, to be certain it's the right one and not some other randomly opened workbook).

I was looking for a more intuitive solution, like drag'n drop the workbook on a userform of my project. I'm not certain it can be done, I was reading about the need of treeview, which according to some source doesn't support 64, and according to others works fine. I also read about a JPK replacement for treeview, but I'm not sure how this replacement can be considered as a safe option on the long term.

Any hints ? I'm looking for a solution without really knowing what to look for.

Upvotes: 0

Views: 53

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

First of all, I would like to say that I am not the 'father' of the next solution. I have it in my collection of useful pieces of code and tips... I do not remember from where I picked it up and adapted according to my needs.

  1. Add a reference to Microsoft Windows Common Controls 6.0 ... This can be done from IDE -> Tools -> References...

  2. Place a TreeViewcontrol on your form. Usually your ToolBox doesn't show it. So, after right click on the ToolBox, the option Additional Controls must be chosen. Then it is only necessary to check the Microsoft TreeView Control, version... and press OK.

  3. Drag the new control on your form and press F4. In the property window which will show up, you must set 'OLEDropMode' as OLEDropManual. Press Enter, select the form and press Save.

  4. Create a variable at the form module level (on top of it, at its declarations area). Not mandatory to declare it there, but it would be good to have it there for future use, if necessary. It happened I needed it there...

    Private strExcelWPath As String

  5. Double click on the TreeView control (default - TreeView1) and open, in this way its events code. Press the right top little down arrow and choose OLEDragDrop event. It will create the next event code:

    Private Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)

    End Sub

    You must insert the next code lines (or similar) and it becomes like that:

    Private Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single) strExcelWPath = Data.Files(1)
    Debug.Print strExcelWPath 'just checking of what it returns droppedWorkbookProcess strExcelWPath 'calling the sub able to process the workbook End Sub

  6. Your function able to process the dropped workbook path must look like this:

    Sub droppedWorkbookProcess(strFullName As String) 'Here you place the processing code, using of passed strFullName parameter End Sub

  7. The code can be adapted for multiple selection, also, if necessary...

Upvotes: 1

Related Questions