I want iLogic to look up todays date in an excel file and then pull information from that row.
The problem I have is that it doesn't seem to like / or - so I am not sure how to format the date.
How would I format my date in Excel so that it matches the wway Inventor stores it in iproperties>Project>Creation Date?
Any help would be much appreciated.
Inventor 2013
DwgNum = iProperties.Value("Custom", "Dwg Number") Client = iProperties.Value("Project", "Vendor") Description = iProperties.Value("Project", "Description") DrawingType = iProperties.Value("Custom", "Drawing Type") author = iProperties.Value("Summary", "Author") Date2 = iProperties.Value("Project", "Creation Date") i = GoExcel.FindRow("C:\Users\jwilloughby\Desktop\Drawing_Log_2004.xls", "2013", "TEST", "=", Date2) DwgNum = GoExcel.CurrentRowValue("DWG #") Description = GoExcel.CurrentRowValue("DESCRIPTION") Client= GoExcel.CurrentRowValue("COMPANY") DrawingType= GoExcel.CurrentRowValue("DRAWING TYPE") author= GoExcel.CurrentRowValue("DRAWN BY") iProperties.Value("Custom", "Dwg Number") = DwgNum iProperties.Value("Project", "Vendor") = Client iProperties.Value("Project", "Description") = Description iProperties.Value("Custom", "Drawing Type") = DrawingType iProperties.Value("Summary", "Author") = author iLogicVb.UpdateWhenDone = True
Solved! Go to Solution.
Solved by Curtis_Waguespack. Go to Solution.
Hi jamjumpin,
I don't see a reference to a date in your example code. Could you post what you've tried so far?
I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com
Sorry I just updatead it.
This code doesn't work anyway, it says "Invalid cast from 'DateTime' to 'Double'."
Ultimately I want it to find all the references to todays date and display the information from the cells next to it so you can choose which is the correct one.
i.e.
Column 1 is date
COlumn 2 is description
It finds all the dates that match in column 1 and then shows a multivalue box with the corresponding info from Column 2.
When the user picks a value from the multivalue list that is the row it then reads all teh infon from.
Is this even possible in iLogic?
Hi jamjumpin,
Here's an example rule. Note if you get an error from Copy and Pasting from this forum, try the attached *.txt file.
I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com
'define the XLS to look at myXLS = "U:\iLogic examples\Excel Date lookup.xlsx" GoExcel.Open(myXLS, "Sheet1") 'create an array list to hold found values Dim myArrayList As New ArrayList 'look at the first 1000 rows of the Excel file For rowCheck = 2 To 1000 'read the value of the column A Dim myCell As Double myCell = GoExcel.CellValue("A" & rowCheck) 'format the found value as a date Dim myDate As DateTime myDate = Date.FromOADate(myCell) 'set the current date from the computer system date Dim curDate As String curDate = DateString 'compare the found value to the current date If myDate = curDate Then 'get the description sDescription = GoExcel.CellValue("B" & rowCheck) 'add the description to the list myArrayList.add(sDescription) End If Next myName= ThisApplication.GeneralOptions.UserName 'ensure that the current date was found (and added to the array list) If myArrayList.Count = 0 Then MessageBox.Show("The current date was not found in the Excel file." , "iLogic") Else 'present the list to the user myDesc = InputListBox("Yo, " & myName & " select one of these bad boys!", myArrayList, myArrayList.Item(0), "iLogic", "Selection List") 'present the result MessageBox.Show("You selected: " & myDesc, "iLogic") End If
Thank you very much, I can probab;y use this to trigger the other functions I need it to do.
It didn't work at first, and this may be help to anyone else having the problem.
My excel was formatted in the international style of date rather than the USA one.
If it helps, rather than grab it from Excel, you can just use DateString to insert today's date, sort of like:
iProperties.Value("Custom", "DATE")=DateString
This would show up in the format 01-07-2013. I know it's not the way you were trying to do it, but it might save you some headache in the future.
The date is being discarded, it's purely for refernce to grab other info from the Excel sheet.
Thank you, the code seems to work now I've added some extra stuff to the end. I have a few questions.
How do I get it to match the international form of the date instead of the US one? As in dd/mm/yyyy
Is there a way to just read the excel file instead of using the open command? iEverytime I run the command I get the following message, even when it isn't open:
Drawing_Log.xls is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen Drawing_Log.xls?
Is there a way to stop it asking the following message at the end of the rule too?
Do you want to save the changes you made to 'Drawing_Log.xls'?
The final thing is that sometimes I get the following error when I run the rule. If I don't change anything and run the rule again it works fine. Any ideas?
Error in rule: Rule3, in document: New Drawing iLogic.idw Object reference not set to an instance of an object.
Once again, thanks so much!
Hi jamjumpin,
Here are some quick answers to your questions, you might need to dig a bit for a more complete answer, but hopefully this will help.
Q: How do I get it to match the international form of the date instead of the US one?
A: see this link: http://forums.autodesk.com/t5/Autodesk-Inventor/iLogic-Date-format/m-p/2720101#M379538 3
You might also search here: http://forums.autodesk.com/t5/Autodesk-Inventor-Customization/bd-p/120
Q: s there a way to just read the excel file instead of using the open command? Everytime I run the command I get the following message, even when it isn't open.
A: I'm not aware of a way to do this without opening Excel in the background. But I suspect if you look at your task manager you might see that an instance of Excel running in the background. I see this often when I'm testing iLogic/Excel code and error out in the midst of it. If you kill the process for this phantom instance of Excel, you shouldn't see this issue.
Q: Is there a way to stop it asking the following message at the end of the rule too?
A:
GoExcel.DisplayAlerts = False
Q: The final thing is that sometimes I get the following error when I run the rule. If I don't change anything and run the rule again it works fine. Any ideas?
A: I don't really have a good guess on this, but it might occur if you've errored out on the previous run of the rule without the rule completing?
I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com
Thank you once again for your answers. I found the date post that you mentioned but I'm not sure where to put it.
I put it in here but it returns an error. I wish there were more tutorials on iLogic becasue I just feel so helpless.
Dim myArrayList As New ArrayList For rowCheck = 2 To 1000 Dim myCell As Double myCell = GoExcel.CellValue("A" & rowCheck) Dim myDate As DateTime.Now.ToString("d") myDate = Date.FromOADate(myCell) Dim curDate As String curDate = DateString If myDate = curDate Then sDescription = GoExcel.CellValue("B" & rowCheck) myArrayList.add(sDescription) End If Next
Can't find what you're looking for? Ask the community or share your knowledge.