How to use VBA to dump data into an empty Excel Workbook

Hello friends,

From time to time I find myself in a situation where I want to copy data from a VBA enabled application into Excel.

In this particular case, I needed to export the Operation list from an Esprit CAM program into a blank workbook.

Press Alt+F11 to open the VBA editor.

First, add a reference to the Microsoft Excel Object Library:

add reference

Then paste this code into a new module:

Dim ExcelInstance As Excel.Application
Dim NewWorkbook As Excel.Workbook
Dim NewWorksheet As Excel.Worksheet

Public Sub OpenExcel()
    Set ExcelInstance = CreateObject("Excel.Application")
    Set NewWorkbook = ExcelInstance.Workbooks.Add
    Set NewWorksheet = NewWorkbook.Sheets(1)
    ExcelInstance.Visible = True
End Sub

Public Sub ExportOps()
    Dim NewTable As ListObject
    Dim NewRange As Range
    Dim i As Integer
    
    ' Run the function which opens our new workbook
    OpenExcel

    ' For each Operation assign the value of operation.key and operation.name to column a and b 
    ' in row number i, starting at row number 1
    For Each EspOp In Document.Operations
        With NewWorksheet
            .Cells(1 + i, 1) = EspOp.Key
            .Cells(1 + i, 2) = EspOp.Name
        End With
        i = i + 1
    Next

    ' Turn our new range into a table
    Set NewRange = Range("A:B")
    Set NewTable = ActiveSheet.ListObjects.Add(xlSrcRange, NewRange, , xlYes)
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *