Solved

Use excel data to extract cost in SQL query.

Posted on 2013-02-05
22
225 Views
Last Modified: 2013-02-12
I need to created quotes based on new products no in our database and on already existing parts in our database.  A spreadsheet will contain all the parts.  Some will match to existing db parts and the rest will have to be manually costed at our suppliers.  The ss will contain Qty,for all lines, MAX Part Number parts without cost, and Cost without part numbers.  The non existent parts can simply be multiplied by the qty for the total cost per line.  The db part numbers will be used to parse the db for their associated cost and cost placed into the spreadsheet to complete the Cost column the multiplied by Qty for each part and finally the Cost column totaled.  I simply need that Cost column total, no formatting..  I would have this ss with the macro and copy/paste the three columns and run the macro.  Here's a copy of the ss.

The initial ss would have been vetted for existing parts and non existent parts will be cross-referenced with our suppliers for our cost.
BOMQuote.xlsx
0
Comment
Question by:gibneyt
  • 12
  • 10
22 Comments
 
LVL 16

Expert Comment

by:terencino
ID: 38858154
So just to confirm, you want the macro to extract the cost of the Max Part Numbers from the database, then multiply the Qty x Cost for each line and put those values in column D for each line?

Or do you want the macro to calculate the overall total cost of all parts?
0
 

Author Comment

by:gibneyt
ID: 38859734
terencino,

Thanks for the reply.

Total cost for all parts.

My DSN is MAXRM and the table is Part_Master.  The columns are PRTNUM_01 and COST_01.

Regards,

Tim
0
 
LVL 16

Expert Comment

by:terencino
ID: 38862360
Hi Tim, attached is the update of your file, note it is XLSM now in order to contain macros. The first thing for you to do is to update the ConnectionString with the correct user ID and password, currently they don't have values:
objConn.ConnectionString = "DSN=MAXRM;UID=myuser;PWD=myPwd;"

Open in new window

Then run the macro by pressing the "Extract data from SQL" button. A message box will pop up to say if it connected OK or not. If not, it will exit. If so, it will carry on to select the data as follows. Just need to be sure the table and field names are exactly as in the database.
objCmd.CommandText = "select PRTNUM_01,COST_01 from Part_Master"

Open in new window

Any idea how many records? Because it will then put all of them in the active sheet starting at E1.

Once you can get the data we can proceed to matching the Max Part Number and extracting the cost. Note that some of the part numbers are text and some numbers, so this might be an issue.
Let me know how you go
...Terry
BOMQuote.xlsm
0
 

Author Comment

by:gibneyt
ID: 38863716
Terry,

Thank you!

I made the changes and all 6681 part numbers are listed.  Do I need to use a username and password?  Can the macro use pass-through Windows authentication?

The BOMs could be any size really.  The current list of parts grows almost every week.

Looks great so far!

Tim
0
 
LVL 16

Expert Comment

by:terencino
ID: 38863945
That's great Tim so it connected to the database and output all the parts on the active sheet? 7000 parts are OK, I was worried it might be in the 100000 range, things might get a bit slow! If your DSN is already setup with pass through Windows authentication then it should be fine, you can delete those from the connection string, just test it again to make sure it works.

So if that's all working then comment out the following line of code
ActiveSheet.Range("E2").CopyFromRecordset objRecordset

Open in new window

and un-comment this line which loads the data into an array
'a = XTranspose(objRecordset.GetRows)

Open in new window

You can uncomment the rest if you like, to see the contents of the array in the debug window.

I'll add in some more code tomorrow, which will look for the max part number in the array and enter the cost, then sum up the total cost.

You can run the TotalCostQuote macro which will generate the total cost in a message box.
Let me know if any other thoughts
...Terry
0
 

Author Comment

by:gibneyt
ID: 38865383
Terry,

The macro looks like this now:

...
a = XTranspose(objRecordset.GetRows)

'Look at the data
For i = 0 To UBound(a, 1)
  For j = 0 To UBound(a, 2)
    If j = 0 Then
      dataset = a(i, j)
    Else
      dataset = dataset & vbTab & a(i, j)
    End If
  Next j
  Debug.Print dataset
Next i
...

I get an error:

Microsoft Visual Basic
Run-time error '3021':

Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

Clicking Debug takes me to the a = XTranspose line.

Tim
0
 

Author Comment

by:gibneyt
ID: 38865411
Terry,

Oops...  I didn't comment out the ActiveSheet line.  I did so and the error stopped and can now see some of the query results in the Immediate window.

Tim.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38866798
Ok Tim, after the a = XTranspose line, can you add this:
For Each c In ActiveSheet.Range("B2:B1000")
    If c.Value = "" Then GoTo skip
    c.Offset(0, 1) = Application.WorksheetFunction.VLookup(CStr(c.Value), a, 2, False)
skip:
Next c
TotalCostQuote

Open in new window

This worked Ok for me but on a different dataset
0
 

Author Comment

