This discussion is archived
4 Replies Latest reply: Sep 15, 2009 6:44 PM by mlov83 RSS

Insert Range into table using vba

mlov83 Newbie
Currently Being Moderated
here is the code i have I'm using the 0040 driver no problem if i want to to hardcode in a insert but now i would like to insert the one column in RANGE A in my spreadsheet into 1 column in the table, and i cant seem to make it work. Here is my lousy attempt to try to get this done but I have had no luck.
Can anyone please poing me in the right direction?

Sub test2()
Dim r As Range
Set r = Range("a1")

'MsgBox r.Value


Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
Set EmpDb = OO4OSession.OpenDatabase("XXX", "xxx/xxx", 0)
EmpDb.ExecuteSQL ("INSERT INTO FABINVH(FABINVH_CODE)" & _
"VALUES (r) ")

End Sub


Thanks for any help in advance.
  • 1. Re: Insert Range into table using vba
    mlov83 Newbie
    Currently Being Moderated
    noone has any help on this issues ? wao!! any help anyone?
  • 2. Re: Insert Range into table using vba
    mlov83 Newbie
    Currently Being Moderated
    Thanks everyone for nothing I was able to find some answers by playing around and playing around on the web and here it is this is how you do it. Hope this helps someone else someday. This would inpur the cells in column A and B.



    Public Sub Kick_Ass_Method()
    Dim OraDatabase As Object
    Dim OraDynaSet As Object
    Dim OraSession As Object
    Dim lngRow As Long
    Dim strFIELD1 As String
    Dim strFIELD2 As String

    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = OraSession.OpenDatabase("XX", "XX/XXX", 0)
    OraDatabase.Parameters.Add "Fabinvh_code", "", 1
    'OraDatabase.Parameters.Add "Fabinvh_vend_pidm", "", 1
    Set OraDynaSet = OraDatabase.CreateDynaset("select Fabinvh_code from Fabinvh", 1)

    For lngRow = 1 To 100
    strFIELD1 = Range("A" & CStr(lngRow)).Value
    'strFIELD2 = Range("B" & CStr(lngRow)).Value
    With OraDatabase
    .Parameters("Fabinvh_code").Value = strFIELD1
    '.Parameters("Fabinvh_vend_pidm").Value = strFIELD2
    .ExecuteSQL "insert into Fabinvh(FABINVH_CODE) values(:Fabinvh_code)"
    End With
    If lngRow Mod 10 = 0 Then
    DoEvents
    End If
    Next lngRow

    OraDynaSet.Close
    OraDatabase.Close
    'OraSession.Close
    Set OraDynaSet = Nothing
    Set OraDatabase = Nothing
    Set OraSession = Nothing
    End Sub
  • 3. Re: Insert Range into table using vba
    315008 Newbie
    Currently Being Moderated
    That's what I am looking for long time. Thanks.
  • 4. Re: Insert Range into table using vba
    mlov83 Newbie
    Currently Being Moderated
    im happy it helped you i cant tell you how long I had to search for this ;-)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points