Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoSequencing in a New Worksheet
Rahul:
With a slight mod, it will save the new workbook to the same folder as the previous workbook. Also, the procedure is now called by Workbook Open and some other code cleanup. Private Sub Workbook_Open() NewPurchaseOrder End Sub Sub NewPurchaseOrder() Dim vNum As Variant, vNewName As Variant Dim y As Boolean, sPath As String Dim fso As New FileSystemObject Set fso = CreateObject("Scripting.FileSystemObject") sPath = ThisWorkbook.Path With ThisWorkbook.Worksheets("sheet1") If .Range("B4").Value = "" Or _ Not IsNumeric(.Range("B4").Value) Then 'provide a default purchase order starting number .Range("B4").Value = "10000" Else .Range("B4").Value = .Range("B4").Value + 1 vNum = .Range("B4").Value End If End With Do vNewName = sPath & "\" & vNum & ".xls" y = fso.fileexists(vNewName) If y = True Then vNum = vNum + 1 End If Loop Until y = False ThisWorkbook.Worksheets("Sheet1").Range("B4").Valu e = vNum ThisWorkbook.SaveAs Filename:=vNewName End Sub Regards Jim Feaver "Jim Feaver" wrote in message news:sXcUb.390873$JQ1.275776@pd7tw1no... Rahul: One more thing: Use of FileSystem Object requires that the workbook has a reference to Microsoft Scription Runtime. (Tools, References....) Jim "Jim Feaver" wrote in message news:S27Ub.390074$JQ1.358834@pd7tw1no... Rahul: It appears that you are using one purchase order per workbook. This works if the purchase order number is located in range B4 of Sheet1. As it stands, the "#" would need to be in B3, unless you add parsing code). Here is some code that can be in Workbook_Open event procedure or else in a sub procedure called by it. Private Sub Workbook_Open() Dim vNum As Variant, vNewName As Variant Dim y As Boolean Dim fso As fileSystemObject Set fso = CreateObject("Scripting.FileSystemObject") With ThisWorkbook.Worksheets("sheet1") MsgBox .Range("B4").Value If .Range("B4").Value = "" Or _ Not IsNumeric(.Range("B4").Value) Then .Range("B4").Value = "10000" Else .Range("B4").Value = .Range("B4").Value + 1 vNum = .Range("B4").Value End If End With vNewName = vNum & ".xls" Do y = fso.fileexists(vNewName) If y = True Then vNum = vNum + 1 vNewName = vNum & ".xls" End If MsgBox vNewName, , "vNewName" Loop Until y = False ThisWorkbook.Worksheets("Sheet1").Range("B4").Valu e = vNum ThisWorkbook.SaveAs Filename:=vNewName End Sub Regards, Jim Feaver "rahul3400 " wrote in message ... Hello Guys, I hope I could get some help! I have an excel work-sheet, on the top-right corner of which there is a field called "PURCHASE ORDER #" with puchase order # 10001 The file is saved with the same name as its purchase order # (10001.xls). IS THERE A WAY TO AUTOMATICALLY PUT IN NEXT-IN-ORDER PURCHASE ORDER #, ON THE NEW .XLS SHEET AS SOON AS YOU OPEN IT i.e. if the old sheet you saved had number 10001, could we make new .xls sheet that we open, have 10002 as default in the Purchase order field and so on. Also, could we save the newly opened file at a location without hitting "Save" or "Save-As", with that new number (10002.xls) ? Thanks, Your help and advise will be sincerely appreciated! --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically appending newly added data on worksheet to a master list worksheet | Links and Linking in Excel | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | New Users to Excel | |||
Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions | |||
I want in one worksheet to relatively link to/reference cells in another without changing the format of the current worksheet. | Excel Discussion (Misc queries) | |||
Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet | Excel Programming |