Select multiple rows from listbox and export to excel

waq963

Registered User.
Local time
Today, 17:02
Joined
Jan 27, 2009
Messages
84
Hi, Is it possible to select multiple rows in a listbox and export them to excel on dblclick or using another function. I know how to enable multi-select but don't know how i would code the exporting. Thanks
 
Hi, thanks for the reply. I have never used transferspreadsheet so i would really appreciate so guidance if possible. Thanks
 
Hmmm...I'm not sure how to use TransferSpreadsheet from a listbox. I normally use that comomand with a table or query. Hopefully the other poster can give us some sample code.

In the meantime, here's how I would normally do it. (By the way, why would use double-click? Wouldn't such clicking deselect the rows? - I would add another button to the form and click that instead). Let's assumne your LB has two columns firstName and lastname. First you'll need a connection string.

Code:
Dim pathToSheet As String
pathToSheet = "C:\workbook1.xls"
Dim cnExcel As New ADODB.Connection
cnExcel.Provider = "Microsoft.Jet.OLEDB.4.0"
cnExcel.ConnectionString = "Data Source=" & pathToSheet & "; Extended Properties='Excel 8.0; IMEX=0; HDR=Yes'"
cnExcel.Open
Dim Cmd As New ADODB.Command
Set Cmd.ActiveConnection = cnExcel
'Let's assume the workbook does not exist. This will create
'a new workbook with a sheet called Customers.
Cmd.CommandText = "CREATE TABLE Customers (FirstName TEXT(50), LastName TEXT(50))"
Cmd.Execute
Cmd.CommandText = "INSERT INTO [Customers$] (FirstName, LastName) VALUES (@FirstName, @LastName)"
Cmd.Parameters.Refresh
Dim i As Long, rowNo As Long
For i = 0 To LB.ItemsSelected.Count - 1
rowNo = LB.ItemsSelected(i)
Cmd.Parameters("@FirstName").Value = LB.Column(0, rowNo) 'column zero
Cmd.Parameters("@LastName").Value = LB.Column(1, rowNo) 'column 1
Cmd.Execute
Next i
cnExcel.Close
Set Cmd = Nothing
 
Last edited:
Personally, though, if you can afford the extra few seconds that it will take to run the possibly slower code below, I would make the code simpler by putting the records into a temp table. Let's assume your listbox has a column called CustomerID. Maybe something like this:

CurrentDb.Execute "DELETE FROM TempTablE "
Dim i As Long, rowNo As Long
For i = 0 To LB.ItemsSelected.Count - 1
rowNo = LB.ItemsSelected(i)
'Let's assume customer id is the 4TH column of the listbox
CurrentDb.Execute "INSERT INTO TempTable SELECT * FROM Customers WHERE CustomerID = " & LB.Column(3, rowNo)
Next i
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "TempTAble", "C:\Workbook2.xls", True
 
Last edited:
Hi When i try the 2nd solution it says too few parameters? and the 1st solution won't accept 'ADODB' as function? i use Access 2007 by the way. Thanks
 
I use Access 2003 where ADODB is standard. In access 2007 you might need to either use late binding or add a reference to Microsoft ActiveX Data Objects 2.8.

As for "too few parameters" that usually means a misspelling somewhere whereby Access can't matchup a column name or parameter name - paste your code and maybe I can help you find it.
 
By the way make sure you let me know which line of code is throwing the error.
 

Users who are viewing this thread

Back
Top Bottom