Results 1 to 4 of 4

Thread: Import Excel File

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 1999
    Posts
    154

    Post

    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

  2. #2
    Hyperactive Member
    Join Date
    Jun 1999
    Location
    Calgary Alberta
    Posts
    359

    Post

    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.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Sep 1999
    Posts
    154

    Post

    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.

  4. #4
    Hyperactive Member
    Join Date
    Jun 1999
    Location
    Calgary Alberta
    Posts
    359

    Post

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width