| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Sequecing in New Work Sheet
Hello Guys,
I hope I could get some help! I have an excel work-sheet, on the top-right corner of which there is 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 yo saved had number 10001, could we make new .xls sheet that we open, hav 10002 as default in the Purchase order field and so on. Also, could we save the newly opened file at a location without hittin "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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto Sequecing in New Work Sheet
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/ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto Sequecing in New Work Sheet
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/ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto Sequecing in New Work Sheet
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/ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto Sequecing in New Work Sheet
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 | |||
| Auto PRINT file path & Date on Excel work sheet ? | Excel Worksheet Functions | |||
| Auto Copy Text from one work sheet to another | Excel Worksheet Functions | |||
| extracting totals from 1 work sheet to another work work sheet | Excel Discussion (Misc queries) | |||
| auto numbering an exel work book sheet everytime it opens or print | Excel Discussion (Misc queries) | |||
| Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
Linear Mode
