Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Import from Excel (2002)

    Hi,

    We presently use scheduling software that allows us to export data from reports to excel to be further manipulated. I've automated this data but find that the end user often times blows it up or has issues. I'd like to be able to import the information into an access table where I can than redesign the reports as needed. My goal is to have a command button that will import the new data from excel and in one instance replaces the previously existing data and another instance adds to the previously exisiting data. In this instance the excel file is called [Client List .xls] and the path is C:Access Program DevelopmentExcel To Access FilesClient List.

    I tried to see if I could use a macro to do it but had no luck. This isn't an area I've worked in before and don't even know where to start on this one.

    Thanks!

    Leesah

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

    Re: Import from Excel (2002)

    Here is a custom VBA procedure you can use:

    Sub ImportExcel(strFile As String, strTable As String, blnAppend As Boolean)
    On Error GoTo ErrHandler

    ' Delete records if necessary
    If blnAppend = False Then
    CurrentDb.Execute "DELETE * FROM <!t>[" & strTable & "]<!/t>"
    End If

    DoCmd.TransferSpreadsheet TableName:=strTable, _
    FileName:=strFile, HasFieldNames:=True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

    Use it like this in the On Click event of a command button cmdImport. I have assumed that you have a check box chkAppend to specify whether the data should be appended to the existing data, and that the target table is named tblTarget:

    Private Sub cmdImport_Click()
    ImportExcel "C:Access Program DevelopmentExcel To Access FilesClient ListClient List.xls", _
    "tblTarget", Me.chkAppend
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import from Excel (2002)

    Thanks so much Hans! I can't wait to get to work testing it out. If this works the way I need it to, which I've no doubt it will, it will make my life sooooo much easier!!

    Thanks again,
    Leesha

Posting Permissions

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