• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA ADODB: Inserting records in combobox from another workbook[SOLVED]

inddon

Member
Hello There,

I am using ADODB for extracting data from another excel workbook into a combobox. However the headers do not show up in the combobox. Therefore I came up with the following solution to include the header and '----' separator in the 1st and 2nd row. Then display the actual records.

The problem is that it gets all overwritten with the command:
ActiveSheet.ComboBoxDisplayBurger.Column = rst.GetRows

Below is the code for your reference. Could you please advise how can I retain the 1st 2 inserts?


Looking forward to hearing from you

Regards
Don


Code:
  cn.Open strCon

  strQuery = "SELECT * FROM [Sheet1$A:R] WHERE [Party Name]='" & CBuyerName & _
  "' AND  [VAT Number]='" & CBuyerVATNumber & "';"
  MsgBox strQuery
  Set rst = New ADODB.Recordset
  rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
   

  Dim i As Integer
  rst.MoveFirst
  i = 0

  With ActiveSheet.ComboBoxDisplayBurger
  .Clear
  Do
  .AddItem
  'Include Column Headings. It seems this is a bug that the column headings does not come auto therefore this workaround
  If ColumnHeadingsCount = 0 Then
  .List(i, 0) = "Party Name"
  .List(i, 1) = "Pick-up"
  .List(i, 2) = "Mode"
  .List(i, 3) = "Empty Depot"
  .List(i, 4) = "Local THC Rotterdam"
  .List(i, 5) = "Gross Tonnage"
  .List(i, 6) = "Terminal"
  .List(i, 7) = "Transport"
  '.List(i, 8) = "WHS Xdock"
  '.List(i, 9) = "FTL"
   
  ColumnHeadingsCount = 1
  i = i + 1
  rst.MoveNext
  .AddItem
   
  .List(i, 0) = "----------------------------------------------------------------------"
  .List(i, 1) = "--------------------------"
  .List(i, 2) = "----------------------------------------------------------------------"
  .List(i, 3) = "------------------------------------------------------------"
  .List(i, 4) = "--------------------------"
  .List(i, 5) = "--------------------------"
  .List(i, 6) = "--------------------------"
  .List(i, 7) = "--------------------------"
   
  i = i + 1
  rst.MoveNext
  .AddItem
  End If
   
  ActiveSheet.ComboBoxDisplayBurger.Column = rst.GetRows
  Exit Sub
 
Hi,

Can you upload both the files


Hello Sathish,

I have attached the files for your reference.

In fact, these are your files itself where you had given me this solution for ADODB. I tried to enhance your solution by adding Headers and a bulk insert into the array.

Look forward for your reply.

Thanks & regards
Don
 

Attachments

  • File_Invoice1.xlsm
    24 KB · Views: 7
  • File_Master_Data.xlsx
    9.2 KB · Views: 5
Hi,

Check out the attachement

Instead of Combobox I have used a List view control

This solved our header problem
 

Attachments

  • Adding rst items to List View.zip
    29.6 KB · Views: 11
Hi,

Check out the attachement

Instead of Combobox I have used a List view control

This solved our header problem


Hello Satish,

Thank you for your reply.

When I am at column 7 it gives me an error message:

Run-time error '424'
Object required.

When I say Debug, it takes me to statement:
ListView41.View = lvwReport

when I comment this statement it takes me to all next ListView41 statements


Could you please advise, if something needs to be added or enabled for this functionality to work?

Thanks & regards
Don
 
Hi,

Its working fine for me. Could you upload your file


Hi Sathish,

I have uploaded the files and also the print screens for your reference which includes the print screen of VBA project reference I have enabled in VBA.

I am using Windows 7 64 bit and Office 2010

Thanks & regards
Don
 

Attachments

  • File_Invoice1.xlsm
    23.8 KB · Views: 1
  • File_Master_Data.xlsx
    9.2 KB · Views: 1
  • Error Print Screen.zip
    479.7 KB · Views: 3
