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

Please Login or Register  to view this content.

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

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

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

and then changing the concatenation From:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

to no avail

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

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

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

Please Login or Register  to view this content.

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

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

Please Login or Register  to view this content.

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.

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.

I confess at the moment I'm concerned only with ensuring the IDs strings are being generated correctly such that the SQL query itself is valid - IMO validating the resulting recordset against the values in C is another step and one that can only really be done as and when the output is definitely accurate.


EDIT: initial code below replaced by post #11

Below is a revised section for generating the IDs which should account for (ie remove) the blanks in C:

Please Login or Register  to view this content.

the column in red is where it will return the strings for your review (modify as desired) - for sake of validation in this testing period (to be removed once successful)

are the codes generated correct ?

In retrospect the prior code would not work in all circumstances should a blank appear in the first/last cell within a block being processed... I think perhaps the below is more robust:

Please Login or Register  to view this content.

...let me know if indeed the above generates the correct code strings.

They appear to be correct, yes:

Please Login or Register  to view this content.

listed horizontally in 2 separate rows.

Apologies for delay - have just returned from running down the road to help my wife dig the car out of the snow and get up the hill near our house... !

OK, so given we assume step 1 is working ok (even the last two records?) - onto step 2 ... does the below work ?

Please Login or Register  to view this content.

Caveat: as per the original this is obviously untested given I don't have an Oracle DB (nor your data!) to test against - so it's a fingers crossed type affair.

Hi,

It outputs, but doesn't look right.

There is some variation at the top, but then continues the same data all the way down...

... it's allso putting data on all rows.

OK, so given we assume step 1 is working ok (even the last two records?

You caught that... I noticed it too after I posted the data here... those 2 have 2 dashes.. I guess I really need the second IN piece to take from last dash to first blank. The first parts is still right.... sorry for not catching that. We can concentrate on getting data first and then if that is an easy adjustment, we can try to do that..

Originally Posted by NBVC

... it's allso putting data on all rows.

that part I don't get as in theory it should only be looking for results where the C value is non-blank, ie:

Please Login or Register  to view this content.

and your project template sheet from earlier definitely shows these cells as being blank so I confess I'm baffled by the fact that you're getting results returned in every row (based on your sample)....

for the sake of debugging... if you add say

Please Login or Register  to view this content.

do the "blank" cells get listed etc in the Immediate Window ? (C8, C9 etc...)

Couple of additional questions...

1 - I presume you cleared the old results before you re-ran the latest code ? (at present there's nothing in the code to clear the old results should they exist)

2 - Can you put the SQL output for the TEMPKEY_ID into a file of some sort (.csv/.txt) - I'm guessing you can do that by pushing the SQL into MS Query direct.... I'm not fussed about the other fields being returned so there should not be any confidentiality issues as I see it.

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:

Please Login or Register  to view this content.

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.

Originally Posted by DonkeyOte

Couple of additional questions...

1 - I presume you cleared the old results before you re-ran the latest code ? (at present there's nothing in the code to clear the old results should they exist)

2 - Can you put the SQL output for the TEMPKEY_ID into a file of some sort (.csv/.txt) - I'm guessing you can do that by pushing the SQL into MS Query direct.... I'm not fussed about the other fields being returned so there should not be any confidentiality issues as I see it.

You are probably right, I did delete those columns before I saved, so I could start fresh.. that was probably it...

Not sure what you are asking for in 2.... is it the results I just posted above?

Not sure I understand the earlier output in terms of what each column represents...

What I was after was... if you were to load the resulting SQL statement into MS Query and run it could you in turn export the output to another file and upload it - removing all but the TEMPKEY_ID column ?

I basically wanted to see what the recordset looked like such that I could in turn compare to the ID IN strings.

The SQL statements will be in the Immediate Window c/o the debug.print statement in the code... failing that - based on the sample file you posted it would be (beware the spurious space):

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 ('B0309','B0311','H0068','H0069','P0430','P0493','P0528','P0528','P0529','P0529','P0529','P0529','P0534','P0534','P0535')
AND SEQUENCE_NO In ('20','10','10','10','90','220','10','20','10','20','30','40','10','20','10')

As to why each c.value is assuming the prior result... not sure...

(given the Oracle construct I guess above might fail in MS Query but I thought from earlier thread it worked... hence not using | for concatenation (failed in MS Query))

Or maybe he attached CSV may give you an idea...


Note: Many columns are currently blank.. They will be filled in eventually, this is the reason for this exercise. So that these User defined fields can be used to store random data and be printed on the report I showed earlier on a weekly basis for production meeting.

Looks good but no TEMPKEY_ID in that .csv ... and that's the one we're really interested in I think at this particular juncture.

I'm going to head off soon else wife will shoot me...

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

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)

This is what I copy from Immediate Window

Please Login or Register  to view this content.

Is something missing in the "In" strings?

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

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

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:

Please Login or Register  to view this content.

Thanks DonkeyOte !

NBVC, as discussed I think there is still one line you need to update, ie the below:

Please Login or Register  to view this content.

needs to be modified in line with the revisions to wstrID creation.

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

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:

Please Login or Register  to view this content.

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

Changing to this works:

Please Login or Register  to view this content.

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

Thanks.