Exporting the results of a listbox to Excel

[Widge]

One more question for you! This follows on from this query:

http://www.thescripts.com/forum/thread685289.html

Now I have the search function and everything displays in a listbox correctly, can I get the results of this to export to Excel somehow. Either by opening and populating a spreadsheet or by saving the results to text to be delimted later.

Thanks for any help!

[JKing]

You want everything in the list box to be sent to a text file / excel?

[Widge]

If thats at all possible, yes!

[JKing]

I don't really do much work with excel. Other than direct imports/exports but here's a little something that I've conjured up. It's fairly simple.

So first off you'll need to make a query and save it. It doesn't matter what's in the query as we're just going to alter it but Access won't allow you to make blank queries and save them. So for this example I've called my saved query qselExport.

 
Dim qdef As DAO.QueryDef
 
Set qdef = CurrentDb.OpenQueryDefs("qselExport")
 
'your code for filling the list box here
 
qdef.SQL = Me.ListBox.RowSource
qdef.Close
 
DoCmd.TransferSpreadsheet acExport, , "qselExport", "testExport"
 
Set qdef = Nothing
 

The logic behind this is we change the SQL of our saved query to match that of the list box's rowsource. Then we just do a simple export of the query. You can be more specific with the filename than "testExport" and add a full path otherwise it will just save to my documents.

[Widge]

I don't really do much work with excel. Other than direct imports/exports but here's a little something that I've conjured up. It's fairly simple.

So first off you'll need to make a query and save it. It doesn't matter what's in the query as we're just going to alter it but Access won't allow you to make blank queries and save them. So for this example I've called my saved query qselExport.

 
Dim qdef As DAO.QueryDef
 
Set qdef = CurrentDb.OpenQueryDefs("qselExport")
 
'your code for filling the list box here
 
qdef.SQL = Me.ListBox.RowSource
qdef.Close
 
DoCmd.TransferSpreadsheet acExport, , "qselExport", "testExport"
 
Set qdef = Nothing
 

The logic behind this is we change the SQL of our saved query to match that of the list box's rowsource. Then we just do a simple export of the query. You can be more specific with the filename than "testExport" and add a full path otherwise it will just save to my documents.

Nice, I'll give it a go this afternoon (must make a coffee first). Cheers!

I just want something simple.

I found a database by someone called Tejpal here:

http://www.rogersaccesslibrary.com/OtherLibraries.asp

but it was WAY more than what I wanted to do.

[Widge]

Extra question, it doesn't recognise QueryDef. Is this a resource I have to import?

[JKing]

Make sure you have a reference to the Microsoft DAO 3.6 Object Library. To view references open up the VBA editor then click Tools > References and select the reference.

[Widge]

Make sure you have a reference to the Microsoft DAO 3.6 Object Library. To view references open up the VBA editor then click Tools > References and select the reference.

Ah, it was a bit of a typo it seems. It still doesn't like OpenQueryDefs. Its fine with CreateQueryDef which does create a blank query for you, but I can't get it to delete afterwards.

EDIT:

Just tested the export, that works fine! I just need to get the query deleted afterwards. Code so far:

 
Private Sub Command36_Click()
 
      Dim qdef As DAO.QueryDef
 
      Set qdef = CurrentDb.CreateQueryDef("exportCatalogue")
 
      'your code for filling the list box here
 
      qdef.SQL = Me.List11.RowSource
 
      qdef.Close
 
      DoCmd.TransferSpreadsheet acExport, , "exportCatalogue", Application.CurrentProject.Path & "\testExport"
 
      Set qdef = Nothing
End Sub
 
 

I've tried doing CurrentDb.QueryDefs.Delete ... but I get a bit lost after there.

[JKing]

Try this:

Currentdb.QueryDefs.Delete "exportCatalogue"
 

[Widge]

Try this:

Currentdb.QueryDefs.Delete "exportCatalogue"
 

PERFECT

Thank you v much for being on hand!

[JKing]

You're welcome.

Jking

[Widge]

Argh, ok, more questions.

What can I do when my export data BREAKS the 65000 row Excel limit?

[JKing]

Hey, sorry for not getting back to you sooner. I'm really not sure about the constraints of Excel. Again as I said before I really don't use excel all that much at least I haven't yet. My suggestion is to start a new thread asking for a work around for the 65,000 constraint. I'm guessing you'll probably end up having to break things up into separate sheets. This would require more code than I've provided.

Only thing I can think of is to make your queries more specific so you don't hit that many rows.