Export data from Listbox to Excel

Question

I am attempting to copy all the data from a listbox into excel (ideally I would just like to copy it to the clipboard but unsure how)

Anyway, below is my code that's throws our this error:

User-defined type not defined

Code Below:

    Dim oExcel As Excel.Application                                                   ' Excel Application
Set oExcel = New Excel.Application                                                ' Start it
oExcel.Workbooks.Open "J:\Book2.xlsx"   ' **** CHANGE NAME HERE **** Open it.

On Error GoTo kill_task
Col = Listbox31.ColumnCount                                                        ' Number of Columns
Row = Listbox31.ListCount                                                          ' Number of Rows


For c = 1 To UBound(Col)                                                          ' For each Column
    For L = 1 To UBound(Row)                                                      ' in Each Line
         oExcel.Cells(j, i) = Listbox31.List(j - 1, i - 1)                         ' Write the value for Line, Columns
    Next L                                                                        ' Next Line
Next c                                                                            ' Next Col


       oExcel.ActiveWorkbook.Save                                                 ' Save
       oExcel.Workbooks(1).Close                                                  ' Close Workbook
       oExcel.Application.Quit                                                    ' Close Application
Exit Function

kill_task:
      oExcel.ActiveWorkbook.Save                                                  ' Save
      oExcel.Workbooks(1).Close                                                   ' Close Workbook
      oExcel.Application.Quit                                                     ' Close Application
End Function

You haven't declared a whole lot of variables. c, L, j, i, Col, Row are all undeclared. My guess is you are using j and i before you declare them or set them to anything. Make sure you are using Option Explicit, it saves time debugging. — MoondogsMaDawg
– MoondogsMaDawg, Commented Dec 15, 2016 at 16:00

Minty · Accepted Answer · 2016-12-15 16:37:32Z

You can copy data to the clipboard using the code below - It's not mine I found on the web some time ago. Paste it into a new module.

Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
   ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
   As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
   ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
   As Long, ByVal hMem As Long) As Long

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

Function ClipBoard_SetData(MyString As String)
   Dim hGlobalMemory As Long, lpGlobalMemory As Long
   Dim hClipMemory As Long, X As Long

   ' Allocate moveable global memory.
   '-------------------------------------------
   hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

   ' Lock the block to get a far pointer
   ' to this memory.
   lpGlobalMemory = GlobalLock(hGlobalMemory)

   ' Copy the string to this global memory.
   lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

   ' Unlock the memory.
   If GlobalUnlock(hGlobalMemory) <> 0 Then
      MsgBox "Could not unlock memory location. Copy aborted."
      GoTo OutOfHere2
   End If

   ' Open the Clipboard to copy data to.
   If OpenClipboard(0&) = 0 Then
      MsgBox "Could not open the Clipboard. Copy aborted."
      Exit Function
   End If

   ' Clear the Clipboard.
   X = EmptyClipboard()

   ' Copy the data to the Clipboard.
   hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:

   If CloseClipboard() = 0 Then
      MsgBox "Could not close Clipboard."
   End If

   End Function

To use simply put ClipBoard_SetData (strYourString) in your VBA. Make sure you don't call the module the same as the function.

Collectives™ on Stack Overflow

Export data from Listbox to Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related