Import data from Excel to Access using vba

Marilyn,

You copied the code from the previous message into here, but did not include the transferspreadsheet line or the error message you were getting.

Chances are that the error message is a result of trying to force an Excel field into an Access field of a different datatype.  Whenever I use TransferSpreadsheet, I generally link the Excel sheet to Access, rather than trying to import it.  This gives me the ability to provide some error handling and data type manipulation to force the data into the correct data type when I actually dump it into the Access table.

Is it possible to copy the data from excel into a recordset  and then use sql to update the table?  I'm just not sure how to do that using data from excel

"Is it possible to ..."

Yes, but it is generally slow.

You could open a recordset that points at your destination table, then loop through the rows in the Excel spreadsheet you opened (you wouldn't need to copy the values to the temp spreadsheet if you did this).  Actually, I'm not sure why you need to copy the records to a new worksheet, now that I think about it.

Dim rs as dao.recordset
Dim intRow as integer

set rs = currentdb.openrecordset("yourtable",, dbfailonerror)

while xlws.cells(intRow,1) <> ""
    rs.AddNew
    rs(0) = xlws.cells(intRow,1)
    rs(1) = xlws.cells(intRow,2)
    rs(2) = xlws.cells(intRow,3)
    rs.Update
    intRow = intRow + 1
Wend

Keep in mind that this will be slow, but it does provide you with the ability to test that the values in a particular column meet certain data type of value requirements prior to setting the value in the appropriate column of the recordset.

I still perfer to link the Excel spreadsheet and then write an Append query to append the data to my destination table.

The data that I need to import into Access has excel formulas in the cells, so I received an error when I tried importing.  

Can I link the excel file in code?  The name of the file location and name will not always be the same - the user is prompted to select a file - the number of rows vary but never more than 500

I want to do this the most efficient way, I'm just not sure how.  I've attached a copy of my code.

Thanks for your help!

Public Function Import_Quote()

Dim strFilter As String
Dim strImportFileName As String
Dim xlApp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlwb_Import As Excel.Workbook
Dim xlws As Excel.Worksheet
Dim xlws_New As Excel.Worksheet
Dim xlwb_Import_Template As Excel.Workbook
Dim obWS_Import As Object
Dim obWS_Import_Template As Object
Dim xlrng As Excel.Range
Dim xlrng_Import As Excel.Range
Dim xlrng_Import_Template As Excel.Range
Dim db As DAO.Database
Dim strSQL As String
Dim strQuoteNo As String
Dim rstImport As DAO.Recordset
Dim strResponse As String
Dim ctl As Control

On Error GoTo Import_Quote_Err

Set db = CurrentDb
Set xlApp = New Excel.Application

'code to inport myla
strImportType = MsgBox(Prompt:="Are you importing from the MYLA Template File in Microsoft Excel?", Buttons:=vbYesNo + vbCritical)
Select Case strImportType
    Case vbYes
        strFilter = ahtAddFilterItem(strFilter, "Import Files (*.xls)", "*.*")
        strImportFileName = ahtCommonFileOpenSave( _
            Filter:=strFilter, OpenFile:=True, _
            DialogTitle:="Please select an import file...", _
            Flags:=ahtOFN_HIDEREADONLY)

If strImportFileName = "" Then
    MsgBox ("Import file not selected, please try again.")
    GoTo Import_Quote_Exit
End If

Set xlwb_Import = xlApp.Workbooks.Open(strImportFileName)

Set xlws_New = xlwb_Import.Worksheets.Add
xlws_New.Name = ("temp")
Set xlws_Import = xlwb_Import.Worksheets("Export")
Set xlws_New = xlwb_Import.Worksheets("Temp")

xlws_Import.Activate
xlws_Import.Range("A1:S500").Copy
xlws_New.Activate
xlws_New.Range("A1:S500").PasteSpecial Paste:=xlPasteValues

DoCmd.TransferSpreadsheet acImport, 8, "tbl_Quote_Equipment", strImportFileName, True, "Temp!A1:S500"
DoCmd.OpenQuery ("qdel_Quote_Equipment_Blanks")
MsgBox ("Successfully Imported the Myla Products")

xlwb_Import.Activate
xlwb_Import.Save
xlwb_Import.Close

Set xlApp = Nothing
Set xlwb_Import = Nothing
Set xlws_Import = Nothing
Set xlws_New = Nothing

Import_Quote_Exit:

Exit Function

'Error Routine
Import_Quote_Err:

Call Error_Routine("Import Quote")
GoTo Import_Quote_Exit


End Function

Select allOpen in new window

1.  If you want to use this technique, I think you are going to have to close the workbook after you copy the data to the worksheet you want to import.  Then try it again.

2.  Like I said, rather than importing the spreadsheet using

docmd.transferspreadsheet acImport

I would link the worksheet, using:

docmd.transferspreadsheet acLink

Then I would run an Append query that makes sure the columns I am appending meet specific datatype and value criteria.

I tried the aclink and received an error "The action or method requires a table name argument"

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, tbl_Quote_Equipment, strImportFileName, True, "Temp!A1:S500"