Extracting Data from ERP Database based on 2 variables in Spreadsheet...cont'd

Hi all,

I got some help from DonkeyOte a while back to help extract data from my ERP database, using info available in my Spreadsheet...

http://www.excelforum.com/excel-prog...variables.html

Try to adapt it a little to another spreadsheet I am working on, but can't seem to nail it.

Here is the code I have changed around from the above thread..

Sub GetDataFromVisual_DO()
'--------------------------------------------------------------------------------------------------------------------------------------
'Defining variables
'--------------------------------------------------------------------------------------------------------------------------------------
Dim cnOra As ADODB.Connection
Dim rsOra As ADODB.Recordset
Dim vIDs As Variant, wIDs As Variant
Dim db_name As String, UserName As String, Password As String, vstrIDs As String, wstrIDs As String, strSQL As String
Dim c As Range, rngIDs As Range
Dim vrngData As Range, vrngSubData As Range, vrngChunkData As Range, wrngData As Range, wrngSubData As Range, wrngChunkData As Range
Dim LastRow As Long, i As Long
Dim XLCalc As XlCalculation: XLCalc = Application.Calculation
Const BLOCK_SIZE = 1000
'--------------------------------------------------------------------------------------------------------------------------------------
'Disable Screen Refresh & Events & Alter Calc to Manual
'--------------------------------------------------------------------------------------------------------------------------------------
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With
'--------------------------------------------------------------------------------------------------------------------------------------
'Establish Database Connection & Recordset
'--------------------------------------------------------------------------------------------------------------------------------------
Set cnOra = New ADODB.Connection
Set rsOra = New ADODB.Recordset
db_name = "SANDBOX_ODBC"
UserName = "RPRO"
Password = "PASSWORD"
cnOra.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" & Password + ";"
rsOra.CursorLocation = adUseServer
'--------------------------------------------------------------------------------------------------------------------------------------
'Set Handler... risky...
'--------------------------------------------------------------------------------------------------------------------------------------
On Error Resume Next
'--------------------------------------------------------------------------------------------------------------------------------------
'Work Order Ranges -- safer to use Col A & Offset I suspect - given M/N could both be blank (Sales Order lines)
'--------------------------------------------------------------------------------------------------------------------------------------
Set vrngData = Mid(Range("C7", Cells(Rows.Count, "C").End(xlUp)), 2, 5) ' Change the Start Cell & Offset to the first Part ID cell...
Debug.Print vrngData
Set vrngChunkData = vrngData.Offset(0, 0).Resize(BLOCK_SIZE)
Set vrngSubData = Intersect(vrngData, vrngChunkData)
'--------------------------------------------------------------------------------------------------------------------------------------
'WO Lots
'--------------------------------------------------------------------------------------------------------------------------------------
Set wrngData = Mid(Range("C7", Cells(Rows.Count, "C").End(xlUp)), 8, 2)                                      ' Same Range Dimension as above just different column
Set wrngChunkData = wrngData.Offset(0, 0).Resize(BLOCK_SIZE)
Set wrngSubData = Intersect(wrngData, wrngChunkData)
'--------------------------------------------------------------------------------------------------------------------------------------
'Loop Subset of vrngData
'--------------------------------------------------------------------------------------------------------------------------------------
Do While Not vrngSubData Is Nothing
    Debug.Print vrngSubData.Address
    'Create SQL InStr of IDs based on subdata range values
    vstrIDs = "'" & Join(Application.WorksheetFunction.Transpose(vrngSubData.Value), "','") & "'"
    wstrIDs = "'" & Join(Application.WorksheetFunction.Transpose(wrngSubData.Value), "','") & "'"
    'Compile query to get Costs for PartIDs in the strIDs array
    strSQL = "SELECT "
        strSQL = strSQL & "WORKORDER_BASE_ID, RESOURCE_ID, SEQUENCE_NO, USER_1, USER_2, USER_3, USER_4, USER_5,USER_6, USER_7, USER_8, USER_9, USER_10, "
        Rem MySQL: strSQL = strSQL & "CONCAT(WORKORDER_BASE_ID, ':',WORKORDER_LOT_ID) AS `TEMPKEY_ID` "
        Rem Oracle: can't test... I think CONCAT can only take 2 args so maybe embedding would work ?
        strSQL = strSQL & "CONCAT(CONCAT(WORKORDER_BASE_ID,':'),SEQUENCE_NO) AS ""TEMPKEY_ID"" "
    strSQL = strSQL & "FROM "
        strSQL = strSQL & "OPERATION "
    strSQL = strSQL & "WHERE 1=1 "
        strSQL = strSQL & "AND WORKORDER_TYPE = 'M' "
        strSQL = strSQL & "AND WORKORDER_BASE_ID In (" & vstrIDs & ") "
        strSQL = strSQL & "AND SEQUENCE_NO In (" & wstrIDs & ") "
    Debug.Print strSQL
    'Execute SQL
    rsOra.Open strSQL, cnOra, adOpenStatic
    'Clear Col AI
    Range("AI:AI").ClearContents
    'Iterate Subset of vrngData and find matching record in Recordset (if exists)
    For Each c In vrngSubData
        With rsOra
            .Find "TEMPKEY_ID = '" & c.Value & ":" & c.Offset(0, 1).Value & "'"
            If Not .EOF Then
                c.Offset(0, 26).Value = rsOra![USER_1]
                c.Offset(0, 27).Value = rsOra![USER_2]
                c.Offset(0, 28).Value = rsOra![USER_3]
                c.Offset(0, 29).Value = rsOra![USER_4]
                c.Offset(0, 30).Value = rsOra![USER_5]
                c.Offset(0, 31).Value = rsOra![USER_6]
                c.Offset(0, 32).Value = rsOra![USER_7]
                c.Offset(0, 33).Value = rsOra![USER_8]
                c.Offset(0, 34).Value = rsOra![USER_9]
                c.Offset(0, 35).Value = rsOra![USER_10]
               
                
                Debug.Print c.Offset(0, 26).Value
            End If
            .MoveFirst
        End With
    Next c
    'Close Recordset
    rsOra.Close
    'Redefine Ranges Before Proceeding
    Set vrngChunkData = vrngSubData.Offset(vrngSubData.Rows.Count).Resize(BLOCK_SIZE)
    Set vrngSubData = Intersect(vrngData, vrngChunkData)
    Set wrngChunkData = wrngSubData.Offset(wrngSubData.Rows.Count).Resize(BLOCK_SIZE)
    Set wrngSubData = Intersect(wrngData, wrngChunkData)
