Ilogic - Excel worksheet required specific name pointed in GoExcel.FindRow

[Anonymous]

Hi folks, hope you all are going well.

My issue is related to GoExcel. FindRow. This snippet requires a name for "worksheet" but when I need to change the design I have to select a new database(xls) that has a different name in the excel worksheet (and different name of the xls file but this is not a problem, see the code bellow).

Is there a way to keep the code looking for the current activated worksheet from any file selected ?

Below, follow a code wrote by now, and obviously, I'm not an expert in this matter but as much as you, trying to learn more and more.

Dim oFileDlg As inventor.FileDialog = Nothing
InventorVb.Application.CreateFileDialog(oFileDlg)
oFileDlg.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx"
oFileDlg.DialogTitle = "Selecione o arquivo .xls extraido"
oFileDlg.InitialDirectory = ThisDoc.Path
oFileDlg.CancelError = True
On Error Resume Next
oFileDlg.ShowOpen()
If Err.Number <> 0 Then
MessageBox.Show("Arquivo nao selecionado.", "Operacao Cancelada")
ElseIf oFileDlg.FileName <> "" Then
NewFile = oFileDlg.FileName
MessageBox.Show("O arquivo " & NewFile & " foi selecionado.", "Arquivo Selecionado")
GoExcel.Open(NewFile,excelSheet)
'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(NewFile) <> "" Then
'workbook exists, open it
excelWorkbook = excelApp.Workbooks.Open(NewFile)
'set the first sheet active
excelSheet = excelWorkbook.Worksheets(1).activate

i=GoExcel.FindRow(NewFile,"excelSheet", "ELEMENT", "=", "CF3")
CF3=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,"excelSheet", "ELEMENT", "=", "CF6")
CF6=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheet , "ELEMENT", "=", "CF22")
CF22=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheet, "ELEMENT", "=", "CF8")
CF8=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile, excelSheet, "ELEMENT", "=", "CF23")
CF23=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile, excelSheet, "ELEMENT", "=", "CF28")
CF28=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile, excelSheet, "ELEMENT", "=", "CF29")
CF29=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheet, "ELEMENT", "=", "CF30")
CF30=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheet, "ELEMENT", "=", "CF13")
CF13=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheet, "ELEMENT", "=", "CF11")
CF11=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheet, "ELEMENT", "=", "CF24")
CF24=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheet, "ELEMENT", "=", "CF21")
CF21=GoExcel.CurrentRowValue("VALUE")

iLogicVb.UpdateWhenDone = True

iProperties.Value("Summary", "Subject")= NewFile
iProperties.Value("Custom", "External diameter [mm]")= CF3
iProperties.Value("Custom", "Internal diameter[mm]")= CF21
iProperties.Value("Custom", "Lenght [mm]")= CF24

InventorVb.DocumentUpdate()
End If
End If

[DRoam · Accepted solution]

Try and replace this line:

excelSheet = excelWorkbook.Worksheets(1).activate

...with this:

excelSheet = excelWorkbook.ActiveSheet

Reference: Workbook.ActiveSheet property (Excel)

[DRoam · Accepted solution]

After getting the active sheet, you'll also need to add this line:

excelSheetName = excelSheet.Name

and replace "excelSheet" in each FindRow with just excelSheetName, like this:

i=GoExcel.FindRow(NewFile,excelSheetName, "ELEMENT", "=", "CF3")

[Anonymous]

Hi Droam, have you been a good day? I hope so.

Dude, you make this happen in a isimple way. Thanks for your time on that issue. Now my code works fine. See the last Update, I've included a message box with Dimensions that was updated. Some texts are in portuguese just to warning local users.

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 40213 StartFragment: 314 EndFragment: 40181 StartSelection: 314 EndSelection: 314

Dim oFileDlg As inventor.FileDialog = Nothing
InventorVb.Application.CreateFileDialog(oFileDlg)
oFileDlg.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx"
oFileDlg.DialogTitle = "Selecione o arquivo .xls extraido"
oFileDlg.InitialDirectory = ThisDoc.Path
oFileDlg.CancelError = True
On Error Resume Next
oFileDlg.ShowOpen()
If Err.Number <> 0 Then
MessageBox.Show("Arquivo nao selecionado.", "Operacao Cancelada")
ElseIf oFileDlg.FileName <> "" Then
NewFile = oFileDlg.FileName
MessageBox.Show("O arquivo " & NewFile & " foi selecionado.", "Arquivo Selecionado")
GoExcel.Open(NewFile,excelSheet)
'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(NewFile) <> "" Then
'workbook exists, open it
excelWorkbook = excelApp.Workbooks.Open(NewFile)
'set the first sheet active
excelSheet = excelWorkbook.ActiveSheet
excelSheetName = excelSheet.Name
i=GoExcel.FindRow(NewFile,excelSheetName, "ELEMENT", "=", "CF3")
CF3=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheetName, "ELEMENT", "=", "CF6")
CF6=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheetName , "ELEMENT", "=", "CF22")
CF22=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheetName, "ELEMENT", "=", "CF8")
CF8=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile, excelSheetName, "ELEMENT", "=", "CF23")
CF23=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile, excelSheetName, "ELEMENT", "=", "CF28")
CF28=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile, excelSheetName, "ELEMENT", "=", "CF29")
CF29=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheetName, "ELEMENT", "=", "CF30")
CF30=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheetName, "ELEMENT", "=", "CF13")
CF13=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheetName, "ELEMENT", "=", "CF11")
CF11=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheetName, "ELEMENT", "=", "CF24")
CF24=GoExcel.CurrentRowValue("VALUE")
i=GoExcel.FindRow(NewFile,excelSheetName, "ELEMENT", "=", "CF21")
CF21=GoExcel.CurrentRowValue("VALUE")

iLogicVb.UpdateWhenDone = True

iProperties.Value("Summary", "Subject")= NewFile
iProperties.Value("Custom", "External diameter [mm]")= CF3
iProperties.Value("Custom", "Internal diameter [mm]")= CF21
iProperties.Value("Custom", "Length [mm]")= CF24
iProperties.Value("Custom", "Weight [kg]")= Round(iProperties.Mass,2)
NewFile.Close
InventorVb.DocumentUpdate()

MessageBox.Show("External Diameter was updated: " & CF3 & "mm" & vbNewLine & _
                "Internal Diameter was updated: " & CF21 & "mm" & vbNewLine & _
                "Length was updated: " & CF24 & "mm" & vbNewLine & _
                "Weight was updated: " & Round(iProperties.Mass,2) & "Kg", "iProperties Update")

End If
End If

[DRoam]

Great, glad it's working!