Click here to Skip to main content
15,881,139 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Dear Experts,
I observed the needed code in vba excel but I cannot move it to visual studio and combine as separate exe-application. Can you help me?

What I have tried:

VB
'Procedure is called when files are dropped to the TreeView in the user form
Private Sub ExportTreeView_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, X As Single, Y As Single)
    Dim fso As New FileSystemObject
    Dim fld As Folders
    Dim fls As Files
    Dim sFileName As String
    Dim sFilePath As String
    Dim iTotalTreeElems As Integer
    Dim sFilesArray() As String
    Dim iTotalFiles As Long
    Dim sBufArray(1000) As String
    Dim sBufSize As Integer
    Dim sRepName As String
    
    Dim iRows As Integer
    Dim sActiveSheet As String
    Dim sKey As String
    Dim sMask As String
    Dim sStr As String
    Dim i As Integer
    Dim j As Integer
    
    Application.ScreenUpdating = False
    sActiveSheet = ThisWorkbook.ActiveSheet.Name
    
    
    
    ThisWorkbook.Sheets("UserForm").Select
    sKey = "" 'ExportForm.edtFilterKey.Text
    iRows = Application.WorksheetFunction.CountA(Range("A:A"))
    iTotalTreeElems = Data.Files.Count
    iTotalFiles = 0
    sBufSize = 0
    
    'Get list of files from all folders and subfolders
    For i = 1 To iTotalTreeElems
        
        'Check if dir is folder or filename
        If (fso.FolderExists(Data.Files(i)) = True) Then
            Call General.GetFileList(sFilesArray, Data.Files(i))
        Else
            iTotalFiles = General.GetArraySize(sFilesArray)
            ReDim Preserve sFilesArray(iTotalFiles)
            sFilesArray(iTotalFiles) = Data.Files(i)
        End If
    Next i
    
    iTotalFiles = General.GetArraySize(sFilesArray)
    
    'Go through the list of neccessary files and identify if files suits any of them by mask
    For i = 2 To iRows
        sMask = ThisWorkbook.ActiveSheet.Cells(i, 4)
    
        'iTotFiles = Data.Files.Count
        For j = 1 To iTotalFiles
            
            If sFilesArray(j - 1) <> "" Then
                sFileName = fso.GetFileName(sFilesArray(j - 1))
                sFilePath = fso.GetParentFolderName(sFilesArray(j - 1))
                
                'Check if file match current mask
                If General.CheckMask(sFileName, sMask) = True Then
                    'ThisWorkbook.ActiveSheet.Cells(i, 3) = sFileName
                    'ThisWorkbook.ActiveSheet.Cells(i, 6) = sFilePath
                    
                    sBufArray(sBufSize) = sFilesArray(j - 1)
                    sBufSize = sBufSize + 1
                    
                    'sFilesArray(j - 1) = ""
                    
                    'Data.Files.Remove (j - 1)
                    'Exit For
                End If
            End If
        Next j
        
        sKey = RemoveFiltrSymbols(sKey)
        
        'If more than one file matched Mask then selecet one that has key word
        If sBufSize > 1 Then
            For j = 1 To sBufSize
                sRepName = RemoveFiltrSymbols(sBufArray(j - 1))
                
                If InStr(UCase(sRepName), UCase(sKey)) Then
                    ThisWorkbook.ActiveSheet.Cells(i, 3) = fso.GetFileName(sBufArray(j - 1))
                    ThisWorkbook.ActiveSheet.Cells(i, 6) = fso.GetParentFolderName(sBufArray(j - 1))
                End If
            Next j
        Else
            If sBufSize = 1 Then
                ThisWorkbook.ActiveSheet.Cells(i, 3) = fso.GetFileName(sBufArray(0))
                ThisWorkbook.ActiveSheet.Cells(i, 6) = fso.GetParentFolderName(sBufArray(0))
            End If
        End If
        
        sBufSize = 0
    Next i
    
    ThisWorkbook.Sheets(sActiveSheet).Select
    
    Application.ScreenUpdating = True
    
End Sub
Posted
Updated 17-Sep-18 3:15am
v2
Comments
RedDk 15-Sep-18 16:07pm    
Here's how I'd go about doing exactly what it is you wish to do -> convert a VBA "program" into an executable running with methods exposed and accessible through VBNET but it's going to take some work on your part. Open the Excel app and on the Developer tab select the Visual Basic editor (Alt+F11) and launch the interface. Find your VBA code block (as above, we'll assume the editor is where you found that block) and with the code under focus and once visible in the workspace, select Object Browser (F2) to make sure that window is open also. You'll see in the Object Browser a bunch of things not the least of which is the libraries under use in the project, the classes, member classes, etcetera ... and you can choose any of the project libraries in the drop down and see the methods and classes change beneath. This is the key place to begin finding the functionality that crosses over to VBNET.

Putting your cursor on any keyword in your code block and hitting F1, say GetFileList, will either result in an Excel Help item or "Keyword not found" but in whichever case, links at the top of help page, say to "Excel XXXX developer Reference" might provide more insight. I'd suggest finding a code block entry which does have a keyword entry ... and start there. You have to set some breakpoints and step through you VBA to see what works. And ususally something like an error message will mean that a library is missing so you'll have use the Object Browser to find a similar keyword and figure out what that library might be then include by adding it to References (right context click - popup to see the list of potential online libs available).

In short, after you've figured out that your Excel code works as expected by stepping through it you'll have the first bit if insight into the functionality which will (inevitably) be available in VS vbnet. 'Almost guarantee the code's in VS VBNET, in some framework incarnation or system core library ... and THEN SOME.

There's no other way, my friend.

It doesn't quite work like that.
We do not do your work for you - we are not a code translation service.
If you want someone to write your code, you have to pay - I suggest you go to Freelancer.com and ask there.

But be aware: you get what you pay for. Pay peanuts, get monkeys.

The idea of "development" is as the word suggests: "The systematic use of scientific and technical knowledge to meet specific objectives or requirements." BusinessDictionary.com[^]
That's not the same thing as "have a quick google and give up if I can't find exactly the right code".
So either pay someone to do it, or learn how to write it yourself. We aren't here to do it for you.
 
Share this answer
 
VBA is not the same as VB.NET for set of reason!

If you understand what above code does, you can write it from scratch using .NET technology. Please, see MSDN documentation: How to add TreeView drag-and-drop functionality in a Visual Basic .NET or Visual Basic 2005 application[^]

Another very interesting article you'll find on CP KnowledgeBase: Introduction to TreeView Drag and Drop (VB.NET)[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900