Hello Sathish,

Just performed the following to get your solution working:

1. re-registered the component for windows 7 64 bit from command prompt.

regsvr32 c:\windows\syswow64\mscomctl.ocx


2. In the design mode-Insert, selected Microsoft ListView Control 6.0(SP6)
3. Added a Listview and name it Listview41

Thanks a lot for your solution. Learned something new from you. Appreciate your help.

I have a few questions:
1. could see the horizontol scroll bar but not vertical one. Is there a way to get both scroll bars?
2. If there is a null value in one of the columns (in File Master Data), it gives an error message while assigning the data in the array. (eg. Amount1 is null, the error control points to .ListItems(i).SubItems(4) = rst![Amount 1]

Looking forward to hearing from you

Regards
Don
 
Last edited:
Hi,

1) The vertical scroll bar will be auto displayed while the rows increased
2) You can use an error handler like 'On Error resume next
 
Hi,

1) The vertical scroll bar will be auto displayed while the rows increased
2) You can use an error handler like 'On Error resume next


Thanks Sathish,

The On Error resume next worked perfect :), but can't get the vertical scroll bar just doesn't seem to come up. I tried to decrease the height to upto 1 row for Customer ABC. This has 3 rows, but the vert. bar doesn't show up

Any advise? I have attached the jpeg file for your reference

Regards
Don
 

Attachments

  • Scroll Bar.jpg
    Scroll Bar.jpg
    251.6 KB · Views: 5
I quit Excel & restart then added ListView, now it shows up... Strange!

Thanks a ton Sathish for your guidance and help.

Regards
Don


Hello Sathish,

I am loving the concept of Listview...

Could you please let me know how to get a column value from a listview (from UserForm) displayed in a particular cell in excel?

Thanks and look forward to hearing from you

Regards
Don
 
Hi,

Still working on to get a single column's value to excel sheet

But the below procedure will download all the data from Listview to workbook

Code:
Sub Download_Listv()
 
    Dim objExcel As New Excel.Application
    Dim bkWorkBook As Workbook
    Dim shWorkSheet As Worksheet
    Dim i As Integer
    Dim j As Integer
    Set objExcel = New Excel.Application
    Set bkWorkBook = objExcel.Workbooks.Add
    Set shWorkSheet = bkWorkBook.ActiveSheet
    For i = 1 To ListView41.ColumnHeaders.Count
        shWorkSheet.Cells(1, Chr(64 + i)) = ListView41.ColumnHeaders(i)
    Next
    For i = 1 To ListView41.ListItems.Count
        shWorkSheet.Cells(i + 1, "A") = ListView41.ListItems(i).Text
               
        For j = 2 To ListView41.ColumnHeaders.Count
            shWorkSheet.Cells(i + 1, Chr(64 + j)) = ListView41.ListItems(i).SubItems(j - 1)
        Next
       
    Next
   
    objExcel.Visible = True
 
 
End Sub
 
Hi,

Still working on to get a single column's value to excel sheet

But the below procedure will download all the data from Listview to workbook

Code:
Sub Download_Listv()

    Dim objExcel As New Excel.Application
    Dim bkWorkBook As Workbook
    Dim shWorkSheet As Worksheet
    Dim i As Integer
    Dim j As Integer
    Set objExcel = New Excel.Application
    Set bkWorkBook = objExcel.Workbooks.Add
    Set shWorkSheet = bkWorkBook.ActiveSheet
    For i = 1 To ListView41.ColumnHeaders.Count
        shWorkSheet.Cells(1, Chr(64 + i)) = ListView41.ColumnHeaders(i)
    Next
    For i = 1 To ListView41.ListItems.Count
        shWorkSheet.Cells(i + 1, "A") = ListView41.ListItems(i).Text
              
        For j = 2 To ListView41.ColumnHeaders.Count
            shWorkSheet.Cells(i + 1, Chr(64 + j)) = ListView41.ListItems(i).SubItems(j - 1)
        Next
      
    Next
  
    objExcel.Visible = True


