How to transcribe VBA to VB.NET

See more:

VB

VBA

I have been working on transcribing a vba code into a usable vb.net code.

Here is the VBA code I am working with:

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
 
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("folder of excel docs")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
 
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A7:IV" & Range("A1048500").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close savechanges:=False


Next
End Sub

This is what I have come up with so far:

Private Sub excelmerger()

        Dim dialog As New FolderBrowserDialog()
        dialog.RootFolder = Environment.SpecialFolder.DesktopDirectory
        dialog.Description = "Select Application Configeration Files Path"
        dialog.ShowNewFolderButton = False

        dialog.ShowDialog()

        Dim workb As Excel.Workbooks
        Dim exapp As New Excel.Application
        Dim bookList As Microsoft.Office.Interop.Excel.Workbook
        Dim ThisWorkbook As Excel.Workbook = exapp.Workbooks.Add()

        Dim mergeObj As Object = CreateObject("Scripting.FileSystemObject")
        Dim dirObj As Object = mergeObj.Getfolder(dialog.SelectedPath)
        Dim filesObj As Object = dirObj.Files
        Dim everyObj As Object
        bookList.Application.ScreenUpdating = False

        'change folder path of excel files here
        For Each everyObj In filesObj
            bookList = workb.Open(everyObj)

            'change "A2" with cell reference of start point for every files here
            'for example "B3:IV" to merge all files start from columns B and rows 3
            'If you're files using more than IV column, change it to the latest column
            'Also change "A" column on "A65536" to the same column as start point
            Dim exr As Excel.Range
            exr = exapp.Range("A7:IV" & exapp.Range("A1048500").End(Excel.XlDirection.xlUp).Row).Copy
            ThisWorkbook.Worksheets(1).Activate

            'Do not change the following column. It's not the same column as above
            exapp.Range("A65536").End(Excel.XlDirection.xlUp).Offset(1, 0).PasteSpecial()
            bookList.Application.CutCopyMode = False
            bookList.Close()

            'bookList.Save
            ' SaveChanges:=False

        Next
    End Sub

at the Dim ThisWorkbook As Excel.Workbook = exapp.Workbooks.Add() I keep getting a null object reference error. Not sure why.

As this is my first time doing this I was hoping to get some help.

Thanks in advance

What I have tried:

I have researched quite a few articles about vba to vb.net. As you can imagine most do not give great detail how to do this type of project.