Community
Hi andrewb,
Here is a quick iLogic rule that looks at each sheet in the excel file and searches for a value (myString).
I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com
'define the string to search for
Dim myString As String
myString = "Bingo"
'define the file to open
myXLS_File = "C:\Temp\ExcelWB-01.xlsx"
'define Excel Application object
excelApp = CreateObject("Excel.Application")
'set Excel to run visibly, change to false if you want to run it invisibly
excelApp.Visible = False
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False
'check for existing file
If Dir(myXLS_File) <> "" Then
'workbook exists, open it
excelWorkbook = excelApp.Workbooks.Open(myXLS_File)
oSheetCount = excelWorkbook.Sheets.Count
Else
'workbook does NOT exist
MessageBox.Show("The Excel workbook does NOT exist.", "iLogic")
End if
Dim sCellValue As String
i2 = 1
Do until i2 = oSheetCount +1
'set the excel sheet to search
GoExcel.Open(myXLS_File, "Sheet" & i2 )
'count row data
CurrentRow = 1 ' first row used
i = 0
Do Until i <> 0
'look for empty string
If String.isNullOrEmpty(GoExcel.CellValue("A" & CurrentRow)) Then
i = i + 1
Else
sCellValue = GoExcel.CellValue("A" & CurrentRow)
If sCellValue = myString Then
'tell user which sheet and cell has the data
MessageBox.Show("Value found on: Sheet" _
& i2 & ", A" & CurrentRow, "iLogic")
End If
CurrentRow = CurrentRow +1
End If
Loop
'last used row
NumberOfRows = CurrentRow -1
i2 = i2 +1
Loop
''close the workbook and the Excel Application
excelWorkbook.Close
excelApp.Quit
>>>
Hi andrewb,
Here is a quick update to this rule that uses the sheet's actual name, rather than building the name string from "sheet" & sheets.count.
I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com
'define the string to search for
Dim myString As String
myString = "Bingo"
'define the file to create/open
myXLS_File = "C:\Temp\ExcelWB-01.xlsx"
‘get the Inventor user name from the Inventor Options
myName= ThisApplication.GeneralOptions.UserName
'define Excel Application object
excelApp = CreateObject("Excel.Application")
'set Excel to run visibly, change to false if you want to run it invi2ibly
excelApp.Visible = False
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False
'check for existing file
If Dir(myXLS_File) <> "" Then
'workbook exists, open it
excelWorkbook = excelApp.Workbooks.Open(myXLS_File)
oSheetCount = excelWorkbook.Sheets.Count
Else
'workbook does NOT exist
MessageBox.Show("The Excel workbook does NOT exist.", "iLogic")
End if
Dim sCellValue As String
i2 = 1
Do until i2 = oSheetCount +1
'set the excel sheet to search
oSheetItem = excelWorkbook.Sheets.Item(i2)
oSheetName = oSheetItem.Name
GoExcel.Open(myXLS_File, oSheetName )
'count row data
CurrentRow = 1 ' first row used
i = 0
Do Until i <> 0
'look for empty string
If String.isNullOrEmpty(GoExcel.CellValue("A" & CurrentRow)) Then
i = i + 1
Else
sCellValue = GoExcel.CellValue("A" & CurrentRow)
If sCellValue = myString Then
'tell user which sheet and cell has the data
MessageBox.Show("Value found on: " & oSheetName _
& ", A" & CurrentRow, "iLogic")
End If
CurrentRow = CurrentRow +1
End If
Loop
'last used row
NumberOfRows = CurrentRow -1
i2 = i2 +1
Loop
''close the workbook and the Excel Application
excelWorkbook.Close
excelApp.Quit
Can't find what you're looking for? Ask the community or share your knowledge.
Post to forums
![Autodesk Design & Make Report](https://forums.autodesk.com/t5/image/serverpage/image-id/1350519iCB55845EBFC43016/image-size/large?v=v2&px=999" "Autodesk Design & Make Report")