Microsoft Excel

Herbers Excel/VBA-Archiv

Zeilennummer in externen Dateien

Betrifft: Zeilennummer in externen Dateien von: Patrick
Geschrieben am: 04.11.2020 11:02:29

Hallo liebes Forum,


ich lese hier schon seit einger Zeit fleissig mit und lerne von den vielen Beiträgen.

Meine 40 Jahren alten C64-Basic und Pascal (!) Kenntnisse reichen aus, um zu verstehen, was ihr da so macht und eure Zeiel so abzuändern, dass Excel das macht, was ich dann will.


Aber jetzt habe ich einen Hirndreher und drehe mich ständig im Kreis - ich komme nicht drauf, was ich falsch mache.


Mein Quellcode-Schnippsel (schreibe darunter, was ich vorhabe):



Do While strFile_Laender <> ""
            
 ' erstmal das Bundesland nachschlagen und in die Zielmappe/Zielzeile eintragen
 Cells(loZeileZielmappe, 1).Formula = _
           "='" & strPath_Laender & "[" & strFile_Laender & "]" & "3.2 Engpass_Tab2" & "'!T4"
               
  ' Suche in der Quellmappe die Zeilennummer des Suchwortes und schlage dann den Wert in der  _
Zeile nach   
  With Worksheets("" & "'" & strPath_Laender & "[" & strFile_Laender & "]" & "3.2 Engpass_Tab2"  _
 _
 _
& "'" & "")
    Set myTableArrayBerufsgruppen = .Range(.Cells(1, 2), .Cells(118, 2))
  End With

 ' Suche in dem Array mit MATCh die Zeilennummer raus und dann den Wert in Spalte S
  '
  ' Cells(loZeileZielmappe, 2).Formula = _
          "='" & strPath_Laender & "[" & strFile_Laender & "]" & "3.2 Engpass_Tab2" & "'!S12"
               
               
  loZeileZielmappe = loZeileZielmappe + 1
  strFile_Laender = Dir()
Loop


Erst mal, was ich grundsätzlich vorhabe:

Ich öffne die externen XLSX.Dateien in meinem lokalen Verzeichnis und schlage Werte in den dortigen Mappen nach und trage sie in die Zieltabelle ein.


Das funktioniert auch bereits super, so trägt mein Script die Bundesländer aus den externen Daten in die Spalte A schön untereinander ein.


Jetzt aber zu meinem Problem:

In diesen externen Dateien soll das Script in dem Worksheet "3.2 Engpass_Tab2" die Spalte B:B nach dem in Variable Berufsgruppe gespeicherten Suchwort durchsuchen und mir die Zeilennummer ausspucken.


Diese Zeilennummer brauche ich, damit ich in Spalte S dann den entsprechenden Wert zum Bundesland in meine Zielmappe übertragen will.


Meine Idee war, die Spalte B in ein Array zu überführen, um dann mit MATCH die Zeilennummer herauszubekommen.

Und beides will mir nicht gelingen:

Ich bekomme weder das Array richtig hin

noch die Zeilennummer mit dem Match-Befehl (Syntax-Fehler nach Syntax-Fehler)


Könnte mir jemand bitte helfen?


Danke schon einmal im Vorab.

Und bitte macht fleissig weiter so in diesem Forum.. das ist eine super Quelle, um VBA zu lernen (bzw. sich selbst zu helfen).


LG


Patrick

Betrifft: AW: Zeilennummer in externen Dateien
von: Regina Resch-Jansen
Geschrieben am: 04.11.2020 11:36:13

Hi Patrick,
nimm doch mal die Find-Methode, die spuckt Dir bei einem Treffer ein Range-Objekt aus und von dem kannst Du die Zeile auslesen:
Dim rng_fund as Range
dim str_suchwort as string
str_suchwort = "test"
set rng_fund = Worksheets("Tabelle1").Columns(2).Find(suchwort, LookAt:=xlwhole)
if not rng_fund is Nothing Then
     Msgbox rng_fund.row
end if
Dieser Code sucht in Spalte B von Blatt Tabelle1 das Wort test und gibt die Zeilennummer aus, in der es gefudnen wurde.

Gruß Regina

Betrifft: AW: Zeilennummer in externen Dateien
von: Patrick
Geschrieben am: 04.11.2020 11:54:42

Hallo Regina,

danke dir. Das probiere ich gleich aus.

Kannst du mir noc helfen, den Zeiger auf die externe Datei zu stellen?
Worksheets("Tabelle1")
C:\Ordner\test.xlsx
und darin die Tabelle1... ich bekomme es nicht hin...

Betrifft: AW: Zeilennummer in externen Dateien
von: Regina Resch-Jansen
Geschrieben am: 04.11.2020 12:05:28

ok, die externe Datei muss ja geöffnet werden, also so:
Dim obj_wkb_quelle as Workbook
Dim rng_fund as Range
Dim str_suchwort as string

Set obj_wkb_quelle = Workbooks.Open ("C:\Ordner\test.xlsx")

str_suchwort = "test"
set rng_fund = obj_wkb_quelle.Worksheets("Tabelle1").Columns(2).Find(suchwort, LookAt:=xlwhole)
if not rng_fund is Nothing Then
     Msgbox rng_fund.row
end if
Gruß Regina

Betrifft: AW: Zeilennummer in externen Dateien
von: Patrick
Geschrieben am: 04.11.2020 12:22:47

Hallo Regina,

ich habe irgendein Syntax-Thema (Laufzeitfehler 9):
strPath_Laender = "E:\patrick\Unterlagen HDW\talea\Produktentwicklung\Recruitment Insights\ _
Fachkräfte-Engpass Länder\"
strFile_Laender = Dir(strPath_Laender & "*.xlsx")
Do While strFile_Laender <> ""
 With Worksheets("'" & strPath_Laender & "[" & strFile_Laender & "]" & "3.2 Engpass_Tab2" & "'") _


und bei diesem Worksheet-Verweis stimmt etwas nicht und ich versteh nicht, was ich falsch mache?
With Worksheets('E:\patrick\Unterlagen HDW\[test_01.xlsx]3.2 Engpass_Tab2')

Stimmt der Verweis auf die externe datei-Tabelle nicht in dem Worksheets-Command?

Betrifft: AW: Zeilennummer in externen Dateien
von: Regina Resch-Jansen
Geschrieben am: 04.11.2020 12:35:45

Hi, das funktioniert so nicht. ich habe es so verstanden, dass Du einen Wert aus der Datei auslesen und in Deine andere datei schreiben willst. Dazu musst Du die externe Datei öffnen und kannst nicht einfach auf sie verweisen. So habe ich das auch in meinem Code gemacht (Workbooks.Open).

Gruß Regina

Betrifft: AW: Zeilennummer in externen Dateien
von: Patrick
Geschrieben am: 04.11.2020 12:53:36

Hallo regina,

ich befürchte mein Problem ist noch viel trivialer. Komme gleich drauf zurück.
Ich habe ein lokales Verzeichnis, das ich in der Variablen strPath_Laender speichere
In diesem Verzeichnis lliegen etwa 16 xlsx-Dateien, die ich nacheinander durcheghen muss, jeweils in das Tabellenblatt "3.2 Engpass_Tab2", um dann dort in Spalte B die Zeile des Suchworts zu finden


Set obj_wkb_quelle = Workbooks.Open ("C:\Ordner\test.xlsx")

Mein Problem ist der Ausdruck "C:\Ordner\test.xlsx" - wohl eher die ", wenn ich mit Variablen diesen Pfad "nachstelle":

Set obj_wkb_quelle = Workbooks.Open (" & strPath_Laender & strFile_Laender & ")

Wenn ich diese Zeile jetzt wieder auflöse, dann fehlen mir doch eigentlich die ", oder?
also aus meiner Zeile wird doch
Set obj_wkb_quelle = Workbooks.Open (C:\Ordner\test.xlsx)

Aber dann fehlen doch die " um meinen Dateipfad.

Und genau da hänge ich gerade fest...

Betrifft: AW: Zeilennummer in externen Dateien
von: Oberschlumpf
Geschrieben am: 04.11.2020 13:11:01

Hi Patrick,

dein Problem ist (zur Zeit) das nicht-Verstehen von VBA.
Da du aber so gar nicht auf meinen Beitrag eingegangen bist, halte ich mich nun auch weiter raus.

Viel Erfolg weiterhin.

Thorsten

Betrifft: AW: Zeilennummer in externen Dateien
von: Patrick
Geschrieben am: 04.11.2020 14:54:06

Hey Thorsten,

sorry. Ich habe nur gesehen, dass ich mit Reginas Ansatz weiterkommen werde und wollte daher nicht "alle" im Forum beschäftigen. Danke trotzdem für dein Hilfsangbeot. Bitte nciht traurig sein, wenn ich erst diesen Antwortstrang weiterverfolgt habe.

dein Problem ist (zur Zeit) das nicht-Verstehen von VBA.
Nicht ganz. Mir fehlen die Befehle und die Syntax. Was die Zeilen machen, verstehe ich in der Regel schon. Ja, und Übung fehlt auch.

Betrifft: AW: Zeilennummer in externen Dateien
von: Oberschlumpf
Geschrieben am: 04.11.2020 15:15:38

Hi Patrick,

zum Verstehen von VBA gehört das Wissen der Befehle hinzu (genau wie bei einer Fremdsprache muss man den Wortschatz kennen (bei VBA ist der Wortschatz jeder einzige Befehl))
SO meinte ich meinen Hinweis auf dein jetziges Problem.

Ciao
Thorsten

Betrifft: AW: Zeilennummer in externen Dateien
von: Regina Resch-Jansen
Geschrieben am: 04.11.2020 13:13:46

... nicht ganz, die "" um den Pfad und den Dateinamen herum sind nur nötig, wenn Du den String quasi als absoluten Wert schreibst. Arbeitest Du mit Variablen, werden die "" nicht benötigt:

Set obj_wkb_quelle = Workbooks.Open(strPath_Laender & strFile_Laender) reicht aus

Gruß Regina

Betrifft: AW: Zeilennummer in externen Dateien
von: Patrick
Geschrieben am: 04.11.2020 14:51:28

Perfekt.
Alles funktioniert wie gewünscht. Danke Dir Regina.

Betrifft: AW: Zeilennummer in externen Dateien
von: Regina Resch-Jansen
Geschrieben am: 04.11.2020 16:56:36

Prima, danke für die Rückmeldung.

Gruß Regina

Betrifft: AW: Zeilennummer in externen Dateien
von: Oberschlumpf
Geschrieben am: 04.11.2020 11:39:01

Hi Patrick,

anstelle nur eines Codeteils zeig doch per Upload eine Bsp-Datei mit allen notwendigen Bsp-Datenzeilen und dem Code.

Ciao
Thorsten

Betrifft: AW: Zeilennummer in externen Dateien
von: ralf_b
Geschrieben am: 04.11.2020 11:46:13

Benötigst du die Werte im Zielblatt oder eine Formel?
Cells(loZeileZielmappe, 1).Value = _
           "='" & strPath_Laender & "[" & strFile_Laender & "]" & "3.2 Engpass_Tab2" & "'!T4"
Wenn die Quelldatei geöffnet ist, kannst du doch einfach auch per find den wert suchen.


z.b.
with Worksheets("" & "'" & strPath_Laender & "[" & strFile_Laender & "]" & "3.2 Engpass_Tab2" &  _
"'" & "")
    Set Suchergebnis = .Range("B1:B" & .Cells(rows.count, "B").end(xlup).row).find(Cells( _
loZeileZielmappe, 1).Value, xllookin:=XlValues, Lookat:= xlWhole)
if not Suchergebnis is nothing then
   Cells(loZeileZielmappe, 1).offset(,1).Value = Suchergebnis.offset(,-1).Value 
end if 

End With


Beiträge aus dem Excel-Forum zum Thema "Zeilennummer in externen Dateien"