sverweis per VB.Net in Excel Datei

  • VB.NET

Es gibt 14 Antworten in diesem Thema. Der letzte Beitrag () ist von matze.tele.

    sverweis per VB.Net in Excel Datei

    Hallo zusammen,
    ich mochte per VB.NET in einer Excel Datei einen Wert per sverweis raussuchen
    als Wert in VB.NET eingeben, in Excel suchen und dazugehörigen Wert ausgeben in VB.NET.
    Die Excel Datei liegt an einem Zentralen Ort und soll nicht jedes mal manuell geöffnet werden müssen.

    hat hier jemand eine Lösung, oder kann mich anleiten?
    Ich habe bisher keinen Code dazu.
    Gruß Matze 8-)

    Ich habe die Chance mal genutzt und getestet wie ChatGPT funktioniert. Habe genau deine Nachricht dort eingegeben.
    Das kam raus:

    Um in VB.NET einen Wert in einer Excel-Datei per SVERWEIS zu suchen und den dazugehörigen Wert auszugeben, können Sie die Microsoft.Office.Interop.Excel-Bibliothek verwenden. Sie müssen zunächst eine Verbindung zur Excel-Datei herstellen, dann können Sie die SVERWEIS-Funktion verwenden, um den Wert zu suchen und den dazugehörigen Wert auszulesen. Hier ist ein Beispielcode, der Ihnen eine Idee davon gibt, wie das gehen kann:

    VB.NET-Quellcode

    1. Imports Microsoft.Office.Interop
    3. Dim excelApp As New Excel.Application
    4. Dim wb As Excel.Workbook = excelApp.Workbooks.Open("Pfad zur Excel-Datei")
    5. Dim ws As Excel.Worksheet = wb.Sheets(1)
    7. Dim suchwert As String = "Suchwert"
    8. Dim ergebnis As String = ws.Cells(1, 1).Formula = "=SVERWEIS(suchwert,R1C1:R10C10,2,FALSCH)"
    10. Console.WriteLine(ergebnis)
    12. wb.Close()
    13. excelApp.Quit()



    Ist schon gruselig, was die KI kann. Vielleicht hilft es ja.


    Meine Website:
    www.renebischof.de

    Meine erste App (Android):
    PartyPalooza

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „xored“ ()

    Jetzt konnte ich mich nicht beherschen und habe es auch mal ausprobiert.
    Ich muss für meinen Chef morgens immer diverse gespeicherte Abfragen erstellen und habe dazu eine entsprechende Anfrage formuliert.
    Ich bekam einen Hinweis wie es gehen würde:
    Um in C# Dynamics 365 gespeicherte Abfragen auszuführen und die Ergebnisse als statische Excel-Tabelle zu speichern, können Sie die Dynamics 365 Web API verwenden, um Abfragen an den Dynamics 365-Server zu senden und die Ergebnisse abzurufen. Sie können dann die Ergebnisse in eine statische Excel-Tabelle speichern, indem Sie eine Bibliothek wie EPPlus verwenden, um das Excel-Dokument programmgesteuert zu erstellen und die Ergebnisse einzufügen.


    Damit war ich natürlich nicht zufrieden und tippte: Bitte ein C# Programmierbeispiel dazu
    Was soll ich sagem ich bekam meinen Code, ein schönes Grundgerüst jedenfalls, ich mach mich da nächste woche mal drann das umzusetzen, wäre geil wenn das hinhaut und mir täglich 20 Minuten Zeitersparniss bringen.

    Dksksm schrieb:

    Ich muss für meinen Chef morgens immer diverse gespeicherte Abfragen erstellen und habe dazu eine entsprechende Anfrage formuliert.

    Für mich schreit dies gewaltig nach PowerQuery/PowerBI.
    NB. Es ist doch schön, wenn man lesbare Namen vergibt. Siehe auch [VB.NET] Beispiele für guten und schlechten Code (Stil).

    Jupp, da hast du Recht:

    PowerQuery geht nicht, FetchXML bringt nicht alle Inhalte raus,wir haben da 2 Tage an einer Abfrage gebastelt (mehrere Leute und keine Dummen) und haben es am Ende nicht hinbekommen, bestimmte Datentypen und Felder haben sich hartnäkig gesträubt. Der Aufbau der Tabellen so wie sie dann auch ohne Programmieraufwand weiterverarbeitet werdne könnten muss dabei "null" sein. Es ist halt nicht gewollt und darf nichts kosten. Ich bin ja eh da (bis Anfang Juli, dann geh ich in Urlaub und direkt anschliessend in Rente). So habe ich eine einzige Tabelle die ich mit Powerquerry zusammengebogen bekommen habe, mehr leider nicht.

    An PowerBI ist man dran. Habe tatsächlich heute nachgefragt wie der Stand ist. Antwort: Die Verteilung in der Abteilung die nötig sind erfordert Liznezen die zur Zeit niemand zahlen will. Das Projekt wurde vorerst auf Eis gelegt.

    @matze.tele

    Du kannst
    - die WorksheetFunction.VLookup-Methode (Excel) verwenden
    Spoiler anzeigen

    VB.NET-Quellcode

    1. Sub MySVerweis1()
    3. Dim Xlapp As New Excel.Application
    4. Dim WB As Excel.Workbook
    5. Dim WS As Excel.Worksheet
    6. Dim destination As String = IO.Path.Combine(IO.Path.GetDirectoryName(Reflection.Assembly.GetExecutingAssembly().CodeBase), "MyExcelFile.xlsx")
    7. Xlapp.Visible = True
    9. Dim searchID As String = TextBox1.Text
    10. WB = Xlapp.Workbooks.Open(destination) ' destination1 is selected by 'user
    11. WS = CType(WB.Worksheets(1), Excel.Worksheet)
    13. TextBox2.Text = Xlapp.WorksheetFunction.VLookup(searchID, WS.Range("$A$1:F37"), 3, True).ToString
    15. WB.Close(False)
    16. Xlapp.Quit()
    17. End Sub

    - in Excel in eine Zelle den SVerweis eingeben, der als Suchtext eine zweite Zelle hat. Mit VB den Suchwert in die zweite Zelle schreiben und das Ergebnis des SVerweis lesen.
    Spoiler anzeigen

    VB.NET-Quellcode

    1. Sub MySVerweis2()
    3. Dim Xlapp As New Excel.Application
    4. Dim WB As Excel.Workbook
    5. Dim WS As Excel.Worksheet
    6. Dim destination As String = IO.Path.Combine(IO.Path.GetDirectoryName(Reflection.Assembly.GetExecutingAssembly().CodeBase), "MyExcelFile.xlsx")
    7. Xlapp.Visible = True
    9. Dim searchID As String = TextBox1.Text
    10. WB = Xlapp.Workbooks.Open(destination) ' destination1 is selected by 'user
    11. WS = CType(WB.Worksheets(1), Excel.Worksheet)
    13. WS.Range("G1").Value = searchID
    14. TextBox2.Text = WS.Range("H1").Value.ToString
    16. WB.Close(False)
    17. Xlapp.Quit()
    18. End Sub

    - eine eigene Funktion schreiben. z.B. mit der Range.Find-Methode (Excel) den Wert in der Suchspalte finden und mit Range.Offset-Eigenschaft (Excel) den Wert zurück geben.
    Spoiler anzeigen

    VB.NET-Quellcode

    1. Sub MySVerweis3()
    3. Dim Xlapp As New Excel.Application
    4. Dim WB As Excel.Workbook
    5. Dim WS As Excel.Worksheet
    6. Dim destination As String = IO.Path.Combine(IO.Path.GetDirectoryName(Reflection.Assembly.GetExecutingAssembly().CodeBase), "MyExcelFile.xlsx")
    7. Xlapp.Visible = True
    9. Dim FoundRange As Excel.Range
    10. Dim searchID As String = TextBox1.Text
    12. WB = Xlapp.Workbooks.Open(destination) ' destination1 is selected by 'user
    13. WS = CType(WB.Sheets(1), Excel.Worksheet)
    15. FoundRange = WS.Range("A:A").Find(What:=searchID, LookIn:=Excel.XlFindLookIn.xlFormulas, LookAt:=Excel.XlLookAt.xlWhole)
    17. If FoundRange Is Nothing Then
    18. TextBox2.Text = "not found"
    19. Else
    20. TextBox2.Text = CType(FoundRange.Offset(0, 2), Excel.Range).Value.ToString 'offset to result column
    21. End If
    23. WB.Close(False)
    24. Xlapp.Quit()
    25. End Sub

    versuche es mit OLEDB und erstelle dort deine SQL-Anweisung(en)

    VB.NET-Quellcode

    1. Option Strict On
    2. Imports System.Data.OleDb
    4. Public Class Form1
    6. Public Function ExcelOleDb(ByVal strTextPath As String, _
    7. ByVal sSQL As String) As System.Data.DataTable
    8. Dim con As New System.Data.OleDb.OleDbConnection
    9. Dim myCmd As New System.Data.OleDb.OleDbCommand
    10. Dim myadp As New System.Data.OleDb.OleDbDataAdapter
    11. Dim mydt As New System.Data.DataTable
    12. With con
    13. .ConnectionString = "provider=microsoft.ACE.OLEDB.12.0;"
    14. .ConnectionString &= "data source=" & strTextPath & ";"
    15. .ConnectionString &= "Extended Properties = ""Excel 12.0 XML"";"
    16. End With
    17. With myCmd
    18. .Connection = con
    19. .CommandType = CommandType.Text
    20. .CommandText = sSQL
    21. End With
    22. With myadp
    23. .SelectCommand = myCmd
    24. Try
    25. .Fill(mydt)
    26. Catch ex As Exception
    27. MessageBox.Show(ex.Message)
    28. End Try
    29. End With
    30. Return (mydt)
    31. End Function
    35. Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    36. 'Sample.1) select all:
    37. DataGridView1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT * FROM [Tabelle1$]")
    38. '###############################
    39. 'Sample.2) select Range: A2:C4
    40. 'DataGridView1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT * FROM [Tabelle1$A2:C4]")
    41. '################################
    44. ComboBox2.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT [Name] FROM [Tabelle1$B:B]")
    45. ComboBox2.DisplayMember = "Name"
    47. End Sub
    49. Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
    50. 'Sample.3) Filter Data mit Combobox
    51. DataGridView1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT * From [Tabelle1$] Where [Name] Like '" & ComboBox2.Text & "'")
    52. End Sub
    54. End Class


    viel Erfolg!

    Danke für eure Antworten,aber leider wäre der nötige Aufwand zu viel für den Nutzen, für den es gedacht war.
    die Excel wird täglich mehrfach aktualisiert und mir stehen leider nicht die nötigen Resorcen zur Verfügung,
    ich muss mir wohl einen anderen Weg suchen.
    Gruß Matze 8-)

    Bisher habe ich noch gar nichts wirklich Aufwändiges hier gesehen, wenn dich so ein paar Zeilen Code abschrecken, ja ich erspare mir den Gedanken auszusprechen.
    Fragen wie "kann mann xx auslesen ohne es zu öffnen" kommen auch häufiger. Ich verstehe nicht mal, wie man auf so eine Frage kommt. Wasch mich aber mach mich nicht nass?
    Wenn du es spezifizierst, kann man immer was tun, @Kasi hat dir einen Vorschlag gemacht, jetzt kommt einer der aufwändig wäre: Nutze die Struktur von XLSX, ist nichts anderes als eine gezippte XML-Struktur und hangel dich da durch.
    Dritte und IMO beste Variante: Nutze EPPLus und lese damit die Excel-Tabelle aus die du benötigst, auch hier wird Excel nicht als Anwendung benötigt, spart dir also Zeit und Arbeitsspeicher.

    @Dksksm
    die Excel umfasst etwa 500k zeilen und 18 Spalten bei ca 140MB.
    die Excel wird von einer webseite geladen auf der sie als report erstellt wird, ich habe auf keinerlei Art Einfluß auf den Report.
    ich habe die Erste Variante von HenryV für mich funktionstüchtig umgesetzt, leider dauert das Laden auf diese Art zu lange für den Prozess.
    es werden sicherlich mehrere Suchen durchgeführt, wenn die Excel beim ersten sverweis geöffnet bleibt, dann wäre das ganze schon um einiges schneller.
    aber ein hochladen in eine OLEDB oder einen Server ist mir nicht möglich, die resorcen habe ich nicht und es soll auf mehreren Arbeitsplätzen gleichzeitig laufen.
    ein Label soll gescannt werden und die dazu gehörige EAN ausgegeben werden.
    nicht dass ich mich scheue es zu versuchen, aber es sollte offline funktionieren.
    Gruß Matze 8-)

    Was hast Du denn genau vor? Ich glaube, dass Deine Idee mit dem SVerweis, nicht der perfomanteste ist.

    Du möchtest
    • eine Eingabe per Scan machen
    • dann in einer Liste, die hoffentlich eindeutig ist, den Eintrag suchen
    • und anschließend einen anderen Wert zurück erhalten.
    Was spricht dagegen den Wert mit Hilfe einer SQL-Abfrage aus der Excel-Datei zu lesen?
    NB. Es ist doch schön, wenn man lesbare Namen vergibt. Siehe auch [VB.NET] Beispiele für guten und schlechten Code (Stil).

    matze.tele schrieb:


    aber ein hochladen in eine OLEDB oder einen Server ist mir nicht möglich

    Mit OLEDB lädst du nichts hoch......
    es soll auf mehreren Arbeitsplätzen gleichzeitig laufen.

    Ja ist weder bei OLEDB noch bei EPPlus ein Problem. OLEDB verwende ich nicht, sondern ausschließlich EPPlus, Einarbeitung ist nur wenig nötig und man benötigt nicht mal Excel dafür am PC, obwohl man Excel lesen und schreiben kann, geiler geht gar nicht.
    ein Label soll gescannt werden und die dazu gehörige EAN ausgegeben werden.

    Davon habe ich keine Ahnung, da bin ich raus.
    aber es sollte offline funktionieren.

    Was auch immer du damit meinst. Vielleicht verstehen andere den Sinn dieses Halbsatzes, ich nicht.