My question is pretty much identical to the related one. I would like to be able to create a template to use for invoices; also each time it is used again it has the next sequential number. Ive never used the "View Code" option in excel so a step by step procedure/ code to do this would be very helpful if anybody knows how to use and create this.
http://www.experts-exchang
This question had the best responses I could make sense of but was still unsure of some things and what I tried did not work. Also, I wasnt sure if this would work with invoices (or purchase orders in my case) being pulled up on different computers and saved to a server.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
http://www.mcgimpsey.com/e
This site has what I think I need with the txt file saved on the server but I do not know how to link the txt file to the template...Lets say I create my template, then the txt file, how do I make it work? I understand VB must be involved.
TVanLanen.
There is really nothing to set up.
Just make the Excel Template and the text file.
Thats it.
You said it yourself:
"you create a template in excel..then the text file."
...After that, you drop the first function into a new code module.
Then drop the "WorkBook_Open code in the "ThisWorkBook" module.
Then simply open the workbook to see the next sequeuntial number.
I took me all of 2 1/2 minutes to do, and it worked fine.
;-)
Do you need help with the Visual Basic aspects of this, or something else?
JeffCoachman
yes the Visual basics .. sorry I havent gotten back to you till now, ive been busy with several projects and my other job. If you could just bring me down to the lamens terms step by step that'd be great. Ive never used Visual Basics period. Ive just stumbled upon the fact that you can do this recently.
Copy the Public Function NextSeqNumber from your link
Open Excel and Pres: Alt F11
...To enter the VBA editor
On the left hand side of the sceen, Double the name of your Excel file
Then click Insert-->Module
Paste the module code in.
Change this code in the module:
Const sDEFAULT_PATH As String = "<your path here>"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
To something like this, to match the attributes of your textfile:
Const sDEFAULT_PATH As String = "c:\YourFolder\"
Const sDEFAULT_FNAME As String = "YourTextFileName.txt"
Again, on the left hand side, double click: "This workbook".
Then near the top near the middle you will see "General"
Click the dropdown and chage it to: "ThisWorkbook"
You will see something like this:
Private Sub Workbook_Open()
End Sub
Insert this:
ThisWorkbook.Sheets(1).Ran
Click: File-->Close and Return to Excel
Save
Exit Excel
Reopen the file and the next number should appear in the cell
JeffCoachman
I know its been ages and i dont mean to ingnore your help. I apologize. I followed your directions, but then after saving the file and trying to reopen it, it prompts me to enable macros. I do so, then I receive a Microsoft Visual Basic Runtime error '52':
Bad file name or number with the options: coninue(greyed out), end, debug, and help. I select debug and it brings me to the VB editor window with a line high-lighted. Ive attached the screenshot. Its kinda obvious that it does not like my file path. Can I use a network share for this because this is where I placed my file on my server. I typed the path \\Newserver\users\travis..
screen shot
TVanLanen,
(Just FYI, try to post jpg images instead of bitmaps, jpgs can be over 10 times smaller than bitmaps)
Let's keep this simple and make sure it works on your local machine, BEFORE worying about network drives, Application.PathSeparators
Can you put "AutoSequence.txt" on your local machine, and test it this way first?
Let me know the outcome?
JeffCoachman
I saved the file to the local harddrive and renamed the path c:\autosequence.xls as well as the txt file...still same exact error as before.
Visual Basic
sorry I havent replied since last year...I removed the carrot brackets and force a different error
compile error: syntax error, after clicking ok It highlights the top line in yellow and my path in red
Business Accounts
Answer for Membership
by: TVanLanenPosted on 2008-09-24 at 14:27:11ID: 22564208
To clarify I want to make a purchase order template with a p.o # that resides in a certain field. After completing the p.o. and then saving it as a different file, the template would generate a new p.o number in the field specified next time it is opened. Any ideas?