Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2006
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Excel Sheet into Access

    Anyone have any links or sample code on importing an Excel sheet into an Access table?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Import Excel Sheet into Access

    Hi,
    If you look up the TransferSpreadsheet method in the VBA Help in Access, the example there is pretty clear.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Sep 2006
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Excel Sheet into Access

    Rory,
    Is there anyway to make sure the fields from the spreadsheet map to a particular field in Access. I am automating a monthly import and the clients have a different name for the two of the fields...

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Tallinn, Harjumaa, Estonia
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Excel Sheet into Access

    Dear MDC,

    You cannot be sure anything while you cannot explicitly define field names and types as it is with the transfer spreadsheet method.

    Something what i have done in similar cases:
    I have first exported the Excel data to text and then imported the text data to Access using specification (being available in Transfertext method) or imported the Excel data to a temporary export table and then appended to the table my application uses. I have also used Copy and Paste Append...
    I really don't think that it helps you much, but ask more if you see something in it.


    Anu

  5. #5
    DAW
    Guest

    Re: Import Excel Sheet into Access

    If you need to move data between Access and Excel (or Word) and you need tight control over what happens when and what goes where, then you should use Automation. See eg. below which copies data from an Access table into an Excel Workbook. You will need to adapt this approach. You will need to set a reference to the Excel object library first. You can then do what you like. You are in control. You don't have to rely on any built-in Methods of the Docmd Object.

    Private Sub OutputToExcel()
    Dim dbs As Database
    Dim rst As Recordset
    Dim XL As New Excel.Application
    Dim intCounter As Integer

    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("Table1")

    XL.Visible = True
    XL.Workbooks.Add

    rst.MoveFirst
    intCounter = 1

    Do Until rst.EOF
    XL.Workbooks(1).Sheets(1).Range("A" & intCounter).Value = rst.Fields(0)
    XL.Workbooks(1).Sheets(1).Range("B" & intCounter).Value = rst.Fields(1)
    intCounter = intCounter + 1
    rst.MoveNext
    Loop

    XL.Quit
    Set XL = Nothing
    Set rst = Nothing

    MsgBox "Export to Excel has finished"

    End Sub

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Import Excel Sheet into Access

    Hi,
    Either of the methods suggested in the other posts will work - I use automation as it gives the most control, but it's not as easy to set up! The temporary table and append query might be your easiest way, particularly as you quite often end up with records that are completely blank if you import from a spreadsheet (I think Access uses every row that Excel holds in its UsedRange which sometimes includes rows that used to have information in but no longer do).
    Anyway, if you would like help with either of these methods, please repost - there are quite a few code gurus around here![img]/w3timages/icons/grin.gif[/img]
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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