Import Excel File

[Steve Thomas]

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

[netSurfer]

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.

[Steve Thomas]

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.

[netSurfer]

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.