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

0
votes

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.

1
excelvbaspreadsheetuserform
You can press a Button on the form and an Open dialog will show, announcing whatever you need ("Please open the xlsx file able to ..."). If you know the folder where the workbook usually is saved, the dialog can be configured to start from that specific folder... - FaneDuru
That was my original design, but this project is to automate a existing process and users already have their habits. As of now, they open the workbook with the datas to be transferred, copy/paste the cells into a sheet of another excel and discard the data workbook without storing it. That's why I switched to asking the user to open the data workbook and let the code perform the transfert (it works without storing the data workbook). - Groumpf
When you say ”it works without storing the data workbook", do you mean they open the workbook directly from the email message? - FaneDuru
It is not complicated to use a TreeView control and configure it to accept manual Drag and Drop. I did that, but it worked only dropping files from folders. Microsoft Oulook mail used to allow drag and drop, but for some years (security reason...) it doesn't. What mail application do you use? You may test the treeView and see if it allows that. Practically, you must right click on the toolBox and select 'Additional Controlls' then you check 'Microsoft TreeView Control, version...' and press OK. Now you can drag and drop the control on your form. There is good stuf here about the settings. - FaneDuru
It is available for both versions. I am using Office 2016 64 bit and the control in discussion exist. What kind of Office/Excel do you use? It is 'Microsoft TreeView Control, version 6.0`... How many options do you have in your Additional Controls list? - FaneDuru

1 Answers

1
votes

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...