Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing to access (Excel 2003)

    Is there a way to take the attached spreadsheet and import it into access? I know I can use the File/Get external data feature but I want it to populate the table a different way. Let me explain. If I just do the File/Get- name, 3/1/07, 3/2/07,3/3/07 (dates are fields). I need it to do this: name, time, date(3/1/07) is record #1, same name, same time, next date(3/2/7) is record #2, etc.

    SO for each month an employee will have 30-31 entries in my table for their work schedule.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing to access (Excel 2003)

    Does the "real" worksheet look like that too, i.e. a blank row 1, the word "Month" in E2, a series of dates in row 3, a blank row 4, then a series of codes in the following rows?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing to access (Excel 2003)

    The sheet actually looks like the attached.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing to access (Excel 2003)

    OK, that looks good. It's dinnertime here now, I'll post some code later (if nobody else does so in the meantime).

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing to access (Excel 2003)

    Here is some code to be run from Access. It assumes

    - You have either imported or linked the workbook in Access. For the sample workbook, the Access table would be named 'Sheet 2'.

    - You have created a table in Access with the correct structure. For illustration purposes, I'll name it tblData. It has the following fields:
    TheName (Text, size 25)
    Position (Text, size 25)
    Location (Text, size 25)
    TheDate (Date/Time)
    Code (Text, size 25)

    - You have set a reference to the Microsoft DAO 3.6 Object Library in Tools | References in the Visual Basic Editor. (There is one by default in Access 2003).

    - Here is the code:

    Sub ConvertImport()
    Dim dbs As DAO.Database
    Dim rstIn As DAO.Recordset
    Dim rstOut As DAO.Recordset
    Dim i As Integer

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    ' Adjust the names in the following two statements
    Set rstIn = dbs.OpenRecordset("Sheet 2", dbOpenDynaset)
    Set rstOut = dbs.OpenRecordset("tblData", dbOpenDynaset)
    Do While Not rstIn.EOF
    For i = 3 To rstIn.Fields.Count - 1
    If Not rstIn.Fields(i) & "" = "" Then
    rstOut.AddNew
    rstOut.Fields(0) = rstIn.Fields(0)
    rstOut.Fields(1) = rstIn.Fields(1)
    rstOut.Fields(2) = rstIn.Fields(2)
    rstOut.Fields(3) = CDate(rstIn.Fields(i).Name)
    rstOut.Fields(4) = rstIn.Fields(i)
    rstOut.Update
    End If
    Next i
    rstIn.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    rstOut.Close
    Set rstOut = Nothing
    rstIn.Close
    Set rstIn = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    - As is, this code will only create a record if something has been filled in in the spreadsheet for that date.

    - If you want a record for each employee / each date, remove or comment out the If ... Then and End If lines.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing to access (Excel 2003)

    Let me ask you this question.
    If I create a worksheet for each unit (people working in a different location) can I use the same code to pull each sheet over? Also, when does the code execute? Here is my thinking: the user enters all the information into excel. She goes to access to print a schedule - all the information is pulled in since I will link the workbook to access.

    Thanks so much for your help.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing to access (Excel 2003)

    You can create a link to each different sheet.
    You can use a variable instead of "Sheet 2" in the code, and set the variable before executing ConvertImport.
    You'll have to call ConvertImport explicitly at some point.

    PS Why not print the schedule from Excel?

  8. #8
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing to access (Excel 2003)

    The process is working great. The only problem is that it is not picking up the first date entry. I copied your code and made some moficiations for specs.

    Thanks, I appreciate your help!

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing to access (Excel 2003)

    The loop that reads the date entries is

    For i = 3 To rstIn.Fields.Count - 1
    ...
    Next i

    This starts at column D (fields are numbered starting at 0, so 3 is actually the 4th field), based on the spreadsheet you attached. You'll have to adjust it if the dates start in another column.

  10. #10
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing to access (Excel 2003)

    It has been awhile since I used the code or even looked at the access database. I am now ready to tackle it.

    When I link to excel, my dates (which are my headings) change to F2, F3, F4 so the code is giving me a type mismatch. I'm not sure what I am doing incorrectly. Can you shed some light? I know it worked before and all I did was change the date headings for another month.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing to access (Excel 2003)

    Did you make sure to tick the check box "First Row Contains Column Headings"?

  12. #12
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing to access (Excel 2003)

    Yes. The spreadsheet looks perfect and even in the import process I can see the dates but when I open the linked table, I get F2, F3, F4 etc.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing to access (Excel 2003)

    Could you post a small sample workbook (with dummy data) that exhibits this behavior when linked in Access?

  14. #14
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing to access (Excel 2003)

    Here is a copy of the linked table.
    Attached Files Attached Files

  15. #15
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing to access (Excel 2003)

    I could not figure out how to attach more than one file. Here is a copy of the excel sheet.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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