Import data from Excel to Access using vba

I need to open an excel spreadsheet, copy and paste values from one sheet to a new sheet.  I have this part done.  Now I need to import the data into an Access table and overwrite the data that is in the table based on 2 fields Quote No and Item No.

Here is my code for the 1st part:

Set xlApp = New Excel.Application
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
Marilync1266Asked:
Who is Participating?
 
Dale FyeCommented:
The message implies that you need to provide a name that you will use to identify the linked table in Access.  In your original code, you were attempting to import into "tbl_Quote_Equipment", you left that value in your statement below, but removed the quotes, which implies that that value is a variable, which doesn't exist.

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

Try:

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

0
 
Dale FyeCommented:
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.
0
 
Marilync1266Author Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dale FyeCommented:
"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.

0
 
Marilync1266Author Commented:
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

Open in new window

0
 
Dale FyeCommented:
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.
0
 
Marilync1266Author Commented:
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"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.