Import from Excel - cell by cell ??

[BigMrC]

We are setting up automatic importing of data from excel sheets t
access database. I want to code something in access that at the clic
of a button will find the correct spreadsheet (excel sheets will b
numbered corresponding to the current Access record), will append dat
from specific cells in the excel sheet to specific columns in an acces
table.
How do you import from excel to access specifying by cell etc?
thank

 

[Mirco Wilhelm]

I've been writing on a function to import given fields from a specified
excelfile/sheet but it's not fully working at the moment

I debugged the script down to this line so far
objExcelSheet.Range(strFirstFieldID, strLastFieldID).Copy
GetRSFromExcel

In the excample it would be called like this
objExcelSheet.Range("A1", "A10").Copy GetRSFromExcel
but it doesn't work. VBA help tells me the syntax is .Range(Cell1, [Cell2])
but not, what kind of variable i need to fill in.

Here's the whole thing, please post if anyone has spotted the error!

Public Function GetRSFromExcel( _
ByVal strExcelFile As String, _
ByVal strFirstFieldID As String, _
ByVal strLastFieldID As String, _
ByVal intExcelWorksheet As Integer) _
As ADODB.Recordset

'On Error GoTo GetRSFromExcel_Error

'---------------------------------------------------------------------------
--------------
' Import an Excel Workbook into an Access Recordset
'
' example call: srcExcelRecordSet = GetRSFromExcel("test.xls","A1","A10",1)
' ^ ^ ^ ^ ^
' | | | | |
' returned recordset Filename, start, end,
sheetnumber
'---------------------------------------------------------------------------
--------------

Dim objExcel As New excel.Application
Dim objExcelWorkbook As excel.workbook
Dim objExcelSheet As excel.Worksheet
Dim objStream As ADODB.Stream
Dim i As Integer

' open excel file
Set objExcelWorkbook = objExcel.Workbooks.Open(strExcelFile)

Set objStream = New ADODB.Stream
objStream.Type = adTypeText

' make invisible and block userinput
objExcel.Visible = False
objExcel.Interactive = False

' set reference to a worksheet
Set objExcelSheet = objExcelWorkbook.Sheets(intExcelWorksheet)

' disable display refresh
objExcel.ScreenUpdating = False

' read recordset
objExcelSheet.Range(strFirstFieldID, strLastFieldID).Copy GetRSFromExcel

' activate display refresh
objExcel.ScreenUpdating = True

' activate user input
objExcel.Interactive = True

objExcelWorkbook.Close
objExcel.Quit

' destroy object references
Set objExcelSheet = Nothing
Set objExcel = Nothing

Exit Function

GetRSFromExcel_Error:
Debug.Print "ERROR IN FUNCTION!"
Debug.Print "Filename: " & strExcelFile & vbCrLf & _
"Worksheet: " & intExcelWorksheet & vbCrLf & _
"Datarange: " & strFirstFieldID & ":" & strLastFieldID

End Function