Have anyone successfully pulled data from progress to excel via

system · June 11, 2009, 9:09am

If you have the parts list in excel, use 'ADO' thru an ODBC connection in VBA:
Â
Â
Â -Loop thru part numbers:
Â Â Â Â -Â For eachÂ part number , pass the part number to a function which use a SQL query to 'hit' the PartBin table , return the 'qnty'

--- On Wed, 6/10/09, bruce_erpgeek <epicor@...> wrote:

From: bruce_erpgeek <epicor@...>
Subject: [Vantage] Re: Have anyone successfully pulled data from progress to excel via ODBC VB funct.?
To: vantage@yahoogroups.com
Date: Wednesday, June 10, 2009, 9:09 PM

This may be a slightly odd way of solving this problem, but, using the system capability of checking inventory requirements for a job.

Step 1
Create a job for part on the fly
Step 2
Copy and paste the list of parts you want to check the inventory for into the material for the job.
Step 3
Thru the action menu, Check inventory for the parts for the job.
or run a pick list.

Copy this to excel.

Step 4
Delete the job - you don't want to cause a problem with MRP. :)

Bruce

--- In vantage@yahoogroups .com, "sh4m4n71x" <lunar@...> wrote:
>
> I have a long list of part numbers that I need the current on hand qty. Instead of checking each number individually I was wondering if anyone have created or used a VB function in excel that would pull this info via ODBC from the PartBin table
>
> Example.
>
>
> "Part Number" , ="function that pulls the current QTY from PartsBin based on the part number from row1"
>
> Any info as how to accomplish this would be appreciated.
>
> Thanks,
> Tom.
>

[Non-text portions of this message have been removed]

system · June 10, 2009, 2:41pm

I have a long list of part numbers that I need the current on hand qty. Instead of checking each number individually I was wondering if anyone have created or used a VB function in excel that would pull this info via ODBC from the PartBin table

Example.

"Part Number" , ="function that pulls the current QTY from PartsBin based on the part number from row1"

Any info as how to accomplish this would be appreciated.

Thanks,
Tom.

system · June 10, 2009, 2:48pm

Can you use a BAQ, Export to csv and open in Excel?

Karen S

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of sh4m4n71x
Sent: Wednesday, June 10, 2009 1:41 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Have anyone successfully pulled data from progress to
excel via ODBC VB funct.?

I have a long list of part numbers that I need the current on hand qty.
Instead of checking each number individually I was wondering if anyone
have created or used a VB function in excel that would pull this info
via ODBC from the PartBin table

Example.

"Part Number" , ="function that pulls the current QTY from PartsBin
based on the part number from row1"

Any info as how to accomplish this would be appreciated.

Thanks,
Tom.

[Non-text portions of this message have been removed]

system · June 10, 2009, 2:55pm

This was the first thing I did, but it does not solve my problem since I still have to go through the whole inventory in excel and search\select the parts I need the QTY for.

As opposed to a function which would do it relatively quickly.

--- In vantage@yahoogroups.com, "Karen Schoenung" <kschoenung@...> wrote:
>
> Can you use a BAQ, Export to csv and open in Excel?
>
>
>
> Karen S
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of sh4m4n71x
> Sent: Wednesday, June 10, 2009 1:41 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Have anyone successfully pulled data from progress to
> excel via ODBC VB funct.?
>
>
>
>
>
>
>
>
> I have a long list of part numbers that I need the current on hand qty.
> Instead of checking each number individually I was wondering if anyone
> have created or used a VB function in excel that would pull this info
> via ODBC from the PartBin table
>
> Example.
>
> "Part Number" , ="function that pulls the current QTY from PartsBin
> based on the part number from row1"
>
> Any info as how to accomplish this would be appreciated.
>
> Thanks,
> Tom.
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

system · June 10, 2009, 3:00pm

You could then use the vlookup function in Excel...Have the long list of
part numbers on another worksheet (or workbook) and have them lookup the
Qty in the CSV file from the BAQ...???

Sorry, I have not worked with an ODBC connection to pull data yet.

Karen S

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of sh4m4n71x
Sent: Wednesday, June 10, 2009 1:55 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Have anyone successfully pulled data from
progress to excel via ODBC VB funct.?

This was the first thing I did, but it does not solve my problem since I
still have to go through the whole inventory in excel and search\select
the parts I need the QTY for.

As opposed to a function which would do it relatively quickly.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Karen Schoenung" <kschoenung@...> wrote:

>
> Can you use a BAQ, Export to csv and open in Excel?
>
>
>
> Karen S
>
>
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>