End Sub



Hello Sathish,

Thank you for your reply and the code. I will try it out.

I tried to implement the below code (got some tips from someone). When on double click on listview record, it copies only the 'Customer' value to the excel cell and it gives out an error for copying VAT Number.


Code:
Private Sub ListView41_DblClick()
   
Dim i As Integer
Dim blnFoundFirstItem As Boolean
Dim ws As Worksheet
Dim tb As ListObject
Dim rng As Range

Set ws = Worksheets("Table1")
Set tb = ws.ListObjects("Table1")
Set rng = Selection

  blnFoundFirstItem = False
  For i = 1 To ListView41.ListItems.Count
  If (ListView41.ListItems(i).Selected) Then
  ' Assign the Text of the 'first' selected item to Textbox1.Text.
  If (Not blnFoundFirstItem) Then
  TextBox1.Text = ListView41.ListItems(i).Text
  Intersect(tb.ListColumns("Customer").DataBodyRange, rng.EntireRow).Value = ListView41.ListItems(i).Text
  Intersect(tb.ListColumns("Vat Number").DataBodyRange, rng.EntireRow).Value = ListView41.ListItems(i).Text
  blnFoundFirstItem = True
  End If
  End If
  Next i
End Sub

Regards
Don
 
Hi,

Still working on to get a single column's value to excel sheet

But the below procedure will download all the data from Listview to workbook

Code:
Sub Download_Listv()

    Dim objExcel As New Excel.Application
    Dim bkWorkBook As Workbook
    Dim shWorkSheet As Worksheet
    Dim i As Integer
    Dim j As Integer
    Set objExcel = New Excel.Application
    Set bkWorkBook = objExcel.Workbooks.Add
    Set shWorkSheet = bkWorkBook.ActiveSheet
    For i = 1 To ListView41.ColumnHeaders.Count
        shWorkSheet.Cells(1, Chr(64 + i)) = ListView41.ColumnHeaders(i)
    Next
    For i = 1 To ListView41.ListItems.Count
        shWorkSheet.Cells(i + 1, "A") = ListView41.ListItems(i).Text
             
        For j = 2 To ListView41.ColumnHeaders.Count
            shWorkSheet.Cells(i + 1, Chr(64 + j)) = ListView41.ListItems(i).SubItems(j - 1)
        Next
     
    Next
 
    objExcel.Visible = True


End Sub


Hello Sathish,

Just would like to share a post I came across a link regarding listview and could get what I was looking for a bit modified:

http://dailydoseofexcel.com/archives/2006/12/26/listview/. I used the below code:

Code:
Private Sub ListView1_DblClick()
   
  Dim i As Integer
  Dim blnFoundFirstItem As Boolean
  Dim ws As Worksheet
  Dim tb As ListObject
  Dim rng As Range
   
  Set ws = Worksheets("invoice entry")
  Set tb = ws.ListObjects("Table1")
  Set rng = Selection

  blnFoundFirstItem = False
  For i = 1 To ListView1.ListItems.Count
  If (ListView1.ListItems(i).Selected) Then
   
  ' Assign the Text of the 'first' selected item to Textbox1.Text.
  If (Not blnFoundFirstItem) Then
  'Always the 1st column value
  Intersect(tb.ListColumns("Comments").DataBodyRange, rng.EntireRow).Value = ListView1.ListItems(i).Text
 ' SubItems(5), is the column sequence you wish to have the value of
Intersect(tb.ListColumns("Invoice Amount").DataBodyRange, rng.EntireRow).Value = ListView1.ListItems(i).SubItems(5)
  blnFoundFirstItem = True
  End If
  End If
  Next i
End Sub


Thanks once again for all the support you gave me, learned new things from you.


regards
Don
 
Last edited:
Back
Top