Loop
'--------------------------------------------------------------------------------------------------------------------------------------
' Close
'--------------------------------------------------------------------------------------------------------------------------------------
cnOra.Close
'--------------------------------------------------------------------------------------------------------------------------------------
' Release
'--------------------------------------------------------------------------------------------------------------------------------------
Set vrngSubData = Nothing
Set vrngChunkData = Nothing
Set vrngData = Nothing
Set wrngSubData = Nothing
Set wrngChunkData = Nothing
Set wrngData = Nothing
Set rsOra = Nothing
Set cnOra = Nothing
'--------------------------------------------------------------------------------------------------------------------------------------
'Restore App Level Settings
'--------------------------------------------------------------------------------------------------------------------------------------
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = XLCalc
End With
'--------------------------------------------------------------------------------------------------------------------------------------
'END
'--------------------------------------------------------------------------------------------------------------------------------------
End Sub

The red items are the one's I have changed...

The data I am trying to reference on my worksheet is in column C and consists of text like: WB0507-10

I need to separate that into 2 separate variable to link to 2 fields in the OPERATION table... the first field would be for WORKORDER_BASE_ID and should be B0507, the second for SEQUENCE_NO should be 10...

These 2 variable are looked up to return the USER_1 to USER_10 fields.

The code runs, but returns no results at all, and I can't figure out where I have gone wrong... the MID() functions should be extracting the info correctly.. I think...