by:gibneyt
ID: 38868187
terencino,

I added the code to the suggested location but when run I get an error:

Microsoft Visual Basic
Run-time error '1004':
Unable to get the VLookup property of the WorksheetFunction class

Clicking on Debug takes me to the c.Offset(0,1) line.

For clarification, the ActiveSheet.Range("B2:B1000") line determines that only 1000 rows could ever be parsed?  I don't know that it will happen, that there will be a BOM with over 1000 parts, but it is not out of the realm of possibility.

Regards,

Tim
0
 
LVL 16

Expert Comment

by:terencino
ID: 38870093
Hi Tim, can you try this instead of that code I sent before. The B1000 refers to the spreadsheet column B which has less than 200 entries, not the data returned from your database. You can extend that to 10000 if you like

On Error Resume Next
For Each c In ActiveSheet.Range("B2:B1000")
    If c.Value = "" Then GoTo skip
    c.Offset(0, 1) = Application.VLookup(c.Value, a, 2, False)
skip:
Next c
TotalCostQuote

Open in new window

0
 

Author Comment

by:gibneyt
ID: 38878138
terencino,

I see the code go through the part numbers in the immediate window then the sheet looks like it is updated with data but does not give the total cost.  The ss is attached with results after pressing the button.

Tim
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 16

Expert Comment

by:terencino
ID: 38878149
Can you send the file again Tim, and I'll check it out. Sounds like we're almost there
0
 

Author Comment

by:gibneyt
ID: 38878159
terencino,

I didn't hit that last "Attach".  Here's the file:

Tim
BOMQuote.xlsm
0
 
LVL 16

Expert Comment

by:terencino
ID: 38878173
I see. If the cost is #N/A the TotalCostQuote calculation will fail. Do any of those Max Part Numbers get returned in the database extract? To test this can you generate the list from the database in the spreadsheet starting from E1 like you did in your initial test in ID 38863716 above? Then attach the file and I'll run some checks on the data type
...Terry
0
 

Author Comment

by:gibneyt
ID: 38878179
terencino,

They shouldn't be.  That is the whole idea; parts w/o part numbers were vetted and did not show up in the db.  I'll check.

Tim
0
 

Author Comment

by:gibneyt
ID: 38878194
terencino,

From the db query:

30-025991-01                        0.04
30-026079-01                        0.14
6000771                             0.33
6000928                             0.342
7001354                             0.027
7001382                             0.0023

It looks like the data is viable.

Tim
0
 
LVL 16

Expert Comment

by:terencino
ID: 38878221
Can you try this code instead. It converts the value to a string, hopefully that is the same data type
On Error Resume Next
For Each c In ActiveSheet.Range("B2:B1000")
    If c.Value = "" Then GoTo skip
    xLookup = CStr(c.Value)
    c.Offset(0, 1).Value = Application.VLookup(xLookup, a, 2, False)
skip:
Next c
TotalCostQuote

Open in new window

0
 

Author Comment

by:gibneyt
ID: 38878240
terencino,

That didn't work either.

I see all the part numbers scroll through the immediate window and only the last few remain after the code run.  See the first sheet.   The second sheet has all part cost data.

Tim
MAXPartNumberCosts.xlsx
0
 
LVL 16

Accepted Solution

by:
terencino earned 500 total points
ID: 38878434
Right the reason it can't match as expected is because the source data is coming through like "6000771                       " where the blanks are a mix of normal spaces and non-breaking spaces. You could either strip those out in the source data, fix it in the XTranspose function or just allow for it in the macro. I've taken the last option, so here is the revised macro for you to test. The macro will take longer since it has to cycle through the dataset each time. Sorry this is taking a while, the data format is critical and I'm working partially blind on this....
On Error Resume Next
For Each c In ActiveSheet.Range("B2:B1000")
    If c.Value = "" Then GoTo skip
    For i = 0 To UBound(a, 1)
      If InStr(1, a(i, 0), c.Value, vbTextCompare) > 0 Then
        c.Offset(0, 1).Value = a(i, 1)
      End If
    Next i
skip:
Next c
TotalCostQuote

Open in new window

0
 

Author Comment

by:gibneyt
ID: 38880367
terencino,


That works.  Seeking clarification on a few things:

The columns are static but the rows could be unsorted correct?

It is possible that there could be multiple rows of the same part number.  I am assuming the macro will simply continue and Total as is?

Tim
0
 
LVL 16

Expert Comment

by:terencino
ID: 38882088
Hi Tim, the spreadsheet columns are static but you could change them in the code. For example column B is used in the lookup routine above and columns 1 & 3 in the TotalCostQuote, so relatively easy to change if you needed to restructure the spreadsheet. The rows don't need to be sorted, and no problem if there are multiple rows of the same part number
...Terry
0
 

Author Closing Comment

by:gibneyt
ID: 38882130
terencino,

Yes!  That code works!  Thank you very much.  You and EE are a great resource.

Tim
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now