Import Excel File

I can do it with the following code but it has some problems in that it doesn't seem to release the excel file from memory when it is finished.

Is there a line input statement or some other way of doing it?


Public Sub Add_To_FRREQ()

Dim myXL As Object
Dim dbs As Database
Dim rc As Recordset

Set myXL = CreateObject("Excel.Application")

myXL.Workbooks.Open filename:=App.Path & "\FRREQ.xls"
With myXL
.Workbooks("FRREQ.xls").Activate

Set dbs = Workspaces(0).OpenDatabase(App.Path & "\frreq.mdb")
Set rc = dbs.OpenRecordset("tFR_Misc_Requests")

For i = 2 To 10
.range("A" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc.AddNew
rc![acctnum] = .ActiveCell
Else
Exit Sub
End If

.range("B" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![oldAcctNum] = .ActiveCell
End If

.range("C" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![pName] = .ActiveCell
End If

.range("D" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![Requestor] = .ActiveCell
End If

.range("E" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![ReqExt] = .ActiveCell
End If

.range("F" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![ReqDept] = .ActiveCell
End If

.range("G" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![ReqMgr] = .ActiveCell
End If

.range("H" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![DateReq] = .ActiveCell
End If

.range("I" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![SSN] = .ActiveCell
End If

rc.Update
Next

End With
myXL.Quit
Set myXL = Nothing
rc.Close
dbs.Close

End Sub

One thing I noticed is that you call an Exit Sub but don't close your object or database or recordset. In the first IF THEN in the begining of the FOR statement.

Thank you. That is probably it. Hey thanks for the help on a few other issues as well. I can import an excel file using this code but I really want to do it another way such as

Open statement and line input#

or possibly with an ODBC connection to an excel file (DSN) but don't really know how to do it.

what kind of Excel file? If a plain CSV file, I can help you with that. If it's an actual Excel spreadsheet, I'm not entirely sure how. If you want info for a csv import, email me. I'm heading home for now so I may not be back to check until tommorow.