By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,844 Members | 1,889 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,844 IT Pros & Developers. It's quick & easy.

list box to table

P: 3
i have a problem, i need copy the data inside of a listbox to a table,the list box give 5 columns and 100 rows,my advance was a textbox how show the data of the listbox but only show the first column any idea?

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  3. Private Sub Busca_AfterUpdate()
  4. Select Case Me.Busqueda
  6. 'optstd
  7.     Case Is = "2"
  8.     Select Case Me.Lista1
  9.     Case Is = "StyleID"
  10.     Me.Lista0.RowSource = "SELECT optstd.StyleID, optstd.Category, optstd.CategoryHeader, optstd.UserFriendlyName, optstd.FeatureType, optstd.State1, optstd.State2 FROM optstd where [optstd].[StyleID]like '*" & Busca.Text & "*' ORDER BY [StyleID] ASC;"
  11.     Case Is = "Category"
  12.     Me.Lista0.RowSource = "SELECT optstd.StyleID, optstd.Category, optstd.CategoryHeader, optstd.UserFriendlyName, optstd.FeatureType, optstd.State1, optstd.State2 FROM optstd WHERE [optstd].[Category] like '*" & Busca.Text & "*' ORDER BY [optstd].[Category] ASC;"
  13.     Case Is = "CategoryHeader"
  14.     Me.Lista0.RowSource = "SELECT optstd.StyleID, optstd.Category, optstd.CategoryHeader, optstd.UserFriendlyName, optstd.FeatureType, optstd.State1, optstd.State2 FROM optstd WHERE [optstd].[CategoryHeader] like '*" & Busca.Text & "*' ORDER BY [optstd].[CategoryHeader] ASC;"
  15.     Case Is = "UserFriendlyName"
  16.     Me.Lista0.RowSource = "SELECT optstd.StyleID, optstd.Category, optstd.CategoryHeader, optstd.UserFriendlyName, optstd.FeatureType, optstd.State1, optstd.State2 FROM optstd WHERE [optstd].[UserFriendlyName] like '*" & Busca.Text & "*' ORDER BY [optstd].[UserFriendlyName] ASC;"
  17.     Case Is = "FeatureType"
  18.     Me.Lista0.RowSource = "SELECT optstd.StyleID, optstd.Category, optstd.CategoryHeader, optstd.UserFriendlyName, optstd.FeatureType, optstd.State1, optstd.State2 FROM optstd WHERE [optstd].[FeatureType] like '*" & Busca.Text & "*' ORDER BY [optstd].[FeatureType] ASC;"
  19.     Case Is = "State1"
  20.     Me.Lista0.RowSource = "SELECT optstd.StyleID, optstd.Category, optstd.CategoryHeader, optstd.UserFriendlyName, optstd.FeatureType, optstd.State1, optstd.State2 FROM optstd WHERE [optstd].[State1] like '*" & Busca.Text & "*' ORDER BY [optstd].[State1] ASC;"
  21.     Case Is = "State2"
  22.     Me.Lista0.RowSource = "SELECT optstd.StyleID, optstd.Category, optstd.CategoryHeader, optstd.UserFriendlyName, optstd.FeatureType, optstd.State1, optstd.State2 FROM optstd WHERE [optstd].[State2] like '*" & Busca.Text & "*' ORDER BY [optstd].[State2] ASC;"
  24.         End Select
  25.     End Select
  27.     'Indica el Nș de registros
  28.     If Me.Lista0.ListCount = 0 Then
  29.     Me.Texto41 = "0"
  30.     Else
  31.     Me.Texto41 = Me.Lista0.ListCount - 1
  32.     End If
  33.     End Sub
  35. Private Sub Comando18_Click()
  37. Dim item As Variant
  38. Dim strTemp As String
  40. strTemp = ""
  41. For Each item In Me.Lista0.ItemsSelected
  42. Debug.Print Me.Lista0.ItemData(item)
  43. strTemp = strTemp & Me.Lista0.ItemData(item) & ","
  44. Debug.Print strTemp
  45. Next
  46. 'Debug.Print strTemp
  47. Me.Texto19 = strTemp
  48. End Sub

Attached Images
File Type: jpg 17-05-2019 01-02-06 p.m..jpg (144.0 KB, 44 views)
File Type: jpg 17-05-2019 02-35-10 0p.m..jpg (137.5 KB, 46 views)
5 Days Ago #1
Share this Question
Share on Google+
7 Replies

Expert Mod 5K+
P: 5,331
I've read your question five or six times now and I'm not exactly sure what you're trying to accomplish.

Looking at your code it appears that you already have the data in your database - you're using that information as the row source?

You also don't indicate if you want all of the information in the listbox or just selected rows.

In a normalized database:
home > topics > microsoft access / vba > insights > database normalization and table structures
you do not usually want to duplicate that information between tables. Perhaps you could give us a very brief explanation about your project and why you want to copy the information out of a listbox into a table - having this information will help us to help you.
5 Days Ago #2

P: 3
I need transport all the information inside of the boxlist to a exel document like a external report
5 Days Ago #3

Expert Mod 5K+
P: 5,331
+ That's a little different than your first post.

+ "Excel Document Like" could mean anything from an actual Excel workbook to a tab or comma delimited file.

+ Given that you want everything in the control, the easiest way I see to do this is to actually pull the SQL or the string array from your control and then push it to the desired location. This way you do not need to actually step thru the control's columns property.

+ How knowledgeable of vba are you?
Writing the CSV text file is very easy from the SQL; however, Excel opens the file directly and in a usable state for a worksheet.
Excel would take a bit more effort to accomplish and would require a bit of application automation.
5 Days Ago #4

P: 3
My knowlege is basic in vba,I would settle for traslate all the columns of the listbox to a textbox, for use a ctrl+v and copy and paste in a exel
2 Days Ago #5

Expert Mod 15k+
P: 31,307
There is no way to Copy from ListBox or ComboBox Control. However, depending on how that data is populated it may well be possible to transfer the required data across to Excel.

It seems, from the code you've included, that you've populated your Control using a SQL recordset. This should make it easier.

Excel has a CopyFromRecordset Method of the Range class.

At this point we've probably reached the limit of what we can do with the information you've posted. Your first post obviously illustrates some effort, but the later posts seem to indicate minimum involvement.

If you can pick it up from here then we can help you. If you're waiting for it all to be done for you then we couldn't if we wanted to at this stage. Too little information. Let's see where we go from here.
2 Days Ago #6

Expert Mod 5K+
P: 5,331
Excel has a CopyFromRecordset Method of the Range class.
I was thinking this would be the way to go if OP was after a native excel and is one I've used quite frequently. :)
2 Days Ago #7

Expert Mod 15k+
P: 31,307
It's largely guesswork as we have little to go on. We'll see what happens next.
2 Days Ago #8

Post your reply

Sign in to post your reply or Sign up for a free account.