Results 1 to 3 of 3

Use VBA to extract Data and create a listbox/dropdownbox or grid

  1. #1
    BugsyFrank is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    3

    Use VBA to extract Data and create a listbox/dropdownbox or grid

    Hello All.



    This week I have to create an Access Form and implement VBA to extract the data from an Excel spreadsheet and display it in an Access listbox, dropdownbox, or any form of grid.

    To show what I've tried so far I am listing the following code that I used on 2 different FORMS but neither one works right.

    The first FORM name I used is frmUpdateMedia which is what we are supposed to use the VBA code to extract the data to.

    I promise I'm not looking for a free ride or someone "to do my homework for me". I'm tired of wasting hours trying to figure out something that I cannot do yet. I want to learn but my Professor keeps referring us to you tubes and MS sites that I do not understand how to apply to our problems the past 2 weeks.

    Here is what I've tried so far:

    First Try
    Private Sub Form_Load()

    Dim dbsWk7_Magaw_INFO262_CD_DVD As DAO.Database
    Dim frmM As DAO.Recordset
    Dim varRecords As Variant
    Dim intNumReturned As Integer
    Dim intNumColumns As Integer
    Dim intColumn As Integer
    Dim intRow As Integer
    Dim strSQL As String

    On Error GoTo ErrorHandler

    Set dbsWk7_Magaw_INFO262_CD_DVD = CurrentDb
    strSQL = "Select MediaID, MediaType, Title, Artist, Producer_Studio FROM ExcelLinkTable1"
    Set frmM = dbsWk7_Magaw_INFO262_CD_DVD.OpenRecordset(SQL, dbOpenSnapshot)
    ' Space
    varRecords = frmM.GetRows(3)
    intNumReturned = UBound(varRecords, 2) + 1
    intNumColumns = UBound(varRecords, 1) + 1

    For intRow = 0 To intNumReturned - 1
    For intColumn = 0 To intNumColumns - 1
    Debug.Print varRecords(intColumn, intRow)
    Next intColumn
    Next intRow

    frmM.Close
    dbsWk7_Magaw_INFO262_CD_DVD.Close

    Set frmM = Nothing
    Set dbsWk7_Magaw_INFO262_CD_DVD = Nothing

    Exit Sub

    ErrorHandler:
    MsgBox "Error #: " & Err.Number & vbCrLf & Err.Description
    End Sub

    Wk7_Magaw_INFO262_CD_DVD is the name of my DB.

    You would be right in saying I did not come up with this all on my own. I found an example and tried to adjust it for my project but no luck so far.

    I tried an experiment with a FORM I named fUM and this was my
    2nd Try:

    Private Sub Form_Load()
    Dim i As Integer
    rst.Open "SELECT [MediaID], [MediaType], [Title], [Artist], [Producer_Studio] FROM ExcelLinkTable1;", _
    rst.MoveFirst
    i = 0
    With Me.Combo0
    .Clear
    Do
    .AddItem
    .List(i, 0) = rest![MediaID]
    .List(i, 1) = rest![MediaType]
    .List(i, 2) = rest![Title]
    .List(i, 3) = rest![Artist]
    .List(i, 4) = rest![Producer_Studio]
    i = i + 1
    rest.MoveNext
    Loop Until rest.EOF
    End With

    End Sub

    I created a table named ExcelTable1 from my linked Excel file but if possible I would rather be able to pull the data straight from the Excel file using VBA rather than the ExcelTable1.

    Can anyone help me?
    I have read numerous examples and Microsoft forums but I have found more Information on how to pull data from Access to Excel than the other way around.

    Thanks,
    BugsyFrank

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,699
    You don't need any code.
    in a macro ,import export, importsreadsheet
    will import excel data to a table.
    then make a query, to pull the items in a list.
    or display the grid of data values in form or spreadsheet.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,436
    This week I have to create an Access Form and implement VBA to extract the data from an Excel spreadsheet and display it in an Access listbox, dropdownbox, or any form of grid.
    In First Try, you had an error or two. And I changed your code a little: some of the names you used were too long for my taste.
    Code:
    Private Sub Form_Load()
    
        '    Dim dbsWk7_Magaw_INFO262_CD_DVD As DAO.Database   '<<=this was too long for me
        Dim db As DAO.Database
        
        '    Dim frmM As DAO.Recordset  '<< didn't make sense (to me) to have a record set declared with a form prefix
        Dim rs As DAO.Recordset  ' record set
    
        Dim varRecords As Variant
        Dim intNumReturned As Integer
        Dim intNumColumns As Integer
        Dim intColumn As Integer
        Dim intRow As Integer
        Dim strSQL As String
    
        On Error GoTo ErrorHandler
    
        Set db = CurrentDb
    
        strSQL = "Select MediaID, MediaType, Title, Artist, Producer_Studio FROM ExcelLinkTable1"
    
    'you had:
        ' Set frmM = dbsWk7_Magaw_INFO262_CD_DVD.OpenRecordset(SQL, dbOpenSnapshot)
    'but you declared "Dim strSQL As String"
    
        Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
        ' Space
        varRecords = rs.GetRows(3)
        intNumReturned = UBound(varRecords, 2) + 1
        intNumColumns = UBound(varRecords, 1) + 1
    
        For intRow = 0 To intNumReturned - 1
            For intColumn = 0 To intNumColumns - 1
                Debug.Print varRecords(intColumn, intRow)
            Next intColumn
        Next intRow
    
        rs.Close
    
        '    dbsWk7_Magaw_INFO262_CD_DVD.Close
        'The rule is: If you create it destroy it,
                       ' If you open it, close it.
    
       ' you didn't open dbsWk7_Magaw_INFO262_CD_DVD, so don't close it.
    
    
        Set rs = Nothing
        Set db = Nothing
    
        Exit Sub
    
    ErrorHandler:
        msgbox "Error #: " & Err.Number & vbCrLf & Err.Description
    End Sub
    The code now should print to the immediate window. (at least it did for me)



    BTW, I learned something from you today. I didn't know about the GetRows method. I'll have to study it - I'm sure it will be useful....thanks.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Report data as a color-coded grid
    By dth122 in forum Reports
    Replies: 11
    Last Post: 12-21-2014, 01:31 PM
  2. Replies: 7
    Last Post: 10-15-2012, 11:05 PM
  3. Replies: 8
    Last Post: 09-24-2012, 12:34 PM
  4. Help Plz. Creating DropDownBox
    By dbalilti in forum Forms
    Replies: 11
    Last Post: 05-22-2012, 06:54 AM
  5. Grid lines vs Grid Dots
    By dharriet in forum Access
    Replies: 0
    Last Post: 10-14-2008, 09:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums