Community
Inventor Forum
Welcome to Autodesk’s Inventor Forums. Share your knowledge, ask questions, and explore popular Inventor topics.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

iLogic Excel date problem

11 REPLIES 11
SOLVED
Reply
Message 1 of 12
jamjumpin
1936 Views, 11 Replies

iLogic Excel date problem

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

 

Tags (3)
11 REPLIES 11
Message 2 of 12

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

Message 3 of 12

Sorry I just updatead it. 

 

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

Message 4 of 12
jamjumpin
in reply to: jamjumpin

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?

Message 5 of 12

 

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

 

Message 6 of 12

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

Message 7 of 12
jamjumpin
in reply to: jamjumpin

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. 

Message 8 of 12
SHoffmeyer
in reply to: jamjumpin

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.

-------------------------------------------------------
Scott Hoffmeyer
Autodesk Inventor 2016 Certified Professional
Inventor 2017.4

Dell Precision 7520
i7-7920HQ CPU @3.10 GHz
NVIDIA Quadro P5000
Message 9 of 12
jamjumpin
in reply to: SHoffmeyer

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

Message 10 of 12

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!

 

 

Message 11 of 12

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

Message 12 of 12

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.

Post to forums  

Autodesk Design & Make Report