Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    import from Excel/filter/set checkbox (2003)

    What I want to do is import a table from Excel into an existing table in my database. We have to do this once a month. I've set up a procedure in a module and I put a command button on my form to call that procedure. That works.
    The part I can't figure out is how to mark only the new records so I can run reports, etc. for that month on just the new records. There is a checkbox on my form called "include on reports" that I would like checked as the new bunch of records are being imported. Then I have a command button to clear that checkbox once I'm done with them for that month. (This used to work when the records were entered manually.)
    Can I add something to my module that would check the box only for the new records I'm importing?

    Here is all I have in the procedure so far:

    Private Sub Command80_Click()
    ImportExcel
    DoCmd.Requery

    End Sub


    Thanks for any help!

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

    Re: import from Excel/filter/set checkbox (2003)

    What is the code for ImportExcel?

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from Excel/filter/set checkbox (2003)

    I copied it from another post here -

    Sub ImportExcel()
    Dim strFolder As String
    Dim strFile As String
    strFolder = "C:Excel"
    strFile = Dir(strFolder & "*.xls")
    Do While strFile <> ""
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel2003, _
    TableName:="test", _
    FileName:=strFolder & strFile, _
    HasFieldNames:=True
    strFile = Dir()
    Loop
    End Sub

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

    Re: import from Excel/filter/set checkbox (2003)

    Open the table named test in design view.
    Add a new field named New.
    Set its data type to Yes/No.
    Set its Default Value to Yes.

    When you import the Excel workbooks into the test table, all new records will have the New field set to Yes.
    So you can create a query that selects all records for which New is Yes, and use this as record source for a report.
    Alternatively, you can pass "New = True" as WhereCondition in DoCmd.OpenReport to filter the report being opened.

    To set the values of New to No, you can use an update query or execute an SQL statement:

    DoCmd.RunSQL "UPDATE test SET New = False"

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: import from Excel/filter/set checkbox (2003)

    Thank you! That's exactly what I was trying to do and it wasn't working, but I just figured out it's because for the check box field, I had an empty field in the table I was importing because I thought they had to match exactly - once I took that field out of the incoming table, it worked. I knew it had to be something fairly easy! I don't even have to change anything else in the database because the rest works the same way it did when we entered manually. Thanks again!

Posting Permissions

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