Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



Simple purchase order system in excel

Posted by Martin Mc on August 30, 2001 7:57 AM
Hi

Is it possible to have a simple purchase order excel sheet which allows me to generate a unique numbering system. I would also need to export the information to a database which stores p.o. number, date created, and the amount ordered.

I am not very familiar with SQL or Access so i hope it is possible to do something simple through excel.

Thank you for any help you may be able to give me.

Martin


Check out our Excel VBA Resources

Re: Simple purchase order system in excel

Posted by Dax on August 30, 2001 10:28 AM

Hello,
What you could do is something like this:-

Say you're PO number is in cell A1 of your worksheet you could use a little VBA code to increase this number by 1 each time a new PO is requested.

Start by opening the VB editor (Alt+F11), then click View, Project Explorer (it may already be visible).

In the project explorer double click the ThisWorkbook icon associated with your PO workbook.

You'll be presented with a blank code module. Now assuming your sheet name is 'PO'. Try this code:-

Private Sub Workbook_Open()
Sheets("PO").Range("A1").Value = Sheets("PO").Range("A1").Value + 1
ThisWorkbook.Save
End Sub

This means that the workbook needs to be closed and then opened again if you want to create another purchase order. You could have a button on your worksheet and then assign a similar macro to the one above.

HTH,
Dax.


Re: Simple purchase order system in excel

Posted by Martin on September 03, 2001 2:45 AM

Yes, that's great for the numbering. Is there anyway of recording the date and the po description in another file?
Martin




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.