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

**NBVC** · 01-07-2010, 04:46 PM

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...

**StephenR** · 01-07-2010, 04:57 PM

Probably out of my depth, but doesn't MID need a string argument rather than a range?

**NBVC** · 01-07-2010, 05:08 PM

I am still pretty useless when it comes to VBA... so I am not quite sure...

What I need to do is extract the 2-6th chars and make them the lookup range, and also the 8-9th chars and make them another range as those are the fields entries I need to look up.

Any suggestions on how to "convert" those into my arrays of interest?

Edit: Actually, the 2nd set of chars is not really 8-9th, its 8th to first space in cell... could be 9th or 10th character...

I also tried putting back

Set vrngData = Mid(Range("C7", Cells(Rows.Count, "C").End(xlUp)), 2, 5)

and

Set wrngData = Mid(Range("C7", Cells(Rows.Count, "C").End(xlUp)), 8, 2)

to

Set vrngData = Range("C7", Cells(Rows.Count, "C").End(xlUp))

and

Set wrngData = Range("C7", Cells(Rows.Count, "C").End(xlUp))

and then changing the concatenation From:

strSQL = strSQL & "CONCAT(CONCAT(WORKORDER_BASE_ID,':'),SEQUENCE_NO) AS ""TEMPKEY_ID"" "

to

strSQL = strSQL & "CONCAT(CONCAT(""W"",WORKORDER_BASE_ID,':'),""-"",SEQUENCE_NO) AS ""TEMPKEY_ID"" "

to no avail

**StephenR** · 01-07-2010, 06:03 PM

I will have a think. One inelegant method would be to extract the required characters of each cell in col C into col D or whatever with MID and then operate on that. On the SQL front I'm afraid I'm entirely ignorant!

**DonkeyOte** · 01-08-2010, 06:35 AM

I'll confess I'm not entirely sure I follow (my standard opening gambit) but it seems as though for step 1 you're basically looking to use the C range to create your SQL IN strings, correct ?

We can / should I believe consider the subsequent process of matching output as step 2 ?

So regards "Step 1"...

The key part of the code relates to the generation of the IN strings themselves, namely vstrIDS and wstrIDS as it is these (in the original) that are key in generating the output.

vrngData / wrngData are Ranges - modification of their content is not really of importance at this stage given the content is handled in the ID variables so (using "v" variables only from this point forth for sake of illustration) vrngData should be:

Set vrngData = Range("C7", Cells(Rows.Count, "C").End(xlUp))

vrngChunkData / vrngSubData should be left as they are - these are smaller sub sections of the main range (ie used in conjunction with your BlockSize constant)

With the above corrected we then come to the important bit and that is seemingly the extraction of the relevant parts of each string for use in the SQL IN string clause - at this stage we're then dealing with the smaller subset range namely vrngSubData

The original used:

vstrIDs = "'" & Join(Application.WorksheetFunction.Transpose(vrngSubData.Value), "','") & "'"

which simply joins all of the values in the range together using a predetermined delimiter to create the string

given we can't use the entire value in this version (ie for vstrIDs we want chars 2 to 6 of each value) we need to modify this somewhat and one way would be to replace the above with something like:

With vrngSubData
    vstrIDs = "'" & Join(Application.Transpose(Evaluate("IF(ROW(" & .Address & "),MID(" & .Address & ",2,5))")), "','") & "'"
End With

given the presumed string lengths of values in C I don't envisage the use of Evaluate to cause too many problems.

If you can provide some samples of the values in C and the respective values you wish to extract for use with the wstrIDS we can put together an appropriate Evaluate call to generate that SQL IN string also.

If we can configure the SQL IN strings correctly such that the SQL is firing as intended then I would hope that we can resolve Step 2 as in theory that should be a case of simply modifying the below line:

.Find "TEMPKEY_ID = '" & c.Value & ":" & c.Offset(0, 1).Value & "'"

in respect of the fact that the TEMPKEY_ID is in fact based on the two sub strings of c.Value (ie chars 2 to 6 etc...) and needs to be revised accordingly.

**NBVC** · 01-08-2010, 12:19 PM

Hi there DonkeyOte,

Thanks for this assistance. It is highly appreciated.

Sample data looks like this:

WP0539-20 M

WP0539-30 E

WS0759-110 M

WS0850-120 M

WS0950-10 M

WS0950-30 E

ie. They always start with "W". I need the next 5 chars for the first IN string and then a dash, then 2 or 3 digits (this is needed for the second IN string) then a space then either an "M" or "E".

Also note, they are not in consecutive cells... not sure if that will affect anything..

**DonkeyOte** · 01-08-2010, 12:44 PM

