how would I do this in VBA ?

[Darkforce_the_ilogic_guy]

I want to find a row in  an excel sheet , and  read other vaule in the same row . I know how to do it in ilogic , it does know seens to be so easy to rewrite the code to vba.  can you help me ?

dim ExcelPath

dim ExcelSheet

ExcelPath="C:\showprice\Showprice.xlsm"

ExcelSheet ="Price"

GoExcel.Open(ExcelPath, ExcelSheet)
GoExcel.TitleRow = 1
GoExcel.FindRowStart = 2
'NO_ collum = column A

'Raw" collum = column B

'Part Description collum = column C
Dim rowNo = GoExcel.FindRow(ExcelPath, ExcelSheet, "No_", "=", CurrentSM_Style)
If rowNo > 0 Then
RawMatrNo=GoExcel.CurrentRowValue("Raw") '
PartDescription=GoExcel.CurrentRowValue("Part Description")
End If

GoExcel.Close

[Stakin · Accepted solution]

excelapp= CreateObject("Excel.Application")
excelapp.Visible = False
excelapp.DisplayAlerts = False
excelapp.Workbooks.Open(ExcelPath)
mainsheet=excelapp.ActiveWorkbook.WorkSheets(ExcelSheet)
mainData = mainsheet.Range("A3").CurrentRegion.Value
excelapp.Quit

[Darkforce_the_ilogic_guy]

how do I get the infomation out ? does your code not just open the excel doc ?

[Ralf_Krieg · Accepted solution]

Hello

I assume position of columns NO_, Raw and PartDescription are unknown, so we have to find it first. Try this:

Private Sub FindValue(ByVal CurrentSM_Style As String)

    Dim oExcelApp As New Excel.Application
        
    Dim ExcelPath As String
    ExcelPath = "C:\showprice\Showprice.xlsm"
    
    Dim ExcelSheet As String
    ExcelSheet = "Price"

    Dim oWB As Excel.Workbook
    Set oWB = oExcelApp.Workbooks.Open(ExcelPath)
    
    Dim oWS As Excel.WorkSheet
    For Each oWS In oWB.Sheets
        If oWS.Name = ExcelSheet Then Exit For
    Next
    
    Dim RawMatrNo As String
    RawMatrNo = getcellvalue(oWS, "Raw", CurrentSM_Style)
    
    Dim PartDescription As String
    PartDescription = getcellvalue(oWS, "PartDescription", CurrentSM_Style)

    'for debugging
    MsgBox ("CurrentSM_Style= " & CurrentSM_Style & vbCrLf _
            & "RawMatrNo= " & RawMatrNo & vbCrLf _
            & "PartDescription= " & PartDescription)
            
    oWB.Close (False)
    oExcelApp.Quit
    
End Sub

Function getcellvalue(oWS As WorkSheet, ct As String, rt As String) As String
    
    Dim colNo As Long
    colNo = Application.WorksheetFunction.Match("NO_", oWS.Rows(1), 0)
    
    Dim rowNo As Long
    rowNo = Application.WorksheetFunction.Match(rt, oWS.Columns(colNo), 0)
    
    colNo = Application.WorksheetFunction.Match(ct, oWS.Rows(1), 0)
    
    getcellvalue = oWS.Cells(rowNo, colNo)
    
End Function

[Darkforce_the_ilogic_guy]

I keep getting all sort of error...I did remove some of them ... but it seens I stock with this one .

And anyone see why ?

Dim oExcelApp

Dim ExcelPath As String
ExcelPath = "C:\Working Folder\CAD\Kallesoe\Kallesoe iLogic\ShowPrices.xlsm"

Dim ExcelSheet As String
ExcelSheet = "Price"

Dim oWB

Set oWB = oExcelApp.Workbooks.Open(ExcelPath)

Dim oWS
For Each oWS In oWB.Sheets
If oWS.Name = ExcelSheet Then Exit For
Next

Dim RawMatrNo As String
RawMatrNo = getcellvalue(oWS, "Unit Cost", "3095006030")
'
' Dim PartDescription As String
' PartDescription = getcellvalue(oWS, "PartDescription", CurrentSM_Style)

'for debugging
MsgBox ("CurrentSM_Style= " & CurrentSM_Style & vbCrLf _
& "RawMatrNo= " & RawMatrNo)

oWB.Close (False)
oExcelApp.Quit

'END CODE

If Msg = "Flat Bars" And Msg2 = "Steel" And msg3 = "Flat Bar, Hol Rol.-StS235" And msg4 = "30 x 6" Then

ShowPrice.KostPris.Caption = "100 kr"
End If
End Sub

Function getcellvalue(oWS, ct As String, rt As String) As String

Dim colNo As Long
colNo = Application.WorksheetFunction.Match("NO_", oWS.Rows(1), 0)

Dim rowNo As Long
rowNo = Application.WorksheetFunction.Match(rt, oWS.Columns(colNo), 0)

colNo = Application.WorksheetFunction.Match(ct, oWS.Rows(1), 0)

getcellvalue = oWS.Cells(rowNo, colNo)

End Function

[bshbsh]

---

And anyone see why ?

Set oWB = oExcelApp.Workbooks.Open(ExcelPath)

---

oExcelApp is not instanced. you need to create the object before you can use it.

[Ralf_Krieg]

Hello

Don't know why you strip my example the way it doesn't work anymore. You need to create the instance as bshbsh said and i wrote in my example.

Dim oExcelApp As New Excel.Application

