4 Replies Latest reply: Sep 15, 2009 8:44 PM by mlov83 RSS

    Insert Range into table using vba

      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)
      "VALUES (r) ")

      End Sub

      Thanks for any help in advance.
        • 1. Re: Insert Range into table using vba
          noone has any help on this issues ? wao!! any help anyone?
          • 2. Re: Insert Range into table using vba
            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
            End If
            Next lngRow

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