Problem with getting Excel Data (GoExcel.Open)

[falkmassmann]

Hello,

I´m having a couple of problems with getting data correctly from an Excel Sheet. I´m using the Excel Sheet to get Data for the Stock Number based on geometry.

First time the Excel File get´s opened it always cannot find the first sheet. I´m using a german Version and Sheet1 is Tabelle1 in german.

It always searches for sheet1 the first time the script runs on a new derived assembly. The second time (on second part) it correctly finds Tabelle1 and reads the correct value.

I´m out of ideas why this is happening.

Here is the code I´m using:

[code]

myXLS = "K:\IdentNr.xls"
GoExcel.Open(myXLS)
i = GoExcel.FindRow(myXLS, "Tabelle1", "Stärke", "=", Stärke, "Material", "=", iProperties.Material)
IdentNr = GoExcel.CurrentRowValue("IdentNr")
iProperties.Value("Custom", "IdentNr") = IdentNr

[\code]

And there is another issue which I have solved already but still gives me a bit of a headache. When the userdefined property IdentNr gets written into the part it always defaults to a numerical value, which means that I always get a comma at the end of the Stock Number.

Only when I define IdentNr like this:

Dim IdentNr As String

the format is correctly set to Text.

I expected that when you read a value from an Excel Cell that the Format of the Cell gives the format of the user defined property. Is that not correct?

Cheers

[Vladimir.Ananyev]

Question 1.  Sheet name in German.

GoExcel.Open(myXLS) failed if the first sheet was renamed.  It expects “Sheet1”. 😞

Luckily you may simply remove this line because the next command GoExcel.FindRow also can open given Excel file and activate given sheet.  This sheet name can be in German.

Question 2.

Format of user defined property is defined when you create this property in UI.  Text is default.

Cheers,

[Ktelang]

I have a question related to this quote:

"GoExcel.FindRow also can open given Excel file and activate given sheet."

I have an excel file which opens thru iLogic and I am trying to write to the same excel file by using GoExcel.FindRow

As I need the cell address where to write. I am experiencing a problem of reopening of same excel file always. how can I make this command use the already open sheet. 

Any ideas, BTW I am using Inventor 2013 and Service pack 2

on my computer

Thanks

[Ktelang]

I forgot to add the code. 

[Vladimir.Ananyev]

Could you test the following sample rule? (Path should be corrected).

'open excel file
Dim ExcelFilename As String = "C:\Temp\DATA.xlsx"
GoExcel.Open(ExcelFilename, "Sheet1")

'write Title values
GoExcel.CellValue("A1") = "LOCATION"	
GoExcel.CellValue("B1") = "X"
GoExcel.CellValue("C1") = "Y"
GoExcel.CellValue("D1") = "Z"

'write Some data in LOCATION column
GoExcel.CellValue("A" & 2) = 11
GoExcel.CellValue("A" & 3) = 22
GoExcel.CellValue("A" & 4) = 33
GoExcel.CellValue("A" & 5) = 44

GoExcel.Save

	'LookUp value	
	GoExcel.FindRowStart = 2
	GoExcel.Tolerance = 0.0000001

	'let's find LOCATION = 33
	MatchRow = GoExcel.FindRow(ExcelFilename, "Sheet1","LOCATION", "=", 33)
	
	If MatchRow = -1 Then
		MsgBox("Requested row not found")
	Else
		GoExcel.CellValue("B"& MatchRow)= "BBB"
		GoExcel.CellValue("C"& MatchRow)= "CCC"
		GoExcel.CellValue("D"& MatchRow)= "DDD"
	End If
	
	GoExcel.Save
	GoExcel.Close
Beep
MsgBox("Completed.")

Result: 

cheers,