If this throws an error, check in VBA references if you have enabled the reference to the "Microsoft Excel XX.0 Object Library". Otherwise VBA don't "know" what an Excel.Application is.

[Darkforce_the_ilogic_guy]

I remove it because it cause an error on my computer like it was missing somthing. maybe needed to imports some extra code libraries(think it is call).

[Ralf_Krieg]

Hello

Have you tried to add the reference to the Excel Library as I wrote in my last post? I think that's what is missing. Without this you get error messages about undefined user defined types and so on. To add this reference open the VBA editor window. Under menu "Tools" goto "References". In the window scroll down to "Microsoft Excel XX.0 Object Library" (XX.0 is the internal version number, Excel 2019 has 16.0) and activate it. Close the dialog with OK and try again.

[WCrihfield · Accepted solution]

You need to turn on the reference to your Excel application's API Object library.

Within the VBA Editor:

Click Tools tab > References...  (this will open a References dialog)

Then scroll down and check the check box beside the "Microsoft Excel 16.0 Object Library", then click the 'OK' button.

(The 16.0 part may be different on your maching...it relates to the version of Excel you have installed.)

After this is turned on, all those Excel related objects should be recognized within your VBA macros.

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.

If you want and have time, I would appreciate your Vote(s) for My IDEAS 💡or you can Explore My CONTRIBUTIONS

[Darkforce_the_ilogic_guy]

thanks. I end of getting it kind of working. But still have a lot of fine turning to do 

you dont happen to how have I can you it to open just ones(excel)? right not it open up a new ones each time I change the last selection. 

[Ralf_Krieg]

Hello

I modified my code from above to get a running instance or, if there's none, create a new one.

Option Explicit

Private Sub FindValue(ByVal CurrentSM_Style As String)

    Dim oExcelApp As Excel.Application
    Set oExcelApp = GetExcelApplication

    ' create a dummy workbook and let it open, otherwise Excel will shutdown an macro end
    Dim oWB As Workbook
    If oExcelApp.Workbooks.Count = 0 Then
        Set oWB = oExcelApp.Workbooks.Add()
    End If
    
    'make Excel visible, otherwise you have to shutdown it by taskmanager :-/
    oExcelApp.Visible = True
        
    Dim ExcelPath As String
    ExcelPath = "C:\showprice\Showprice.xlsm"
    
    Dim ExcelSheet As String
    ExcelSheet = "Price"

    Dim oWB As Excel.Workbook
    Set oWB = oExcelApp.Workbooks.Open(ExcelPath)
    
    Dim oWS As Excel.WorkSheet
    For Each oWS In oWB.Sheets
        If oWS.Name = ExcelSheet Then Exit For
    Next
    
    Dim RawMatrNo As String
    RawMatrNo = getcellvalue(oWS, "Raw", CurrentSM_Style)
    
    Dim PartDescription As String
    PartDescription = getcellvalue(oWS, "PartDescription", CurrentSM_Style)

    'for debugging
    MsgBox ("CurrentSM_Style= " & CurrentSM_Style & vbCrLf _
            & "RawMatrNo= " & RawMatrNo & vbCrLf _
            & "PartDescription= " & PartDescription)
            
    oWB.Close (False)
    oExcelApp.Quit
    
End Sub

Function getcellvalue(oWS As WorkSheet, ct As String, rt As String) As String
    
    Dim colNo As Long
    colNo = Application.WorksheetFunction.Match("NO_", oWS.Rows(1), 0)
    
    Dim rowNo As Long
    rowNo = Application.WorksheetFunction.Match(rt, oWS.Columns(colNo), 0)
    
    colNo = Application.WorksheetFunction.Match(ct, oWS.Rows(1), 0)
    
    getcellvalue = oWS.Cells(rowNo, colNo)
    
End Function

Public Function GetExcelApplication() As Object
    On Error GoTo openExcel
    
    Set GetExcelApplication = GetObject(, "Excel.Application")
    Exit Function
    
openExcel:
    If Err.Number = 429 Then
        Set GetExcelApplication = CreateObject("Excel.Application")
    Else
        Debug.Print "Unhandled exception: " & Err.Number & " " & Err.Description
    End If
End Function

[bshbsh]

---

you dont happen to how have I can you it to open just ones(excel)? right not it open up a new ones each time I change the last selection. 

---

instead of using the New keyword to create the excel instance (which will open a new Excell every time) you can use the createobject() and getobject() functions.

Dim oExcelApp as Object
On Error Resume Next
Set oExcelApp = GetObject(, "Excel.Application")
If Err.Number <> 0 then
    Set oExcelApp = CreateObject("Excel.Application")
    Err.Clear
end if
On Error Goto 0

[WCrihfield]

Here's another example of a similar routine that both @Ralf_Krieg  & @bshbsh provided examples for that attempts to either Get or Create an instance of Excel, while attempting to avoid the potential errors.  I don't use VBA nearly as much as I do iLogic & vb.net, so I had to dig for this one.  Similarly too, you can either use it by itself, or use it as a separate Function (with a couple minor tweaks).

I believe that back then I may have gotten some of the inspiration for it from the standard Microsoft page for the VBA use of GetObject() method.  Anyways, since I had just posted something so related to this situation on that other post, I would at least drop a link to that other post here too, just in case it may help anyone else out.

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.

If you want and have time, I would appreciate your Vote(s) for My IDEAS 💡or you can Explore My CONTRIBUTIONS