Importing CERTAIN fields from Excel into Access?

[Dpot]

I created a template in excel that matches the layout of a form I have in my Access Database. The reason for this is we can export all the data of one record into the template, email it to someone, that someone can fill in whatever portions they need to fill in and send it back.

This is where I'm running into trouble, the next thing we need to be able to do is take what has been emailed back to us and import the excel fields that have been filled in (they are pre-defined) into our Access form. I have found plenty of codes for importing data, but they make you import the entire excel file. Could someone help point me in the direction of a code that lets you set pre-defined fields in it to import from Excel?

[jimatqsi]

Dpot,
All you need to do is to link to the excel file and write a query using that new table name (the table name that points to the linked excel file). In your query you can use whichever fields you choose.

There are other ways, including a predefined import process that skips certain fields, but I think the linked table is the simplest.

Jim

[jforbes]

Jim has got your answer for using as spreadsheet as a datasource.

The following is a quick and dirty way to get the value of a Range in a Spreadsheet. This uses early binding, so you'll need a reference to a Microsoft Excel x.x Object Library. If you start running into versioning problems, the code may need to be converted to late binding and then the reference wouldn't be needed. Lastly, I haven't worried about putting in error handling and a validator to check to see if the file exists.

Public Function getExcelValue(ByRef sSpreadsheet As String, sRange As String) As String
    
    Dim oExcel As Excel.Application
    Dim oWorkbook As Excel.Workbook
    Dim oSheets As Excel.Sheets
    Dim oSheet As Excel.Worksheet
    
    Set oExcel = New Excel.Application
    Set oWorkbook = oExcel.Workbooks.Open(sSpreadsheet, ReadOnly:=True)
    Set oSheets = oWorkbook.Sheets
    Set oSheet = oWorkbook.Sheets(1)
    getExcelValue = oSheet.Range(sRange)
    
    If Not oWorkbook Is Nothing Then
        oWorkbook.Close SaveChanges:=False
        Set oWorkbook = Nothing
    End If
    If Not oExcel Is Nothing Then
        oExcel.Quit
        Set oExcel = Nothing
    End If

End Function

The variable oSheet cracks me up.