Inventor General Discussion

Inventor General Discussion

Reply
Valued Contributor
jamjumpin
Posts: 132
Registered: ‎12-19-2012
Message 1 of 12 (828 Views)
Accepted Solution

iLogic Excel date problem

828 Views, 11 Replies
01-07-2013 07:15 AM

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

 

 

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

 

*Expert Elite*
Curtis_Waguespack
Posts: 3,000
Registered: ‎03-08-2006
Message 2 of 12 (821 Views)

Re: iLogic Excel date problem

01-07-2013 07:36 AM in reply to: jamjumpin

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



  solution.png  Did you find this reply helpful ? If so please use the Accept as Solution or  Kudos button below.





Valued Contributor
jamjumpin
Posts: 132
Registered: ‎12-19-2012
Message 3 of 12 (820 Views)

Re: iLogic Excel date problem

01-07-2013 07:43 AM in reply to: Curtis_Waguespack

Sorry I just updatead it. 

 

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

Valued Contributor
jamjumpin
Posts: 132
Registered: ‎12-19-2012
Message 4 of 12 (817 Views)

Re: iLogic Excel date problem

01-07-2013 07:47 AM 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?

*Expert Elite*
Curtis_Waguespack
Posts: 3,000
Registered: ‎03-08-2006
Message 5 of 12 (806 Views)

Re: iLogic Excel date problem

01-07-2013 09:54 AM in reply to: jamjumpin

 

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

 



  solution.png  Did you find this reply helpful ? If so please use the Accept as Solution or  Kudos button below.





Valued Contributor
jamjumpin
Posts: 132
Registered: ‎12-19-2012
Message 6 of 12 (794 Views)

Re: iLogic Excel date problem

01-07-2013 11:47 AM in reply to: Curtis_Waguespack

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

Valued Contributor
jamjumpin
Posts: 132
Registered: ‎12-19-2012
Message 7 of 12 (793 Views)

Re: iLogic Excel date problem

01-07-2013 11:50 AM 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. 

New Member
SHoffmeyer
Posts: 2
Registered: ‎01-07-2013
Message 8 of 12 (785 Views)

Re: iLogic Excel date problem

01-07-2013 12:28 PM 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 2013 Certified Professional
Inventor Pro 2013 - SP1.1

Dell Precision M6500
i7 CPU Q 820 @1.73GHz
NVIDIA Quadro FX 2800M
Valued Contributor
jamjumpin
Posts: 132
Registered: ‎12-19-2012
Message 9 of 12 (779 Views)

Re: iLogic Excel date problem

01-07-2013 12:34 PM in reply to: SHoffmeyer

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

Valued Contributor
jamjumpin
Posts: 132
Registered: ‎12-19-2012
Message 10 of 12 (774 Views)

Re: iLogic Excel date problem

01-07-2013 12:38 PM in reply to: Curtis_Waguespack

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!

 

 

Post to the Community

Have questions about Autodesk products? Ask the community.

New Post
Announcements
Do you have 60 seconds to spare? The Autodesk Community Team is revamping our site ranking system and we want your feedback! Please click here to launch the 5 question survey. As always your input is greatly appreciated.