Autodesk Inventor

[#1]

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

[#2]

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

[#3]

Sorry I just updatead it. 

This code doesn't work anyway, it says "Invalid cast from 'DateTime' to 'Double'."

[#4]

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?

[#5 · Accepted solution]

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

[#6]

Thank you very much, I can probab;y use this to trigger the other functions I need it to do. 

[#7]

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. 

[#8]

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.

[#9]

The date is being discarded, it's purely for refernce to grab other info from the Excel sheet.

[#10]

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!