1/27/2015
GetnewpartnumberfromExcelwithiLogicAutodeskCommunity
SIGNIN REGISTER
Search the Community
Advanced Search
FORUMS
ARTICLES
Browse by
IDEAS
Autodesk Community
Inventor
Inventor Customization
Get new part number from Ex
INVENTOR CUSTOMIZATION
REPLY
Message Listing
Topic Options
Previous Topic
Next Topic
Search This Board
Previous
LSA-skan
Valued Contributor
95 Posts
0 Kudos
Registered:
08-09-2012
Get new part number from Excel with iLogic
Next
Search Inventor Customiza
Options
861 Views, 22 Replies
01-28-2013 02:46 AM
Hi
I have made a configurator, that defines my part. Now i would like to add some intelligence for the Save process...
When i activate my rule it should.:
1. Go to a Excel spreadsheet and find next Empty cell in a specific collum
2. Place Title from inventor in that empty cell
3. Return the value from the cell next to this cell
3. Use this value as the new filename for my part.
Example: here i would like to get the parts tile put into B3 and A3 (103) returned to Inventor as filename to my part
------------------- A B C
1 101 Title1
2 102 Title2
3 103
If possible, i would like to be able to adjust this alittle as i go forward in my work, so if the code could be made easy to
understand, then I would be very happy..
If you only know some of the steps, i would be happy to hear about them as well... for every step made automatic i
save time..!!
/LSA-Skan
Solved! by LSA-skan. See the answer in context.
0 Kudos
Just to finish of this thread, i bring the solution im using now..
It works realy good, and isn't a "beast" at all... actually stating up the VBA first time in inventor take longer time
than it takes to open, edit and save the Excel sheet..
(i have set a macro for every rule i use, for faster access)
Hope someone other than me can benefit from this..!
DimNavnAsString
DimTitleAsString
DimRaavareAsString
DimDescrAsString
DimNummerAsInteger
DimNewPNAsString
Nummer=MessageBox.Show("Vilduudtageetnummertildenbneparti80000nr.
listen?","SkanrollStandard",MessageBoxButtons.YesNo)
IfNummer=6Then
IfiProperties.Value("Summary","Title")=""Then
Title=InputBox("Indtastentitel","Titel","")
iProperties.Value("Summary","Title")=Title
Else
Title=InputBox("Erdutilfredsmeddennetitel,ellersretden","Titel",
iProperties.Value("Summary","Title"))
iProperties.Value("Summary","Title")=Title
EndIf
IfiProperties.Value("Custom","Raavare")=""Then
Raavare=InputBox("IndtastenRaavare"&vbCr&"Raavarenerdenprimre
beskrivelseafkomponenten","Raavare","")
iProperties.Value("Custom","Raavare")=Raavare
Else
Raavare=InputBox("Erdutilfredsmeddennetekst,ellersretden","Raavare",
iProperties.Value("Custom","Raavare"))
iProperties.Value("Custom","Raavare")=Raavare
EndIf
IfiProperties.Value("Project","Description")=""Then
Descr=InputBox("Indtastenbeskrivelse"&vbCr&"Beskrivelsenerdensekundre
beskrivelseafkomponenten","Beskrivelse","")
iProperties.Value("Project","Description")=Descr
Else
http://forums.autodesk.com/t5/inventorcustomization/getnewpartnumberfromexcelwithilogic/tdp/3763000
Post to the Communi
Have questions about Au
products? Ask the comm
The Knowledge Netw
Access a broad range of
knowledge to help get th
out of your products and
Excel Data Links function
reference
Write Information to an E
Spreadsheet
Update iProperties
Rule Text Reference
Recommendations
Create a new work p
iLogic
Interface not registe
Broken connection w
iLogic rule for assem
generate a drawing
iLogic Excel date pro
iLogic rule
Download & Installat
Getting your Software
Downloads & Upgrades
Serial Numbers & Produ
Installation & Licensing
Activation & Registration
Network License Admin
Subscription Manage
Sign In / Create Account
Maintenance Subscriptio
Desktop Subscription He
Cloud Service Subscripti
1/5
1/27/2015
GetnewpartnumberfromExcelwithiLogicAutodeskCommunity
Descr=InputBox("ErdutilfredsmeddenneTekst,ellersretden","Titel",
iProperties.Value("Project","Description"))
iProperties.Value("Project","Description")=Descr
EndIf
Navn=iProperties.Value("Summary","Title")&""&iProperties.Value("Custom",
"Raavare")&""&iProperties.Value("Project","Description")
GoExcel.Open("I:\TEGNINGSNUMRESKEMAER\Standardnummerudtagning.xlsx","80000")
NewPN=GoExcel.CellValue("L1")
RowN=2
Do
RowN=RowN+1
Tomcelle=GoExcel.CellValue("C"&RowN)
LoopUntilTomcelle=""
GoExcel.CellValue("C"&RowN)=Navn
GoExcel.CellValue("D"&RowN)=""
GoExcel.CellValue("E"&RowN)=""
Pris=InputBox("IndtastPris","Pris","100")
GoExcel.CellValue("F"&RowN)=Pris
GoExcel.CellValue("G"&RowN)="0"
GoExcel.CellValue("H"&RowN)="1"
GoExcel.CellValue("I"&RowN)=iProperties.Value("Summary","Author")
GoExcel.Save()
MessageBox.Show("DinPartblivernugemtsom"&NewPN,"Ditnr.")
ThisDoc.Document.SaveAs("M:\tegn\Standardbibliotek\80000Skanrollstandard\"&NewPN
&".ipt",False)
EndIf
IfNummer=7Then
GoExcel.Open("I:\TEGNINGSNUMRESKEMAER\Standardnummerudtagning.xlsx","80000")
NewPN=GoExcel.CellValue("L1")
MessageBox.Show("Serderjoingengrundtilattrykkepknappen,vel..?!"&vbCr&
"Dukunneellershavefetnr.:"&NewPN,"Tumpe")
EndIf
Post 1 of 23 | Share
Report Inappropriate
Content
LSA-skan
Valued Contributor
95 Posts
0 Kudos
Registered:
08-09-2012
0 Kudos
REPLY
Options
Re: Get new part number from Excel with iLogic
02-04-2013 02:05 AM in reply to: LSA-skan
Is this not possible at all since i have no replies on this yet...?
Im sure there must be some of you Blilliant minds out there who know a way to do this..
/LSA-Skan
Post 2 of 23 | Share
Report Inappropriate
Content
Gruff
Valued Mentor
446 Posts
17 Kudos
Registered:
11-24-2003
0 Kudos
Re: Get new part number from Excel with iLogic
REPLY
Options
02-18-2013 01:54 PM in reply to: LSA-skan
Personally I avoid using Excel for anything tied to Inventor.
It is a huge beast. So is Inventor. You can use up resources in an eye blink.
I also think that iLogic is not the best place to create this sort of tool.
I have an auto part / filename numbering scheme that is based around a simple text file.
Each Engineer has their own so they are not trying to open the same file.
The file format is:
Line 1: 000_000_002.
Line 2: Do not atler these top two lines.
Line 3 User Intiials 000_000_000 DateTimeStamp Document Description
Line 4 User Intiials 000_000_001 DateTimeStampDocument Description
in process...
In VBA...
The user enters a file name description.
Presses a Get New FileName button.
The file is opened and the first line is read. The file is closed.
This number is appended to the bottom of the file along with the users initials and description by opening the file in
append mode. The text is written to the file. The file is closed. (You use this information to create your inventor
document name.)
The next number to be used is generated from the original number (Say 000_000_003)
The file is opened as binary and the first 11 bytes are overwritten with the next number to be used.
---
So at the end of pulling a filename the text file would look like:
Line 1: 000_000_003.
Line 2: Do not atler these top two lines.
http://forums.autodesk.com/t5/inventorcustomization/getnewpartnumberfromexcelwithilogic/tdp/3763000
2/5
1/27/2015
GetnewpartnumberfromExcelwithiLogicAutodeskCommunity
Post 3 of 23 | Share
Report Inappropriate
Content
cean_au
Valued Contributor
Line 3 User Intiials 000_000_000 DateTimeStamp Document Description
Line 4 User Intiials 000_000_001DateTimeStampDocument Description
Line 5 User Intiials 000_000_002 DateTimeStampDocument Description
This process is extremely lightweight and takes place in an eyeblink.
All this is relatively easy as we are working with a simple text file.
0 Kudos
REPLY
Options
Re: Get new part number from Excel with iLogic
02-18-2013 02:13 PM in reply to: Gruff
You put comma in between and name it cvs, you got an excel txt file.
100 Posts
2 Kudos
Registered:
07-11-2011
Post 4 of 23 | Share
Report Inappropriate
Content
Gruff
Valued Mentor
0 Kudos
REPLY
Options
Re: Get new part number from Excel with iLogic
02-18-2013 02:30 PM in reply to: cean_au
Right, but my point is you do not need Excel to utilize a text based file.
The system I outlined is self supporting.
No need to manually edit the file once it is in play.
446 Posts
17 Kudos
Registered:
11-24-2003
Post 5 of 23 | Share
Report Inappropriate
Content
LSA-skan
Valued Contributor
95 Posts
0 Kudos
Registered:
08-09-2012
Post 6 of 23 | Share
Report Inappropriate
Content
Gruff
0 Kudos
REPLY
Options
Re: Get new part number from Excel with iLogic
02-18-2013 10:39 PM in reply to: Gruff
Hi
Thanks for your replys..
The thing with the excel file is, that we use this to gather information about the different partnumbers... therefore
there will be 5-6 people looking at the same file, and using it for taking new numbers, which now is a manual process.
and takes dicipline, and time. I simply seek to automate this process to eliminate errors..
it would not matter to me if the process is "heavy" or in any way takes up resources, as long as what is done is correct
and happends automatically..!
So if I understand you correct this VBA code you have should (with some editing) be able to do the trick..?
Maybe i can set my iLogic rule to run the VBA at the end..? (the current configurator is at 1500 lines, and including a
form i hope i don't have to redo.
)
0 Kudos
Re: Get new part number from Excel with iLogic
REPLY
Options
Valued Mentor
02-19-2013 08:58 AM in reply to: LSA-skan
446 Posts
17 Kudos
Registered:
11-24-2003
Where you are going to have issues is trying to use a centralized file (of any kind).
What you want is going to require read/write access for every user.
This does not happen automatically which is why I give each of my engineers their own part numbering file.
No chance what so ever of collisions.
-----Excel has a share mode, but basically each user opens a copy of the single spreadsheet and when they close their copy
Excel trys to merge each back into the original. If users have changed the same cell then the last out overwrites cell
with their data.
This could mean that two or more users could have the same part number but the spreadsheet would show only the
last user. (Another reason I do not recommend an Excel file)
http://forums.autodesk.com/t5/inventorcustomization/getnewpartnumberfromexcelwithilogic/tdp/3763000
3/5
1/27/2015
GetnewpartnumberfromExcelwithiLogicAutodeskCommunity
Post 7 of 23 | Share
Report Inappropriate
Content
cean_au
Valued Contributor
100 Posts
2 Kudos
Registered:
07-11-2011
Read/Write collisions on a single file are nothing new. Generally the only decent solution is a database which is made
to handle multiple users gracefully.
Key to any sort of multiple file access is to get in and out of the file as quickly as possible. This reduces the chance of a
collision.
-----
Just my two cents worth of advice.
0 Kudos
REPLY
Options
Re: Get new part number from Excel with iLogic
02-19-2013 12:42 PM in reply to: Gruff
I once worked in a place when you open a autocad drawing, if somebody already opened it, you will get a notice
saying you can't save. So I think there may be some method on the server side to set a file could only be saved by the
first person who opened it.
Post 8 of 23 | Share
Report Inappropriate
Content
Gruff
Valued Mentor
446 Posts
17 Kudos
Registered:
11-24-2003
0 Kudos
REPLY
Options
Re: Get new part number from Excel with iLogic
02-19-2013 01:04 PM in reply to: LSA-skan
Actually it is a fundemental part of the Windows client operating system and Windows Network Server soltware.
Normally any file in use by a program is not allowed to be opened by another.
One method is to try to open the file. If it returns an error saying it is not available loop and try again
until you get through or until a certain amount of time has elapsed. If the latter then end trying with an error message.
Done property you will not get collisions but occasionally you will get a bit of a lag as your program waits its turn.
Post 9 of 23 | Share
Report Inappropriate
Content
Gruff
Valued Mentor
446 Posts
17 Kudos
Registered:
11-24-2003
Post 10 of 23 | Share
0 Kudos
Options
Re: Get new part number from Excel with iLogic
02-19-2013 03:08 PM in reply to: Gruff
Attached is a sample zipped VBA form file. Import it into your current VBA project.
Set the three constants at the top of the code page to meet your requirements.
One can use a different scheme for the part no of course.
This is an attempt to use a centralized single Partno text file.
It uses a secondary file called gatekeeper to allow the current user
the freedom to manipulate the masterpn file without fear of interruption.
Give it a go and see if it will work for you.
Attachments:
frmGetPnSample.zip 3
KB
Report Inappropriate
Content
0 Kudos
Previous
Share
Share
Share Share
More
Did you find this discussion helpful?
FOLLOW AUTODESK
REPLY
Message Listing
Yes
REPLY
Previous Topic
Next
Next Topic
No
PRODUCTS
HELP & SUPPORT
BUY
ABOUT AUTODESK
Facebook
3D CAD software
Customer Service
Online store
Careers
Twitter
Construction software
Documentation & knowledge base
Find a reseller
Contact us
http://forums.autodesk.com/t5/inventorcustomization/getnewpartnumberfromexcelwithilogic/tdp/3763000
4/5
1/27/2015
GetnewpartnumberfromExcelwithiLogicAutodeskCommunity
YouTube
Drafting software
Installation
Subscription
Philanthropy
LinkedIn
Painting software
Product upgrades
Support offerings
Gallery
Student downloads
Service packs & updates
Education licensing
Investor relations
Design engineering
Training resources
Newsroom
Civil engineering
Blogs
Trust Center
PLM
Discussion groups
Autodesk Labs
All social
Character animation
Autodesk Research
Movie editing
Autodesk University
Visual effects
Privacy | Legal Notices & Trademarks | Report Noncompliance | Site map | Copyright 2014 Autodesk Inc. All rights reserved
http://forums.autodesk.com/t5/inventorcustomization/getnewpartnumberfromexcelwithilogic/tdp/3763000
Except where otherwise noted, this work is licensed under a Creative Commons Attribution-No
3.0 Unported License. Please see the Autodesk Creative Commons FAQ for more information.
5/5