[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf

> Of sh4m4n71x
> Sent: Wednesday, June 10, 2009 1:41 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Have anyone successfully pulled data from progress

to

> excel via ODBC VB funct.?
>
>
>
>
>
>
>
>
> I have a long list of part numbers that I need the current on hand

qty.

> Instead of checking each number individually I was wondering if anyone
> have created or used a VB function in excel that would pull this info
> via ODBC from the PartBin table
>
> Example.
>
> "Part Number" , ="function that pulls the current QTY from PartsBin
> based on the part number from row1"
>
> Any info as how to accomplish this would be appreciated.
>
> Thanks,
> Tom.
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

[Non-text portions of this message have been removed]

system · June 10, 2009, 3:01pm

If you have ODBC already configured then just pull in the partbin partnumber
and onhand quantity into a sheet. Unless you have over 65K partbins
present.

Then do a VLOOKUP from your original list.

Because you may have multiple bins per part this would be easier with a
standard query in Access.

1. import your table in to access
2. connect to partbin with ODBC linked tables
3. write a query that connects the two tables together on part number.
Display partnumber in query.
4. export to XLS

Ross

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
sh4m4n71x
Sent: Wednesday, June 10, 2009 1:41 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Have anyone successfully pulled data from progress to
excel via ODBC VB funct.?

I have a long list of part numbers that I need the current on hand qty.
Instead of checking each number individually I was wondering if anyone have
created or used a VB function in excel that would pull this info via ODBC
from the PartBin table

Example.

"Part Number" , ="function that pulls the current QTY from PartsBin based on
the part number from row1"

Any info as how to accomplish this would be appreciated.

Thanks,
Tom.

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links

system · June 10, 2009, 3:14pm

Tom,

You might consider a 2-tab workbook with the first tab being an ODBC
call to the database to get the PartBin information for Qty>0. The
query could be set to auto-refresh on file open. On the second tab put
the list of parts that you want to evaluate and use the SUMIF command to
index the part number from the first tab.

I have sent you a copy of a worksheet off-list to give you an idea of
what I'm thinking.

Best regards,

Bill

William Hannah

Director of Finance

William Frick & Company

Direct: 847.918.7338

www.fricknet.com

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of sh4m4n71x
Sent: Wednesday, June 10, 2009 1:55 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Have anyone successfully pulled data from
progress to excel via ODBC VB funct.?

This was the first thing I did, but it does not solve my problem since I
still have to go through the whole inventory in excel and search\select
the parts I need the QTY for.

As opposed to a function which would do it relatively quickly.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Karen Schoenung" <kschoenung@...> wrote:

>
> Can you use a BAQ, Export to csv and open in Excel?
>
>
>
> Karen S
>
>
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>

[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf

> Of sh4m4n71x
> Sent: Wednesday, June 10, 2009 1:41 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Have anyone successfully pulled data from progress

to

> excel via ODBC VB funct.?
>
>
>
>
>
>
>
>
> I have a long list of part numbers that I need the current on hand

qty.

> Instead of checking each number individually I was wondering if anyone
> have created or used a VB function in excel that would pull this info
> via ODBC from the PartBin table
>
> Example.
>
> "Part Number" , ="function that pulls the current QTY from PartsBin
> based on the part number from row1"
>
> Any info as how to accomplish this would be appreciated.
>
> Thanks,
> Tom.
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

--------------------------------------------------------------------------

William Frick & Co's SmartMarkT RFID Named to 2008 Top Ten Products List

[Non-text portions of this message have been removed]

system · June 10, 2009, 6:55pm

Go with the SUMIF() function ifÂ the PartBin data doesn't exceed the 65k row limit. VLOOKUP() will also work but is slower to recalc.

You can write your own ODBC function in VBa (example code abounds on microsoft's technet and MSDN sites) but you probably don't want to do that as it will result in multiple attempts to open the connection & query for each associated cell PartNumber. (Contention will drag down your db server.)

The only way around that is to do EVERYTHING in your VBa function using arrays to get all your PartNumbers in your cell range & then one at a time query for the part bin o/h's & fill in the array with the results. Displaying the results is even more fun :(

That would stillÂ be much slower than SUMIF() or VLOOKUP() - and still risk swamping your db server.

It's really only a useful technique if you effectively create an excel form to act as an application UI for users to manipulate one record at a time.

RobÂ

________________________________
From: sh4m4n71x <lunar@...>
To: vantage@yahoogroups.com
Sent: Wednesday, June 10, 2009 2:41:26 PM
Subject: [Vantage] Have anyone successfully pulled data from progress to excel via ODBC VB funct.?

I have a long list of part numbers that I need the current on hand qty. Instead of checking each number individually I was wondering if anyone have created or used a VB function in excel that would pull this info via ODBC from the PartBin table

Example.

"Part Number" , ="function that pulls the current QTY from PartsBin based on the part number from row1"

Any info as how to accomplish this would be appreciated.

Thanks,
Tom.

[Non-text portions of this message have been removed]

system · June 10, 2009, 9:10pm

This may be a slightly odd way of solving this problem, but, using the system capability of checking inventory requirements for a job.

Step 1
Create a job for part on the fly
Step 2
Copy and paste the list of parts you want to check the inventory for into the material for the job.
Step 3
Thru the action menu, Check inventory for the parts for the job.
or run a pick list.

Copy this to excel.

Step 4
Delete the job - you don't want to cause a problem with MRP. :)

Bruce

--- In vantage@yahoogroups.com, "sh4m4n71x" <lunar@...> wrote:
>
> I have a long list of part numbers that I need the current on hand qty. Instead of checking each number individually I was wondering if anyone have created or used a VB function in excel that would pull this info via ODBC from the PartBin table
>
> Example.
>
>
> "Part Number" , ="function that pulls the current QTY from PartsBin based on the part number from row1"
>
> Any info as how to accomplish this would be appreciated.
>
> Thanks,
> Tom.
>