Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2012
    Posts
    3

    Unanswered: Auto Fill in data from a microsoft access table to a microsoft excel table

    I have a master database table in Microsoft Access 2007. I have a formatted schedule done in Microsoft Excel 2007. I do not want to copy and paste this information. I need to be able to enter in an employee number that is six digits long in the Excel Schedule and have it automatically fill in the rest of the information (Shift, Role, Phone Number and Office Number) that is associated with that employee in the master database in Microsoft Access. I also need for it to be able to do that if I put in a new employee in my master database. New entries in the master database need to auto fill in the Excel Schedule. I am not good with SQL or anything like that. I tried a query, but it wasn’t working for me. Can anybody provide me the easiest way to do this? I provided example of the two files in a zip folder.
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The attached files have been modified to retrieve employee information
    and add it to the spreadsheet. My Excel version is 2003.

    You have a field called “Phone Number” (which has a double space),
    and I changed it to “Phone Number” (single spaced).

    Store the data base and Excel file in the same folder.

    Code:
    Private Sub CommandButton1_Click()
    Dim strTarget As String
    
        strTarget = Cells(ActiveCell.Row, ActiveCell.Column).Value
        If Not IsNumeric(strTarget) Or Len(strTarget) <> 6 Then
            MsgBox "Enter 6-digit employee ID and make that cell active" & vbCrLf & _
                "before retrieving employee information."
            Cells(ActiveCell.Row, ActiveCell.Column).Select
            Exit Sub
        End If
        
        Call Get_EmpInfo(CLng(strTarget), ActiveCell.Row)
        
    End Sub
    
    Sub Get_EmpInfo(empID As Long, currRow As Long)
    'retrieve employee information from Access data base
    'and add to Sheet1 cells on row with employee ID
    Dim sQRY As String, strFilePath As String, tblName As String
    Dim destSheet As Worksheet
    Dim conn, rst
    
        strFilePath = ThisWorkbook.Path & "\Master.mdb"
        tblName = "Master"
        Set destSheet = Sheets("Sheet1")
           
        Set conn = CreateObject("ADODB.Connection")
        Set rst = CreateObject("ADODB.Recordset")
        
        conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & strFilePath & ";"
            
        sQRY = "SELECT Master.[Last Name], Master.[First Name], Master.Shift, Master.[Office Number], Master.[Phone Number] "
        sQRY = sQRY & "FROM Master WHERE (((Master.ID)=" & empID & "));"
        
        rst.Open sQRY, conn
        
        If rst.EOF Then
            MsgBox "Employee ID " & empID & " does not exist in the data base."
            GoTo Exit_Get_EmpInfo
        End If
        
        With Sheets(destSheet.Name)
            .Cells(currRow, 2).Value = rst.Fields(0)
            .Cells(currRow, 3).Value = rst.Fields(1)
            .Cells(currRow, 4).Value = rst.Fields(2)
            .Cells(currRow, 5).Value = rst.Fields(3)
            .Cells(currRow, 6).Value = rst.Fields(4)
        End With
    
    Exit_Get_EmpInfo:
    
        rst.Close
        Set rst = Nothing
        conn.Close
        Set conn = Nothing
        Sheets(destSheet.Name).Cells(ActiveCell.Row, ActiveCell.Column).Select
        
    End Sub
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •