Get new part number from Excel with iLogic

[LSA-skan]

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

[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

[Anonymous]

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 DateTimeStamp Document 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.

Line 3 User Intiials 000_000_000 DateTimeStamp Document Description

Line 4 User Intiials 000_000_001 DateTimeStamp Document Description

Line 5 User Intiials 000_000_002 DateTimeStamp Document 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.

[Anonymous]

You put comma in between and name it cvs, you got an excel txt file.

[Anonymous]

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.

[LSA-skan]

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. 🙂 )

[Anonymous]

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)

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.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.

[LSA-skan]

Hi

This looks interresting..! I will have a look at this in the coming week..!

Regarding the read/write access to the file, it´s not a problem, we all have read/write access to everything on the server. and as i mentioned it is also a necessity for all to be able to access this excel file to take the next partnumber.

The only problem i see is if the Code somehow fails/crashes, if 2 people use it at the same time... but first of all i would like to have something that works, then i will figure out a solution for that afterwards..

The gatekeeperfile you mention... how does that work exaclty? is it an extra file placed next to the real one or what?

/LSA-Skan

[LSA-skan]

after looking alittle into your code, i think i understand the princip of the Gatekeeper file...

you use it simply to see, if someone else is running the code, the file itself doesn't have to contain anything..?

for my case, i need to go directly to the masterfile, as some people are accessing this file without inventor. and therefore without a VBA code checking.

The Excel File is shared as it is now, which gives acces to multiple users at the same time. if a cell is used by two different users, the one saving last will be informed, that the cell has already been used, and then have the choice to accept others changes or ones own. But since the time for this VBA code in the document  is so short, the risk that someone else saves his/hers document in that time is realy minimal..!

as a VBA rookie, i might need some help converting your code to work with my excel file, and entering the correct cells. If i stille need the two top lines to be constant, like yours, then it´s no problem...!

/LSA-Skan

[LSA-skan]

Yet another update.. 🙂

now i have the next partnumber in my excel file in Cell "B1" this cell automatically shows the next number available. So what i need now is simply VBA to look into this sheet return the number from Cell "B1" then place inventors "title" in the Cell after the last filled cell. 

.......................................

80018    Title 

80019    Title 

              [Empty cell]

In Cell "B1", 80020 will now be shown, and i need "title" from inventor to be inserted in [Empty cell].

How do i tell VBA to insert title into this cell..?

[LSA-skan]

Status update.:

i actually came pretty close to this myself now, however i only need 1 thing now, to get the open document saved in the right path, and with the right filename... this doesn't seem to work..:

'Open Excelfile
GoExcel.Open("M:\tegn\Medarbejder Mapper\LSA\Nummer udtagning.xlsx", "80000")
'Get new partnumber from Excel
NewPN = GoExcel.CellValue("J1")
MsgBox(NewPN)
'Find next empty row in Column B
For RowPN = 2 To 1000
If GoExcel.CellValue("B" & RowPN) = 0 Then
'Insert title from open Document
GoExcel.CellValue("C" & RowPN) = iProperties.Value("Summary", "Title")
'Save File with the new partnumber NewPN
ThisDoc.Document.SaveAs(ThisDoc.WorkspacePath()&NewPN, True)
End If
Next

GoExcel.Save()

Can any of you guys push me over the last step..?

[Anonymous]

Yes the gatekeeper concept is to use a second file to lock out other users while you open and close the master file several times.  Without it you might get a collision between the master file open and close, open and close.

Regarding opening the master file as Random Access:  If this is an issue then the file structure could be changed to use three files total.

GateKeeper File

NextPartNo File

PartNoHistory File

Gatekeeper still performs it job.

NextPartNo contains only the single next PartNo to be used and nothing else.

PartNoHistory contains every part number ever used with accompaning data on each line.

Regarding Excel... No idea.

[LSA-skan]

im alittle currious regarding the way you describe this process...

you say each engineer has their own file..?! is it functioning only as a number generator then? and whats the point in a gatekeeper then if they all have their own..? i take it your engineers sometime are working on the same project, and therefore need the gatekeeper, but do they then switch the file around from time to time according to what they are working on? or how do you manage this practically..?

/LSA-Skan

[Anonymous]

Sorry for any confusion.  There were two topics I covered in my posts.

1) What I am doing at my company with each engineer using their own file.  (No gatekeeper required.)

2) What I proposed using a similar method that uses one centralized file. (Needs a gatekeeper)

I proposed #2 back in post 10 on page one of this thread.  This is the post where I attached a sample VBA form.

Everything I've said since then is in regard to #2.

[LSA-skan]

oh... im with you on that.. i was just currious of how you are working with this practically in your company.. 🙂

do your engineers never work on the same project then..? or are numbers handed out by the "Main" engineer on the project, if a second engineer gets "helping" assignments..? or do you maybe switch to a gatkeeper if you have 2 or more people on the same project..?

Regarding my post here, do you know how to "Save as" specific file name? if have have the Parameter NewPN that should make the part become "NewPN.ipt"

/LSA-Skan

[Anonymous]

Regarding what we are currently doing...

99  percent of the time they work alone or in series.  

Eng1 Starts a project and pulls part number for any new parts.  Customer drawing are created.

After customer approvalkl (Could be a couple of months)

Eng2 picks up the package makes any changes necessary.  pulls numbers for any additional new parts.

After QC approval

Eng2 releases the package to manufacturing.

Regarding SaveAs...

As long as I've been programming with inventor the "SaveAs" method has been flakey.

I avoid using it when ever possible as I never get the results i want.

[LSA-skan]

Sounds very similar to what we do here, besides it will almost always be Eng 1 that takes it all the way... 🙂

Good to know you experience with Save As... i might try and work around it then..! thanks alot for your aid in this.. !