Using the ranges per the prior post - in *theory* the IN strings can be created as so:

With vrngSubData
    vstrIDs = "'" & Join(Application.Transpose(Evaluate("IF(ROW(" & .Address & "),MID(" & .Address & ",2,5))")), "','") & "'"
    wstrIDs = "'" & Join(Application.Transpose(Evaluate("IF(ROW(" & .Address & "),TRIM(MID(" & .Address & ",FIND(""-""," & .Address & "&""-"")+1,3)))")), "','") & "'"
End With

However... this bit needs clarification:

Originally Posted by NBVC

Also note, they are not in consecutive cells... not sure if that will affect anything..

Are you saying (as inferred by your sample) that you have blank rows between the valid data ... this may preclude use of Evaluate etc and may necessitate iteration of each item within the Range when building the IN strings.

Are you able to post up a small sample file of just the C values so I can see the setup - presumably this would not contravene any confidentiality clauses ?

EDIT: I added a hyphen in the FIND to prevent a debug...

**NBVC** · 01-08-2010, 12:56 PM

I tried playing around to make that 2nd statement using MID/FIND functions to come up with:

 With wrngSubData
    '=MID(C7,8,FIND(" ",C7)-FIND("-",C7)-1)
        wstrIDs = "'" & Join(Application.Transpose(Evaluate("IF(ROW(" & .Address & "), MID(" & .Address & ",8, FIND("" ""," & .Address & ")-FIND(""-""," & .Address & ")-1 )))")), ",'") & "'"
    End With

and used your first coed for wrngSubData .. but still coming up blank.

Attached is the template with those items filled in Column C.. I need results to show up anywhere at column P and onwards...

Thanks again.

**NBVC** · 01-08-2010, 01:00 PM

I tried your last code...

It gave results, though each row had the same results as the first.. i.e.It's taking the results for C7 and copying them down...

... Also every row is filled in. I just need it to fill where there is a job number in column C.

**NBVC** · 01-08-2010, 03:10 PM

Well, not sure what when on there... I just saved, closed the workbook, re-opened and re-ran the macro and now it displays on the correct rows...skipping when it should... good, that part works well now, I apologize for the scare...

However, it is still is producing incorrect results:

Here is the output:

C~		06/25/09	10/14/09
			
			
C~C		01/29/09	01/23/09
			
			
~		12/22/2009	12/22/2009
			
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09
			
			
C~C		01/29/09	01/23/09

The first one is correct, after that the next few are incorrect, and then for some reason the same data is getting pulled right to the end.

**NBVC** · 01-08-2010, 03:40 PM

It is telling me that the FROM key is not found where expected... I fixed the spacing...

and the debug.print only displayed the Select statement

I understand about you leaving ... we can continue later.... no problem. I have access to my work desktop from home so I can do testing on Weekend or Monday...

**DonkeyOte** · 01-08-2010, 03:45 PM

probably because I added line breaks for visibility - copy directly from the Immediate Window and see what happens... again I can't test myself I'm afraid.

(debug.print should show all of it - will be in one l-o-n-g string)

**NBVC** · 01-08-2010, 04:32 PM

This is what I copy from Immediate Window

SELECT 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, CONCAT(CONCAT(WORKORDER_BASE_ID,':'),SEQUENCE_NO) AS "TEMPKEY_ID" FROM OPERATION WHERE 1=1 AND WORKORDER_TYPE = 'M' AND WORKORDER_BASE_ID In ('') AND SEQUENCE_NO In ('')

Is something missing in the "In" strings?

Also it gives Oracle error stating FROM keyword is not where it is expected to be

**DonkeyOte** · 01-08-2010, 04:37 PM

Yes, the strIDs are completely blank - I've sent you a PM - off for some food - will check back later.

**NBVC** · 01-11-2010, 12:43 PM

Ok, for sake of proper thread closure, this has been solved. DonkeyOte has helped me immensely and I apologize to him for the extra hair he might of lost on this one...

Here is the final working code:

Option Explicit
Sub GetDataFromVisual_DO_Last()
'--------------------------------------------------------------------------------------------------------------------------------------
'Defining variables
'--------------------------------------------------------------------------------------------------------------------------------------
Dim cnOra As ADODB.Connection
Dim rsOra As ADODB.Recordset
Dim vrngData As Range, vrngChunkData As Range, vrngSubData As Range, c As Range
Dim db_name As String, UserName As String, Password As String, vstrIDs As String, wstrIDs As String, strSQL As String, strKey As String
Dim XLCalc As XlCalculation: XLCalc = Application.Calculation
Dim i As Integer
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
'--------------------------------------------------------------------------------------------------------------------------------------
Set vrngData = Range("C7", Cells(Rows.Count, "C").End(xlUp))
Set vrngChunkData = vrngData.Offset(0, 0).Resize(BLOCK_SIZE)
Set vrngSubData = Intersect(vrngData, vrngChunkData)
'--------------------------------------------------------------------------------------------------------------------------------------
'Loop Subset of vrngData
'--------------------------------------------------------------------------------------------------------------------------------------
Do While Not vrngSubData Is Nothing
    'Debug.Print vrngSubData.Address
    With vrngSubData
        vstrIDs = Join(Application.Transpose(Evaluate("IF(" & .Address & "<>"""",MID(" & .Address & ",2,5),"" "")")), " ")
        vstrIDs = "'" & Replace(Application.WorksheetFunction.Trim(vstrIDs), " ", "','") & "'"
        wstrIDs = Join(Application.Transpose(Evaluate("IF(" & .Address & "<>"""",TRIM(MID(" & .Address & ",FIND(""^^"",SUBSTITUTE(" & .Address & ",""-"",""^^"",LEN(" & .Address & ")-LEN(SUBSTITUTE(" & .Address & ",""-"",""""))))+1,3)),"" "")")), " ")
        wstrIDs = "'" & Replace(Application.WorksheetFunction.Trim(wstrIDs), " ", "','") & "'"
    End With
    '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, "
        strSQL = strSQL & "CONCAT(CONCAT(WORKORDER_BASE_ID,':'),SEQUENCE_NO) AS ""TEMPKEY_ID"" "
        'strSQL = strSQL & "TEMPKEY_ID " 'MYSQL MODIFICATION (had to create fixed field rather than generate @ run time)
    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(Cells(2, "AB"), Cells(Rows.Count, "AL")).ClearContents
    
 
    'Iterate Subset of vrngData and find matching record in Recordset (if exists)
    For Each c In vrngSubData
        If c.Value <> "" Then
            strKey = "'" & c.Offset(, 13).Value & ":" & c.Offset(, 14).Value & "'"
            c.Offset(0, 25).Value = strKey
            With rsOra
                .Find "TEMPKEY_ID = '" & Mid(c.Value, 2, 5) & ":" & Trim(Mid(c.Value, 8, 3)) & "'"
                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
        End If
    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)
Loop
'--------------------------------------------------------------------------------------------------------------------------------------
' Close
'--------------------------------------------------------------------------------------------------------------------------------------
cnOra.Close
'--------------------------------------------------------------------------------------------------------------------------------------
' Release
'--------------------------------------------------------------------------------------------------------------------------------------
Set vrngData = Nothing
Set vrngChunkData = Nothing
Set vrngSubData = Nothing
'--------------------------------------------------------------------------------------------------------------------------------------
'Restore App Level Settings
'--------------------------------------------------------------------------------------------------------------------------------------
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = XLCalc
End With
'--------------------------------------------------------------------------------------------------------------------------------------
'END
'--------------------------------------------------------------------------------------------------------------------------------------
Exit Sub

End Sub

Thanks DonkeyOte !

**NBVC** · 01-11-2010, 12:59 PM

... funny because it seems to work as is.....

Should I just change using something like:

TRIM(MID(c.Value,FIND("^^",SUBSTITUTE(c.Value,"-","^^",LEN(c.Value)-LEN(SUBSTITUTE(c.Value,"-",""))))+1,3))

I did try that, and not surprisingly... didn't work.

**DonkeyOte** · 01-11-2010, 01:05 PM

It will work as long as the 2nd hyphen (where it exists) does so in position 8 of the string.

A safer method perhaps might be to create a Variant variable and Split the c.value based on hyphen and work from that, ie:

Dim vKey As Variant
'....
For Each c In vrngSubData
    If c.Value <> "" Then
        vKey = Split(c.Value,"-")
        .Find "TEMPKEY_ID = '" & Mid(c.Value, 2, 5) & ":" & Val(vKey(UBound(vKey))) & "'"
        ....
    End If
Next c

I believe that should work if as it seems the SEQUENCE_NO is always numeric and appears after the last hyphen in the string.

**NBVC** · 01-11-2010, 01:07 PM

Changing to this works:

.Find "TEMPKEY_ID = '" & Mid(c.Value, 2, 5) & ":" & Trim(Mid(c.Value, Application.WorksheetFunction.Find("^^", Application.WorksheetFunction.Substitute(c.Value, "-", "^^", Len(c.Value) - Len(Application.WorksheetFunction.Substitute(c.Value, "-", "")))) + 1, 3)) & "'"

**NBVC** · 01-11-2010, 01:09 PM

Your last suggestion worked too! and, of course, shorter and probably more effective and efficient.

Thanks.

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions