PDA

Vollständige Version anzeigen : Zugriff auf Datei mittels Workbooks


JustMe
01.10.2016, 14:30
Hallo,
ich hätte da gerne mal ein Problem.
Ich möchte Daten aus einer anderen Excel-Datei lesen und öffne diese mittels Workbooks.Open.
In dem Sub funktioniert es in der Function nicht. Dort ist bei With Worksheets("2016") ende (ohne Fehlermeldung). In der Zelle in der ich die Funktion anwende erhalte ich "!Wert"

Funktioniert

Sub Blabla() '(KW As String) ', Bereich As Range)
'
' MA_ANWESEND Makro
'
Dim bla As Variant

'
Workbooks.Open Filename:="c:\test\MT-Einrichter-2011.xls"
With Workbooks
With Worksheets("2016")
bla = .Cells(6, 1).Value
End With
End With
Workbooks.Close



End Sub


Funktioniert nicht:

Function MA_ANWESEND(KW As String) ', Bereich As Range)
'
' MA_ANWESEND Makro

'
MA_ANWESEND = 999
Workbooks.Open Filename:="c:\test\MT-Einrichter-2011.xls"

With Workbooks '("c:\test\MT-Einrichter-2011.xls")
With Worksheets("2016")
For i = 1 To 300
If UCase(Trim(.Cells(i, 1).Value)) = UCase(KW) Then
MA_ANWESEND = i
End If
Next i
End With
End With
Workbooks.Close
End Function

Hat jemand einen Hinweis bzw. eine andere Möglichkeit die Daten aus der externen Tabelle zu lesen ?

Danke schonmal

ebs17
01.10.2016, 19:43
Verwende saubere Referenzierungen auf Objekte und Auflistungen:
Option Explicit

Function MA_ANWESEND(KW As String) As Long
Dim oWorkbook As Workbook
Dim i As Long
Set oWorkbook = Workbooks.Open Filename:="c:\test\MT-Einrichter-2011.xls"

With oWorkbook.Worksheets("2016")
For i = 1 To 300
If UCase(Trim(.Cells(i, 1).Value)) = UCase(KW) Then
MA_ANWESEND = i
End If
Next i
End With

oWorkbook.Close
End Function
Über den sonstigen Sinn und Stil möchte ich nicht nachdenken.

JustMe
02.10.2016, 13:29
Vielen Dank Ebs,
hab es mir einfach gemacht und deinen Code kopiert.
Da erscheint leider das....

97355

Ich probiere mal weiter.
Gruß

JustMe
02.10.2016, 13:37
so kommt kein Syntaxfehler mehr

Function MA_ANWESEND(KW As String) As Long
Dim oWorkbook As Workbook
Dim i As Long
Set oWorkbook = Workbooks.Open("c:\test\MT-Einrichter-2011.xls")

With oWorkbook.Worksheets("2016")
For i = 1 To 300
If UCase(Trim(.Cells(i, 1).Value)) = UCase(KW) Then
MA_ANWESEND = i
End If
Next i
End With

oWorkbook.Close
End Function

Das Problem ist aber nach wie vor das gleiche.
Die Datei wird nicht geöffnet.
Meine Frage wieso es in der Subroutine funktioniert und in der Funktion nicht ist auch noch nicht beantwortet.

Storax
02.10.2016, 19:40
Wenn ich das richtig sehe, wird die Datei in beiden Fällen geöffnet, nur bei der "Sub" nicht wieder geschlossen. Das solltest Du eigentlich beim Debuggen mitkriegen.

JustMe
03.10.2016, 09:31
In der Funktion wird die Datei definitiv nicht geöffnet, und vermutlich deswegen, geht es in der Zeile

With oWorkbook.Worksheets("2016")

nicht weiter

JustMe
03.10.2016, 09:49
Und eines noch zu Ebs post.
Sauber Programmieren sollte man...aber dann sollten auch die Beispiele in der MS-Doku so programmiert sein. Da ist kein Hinweis auf SET....

Hier das Beispiel von MS

Sub ImportWorksheet()
' This macro will import a file into this workbook
Sheets("Sheet1").Select
PathName = Range("D3").Value
Filename = Range("D4").Value
TabName = Range("D5").Value
ControlFile = ActiveWorkbook.Name
Workbooks.Open Filename:=PathName & Filename
ActiveSheet.Name = TabName
Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)
Windows(Filename).Activate
ActiveWorkbook.Close SaveChanges:=False
Windows(ControlFile).Activate
End Sub

Storax
03.10.2016, 09:52
Das stimmt nicht! Die Datei wird geöffnet. Debuggen hilft!
Wie lautet denn die Fehlermeldung bei With oWorkbook.Worksheets("2016")? Gibt es das Blatt "2016"?
http://www.excel-easy.com/vba/examples/debugging.html

RPP63neu
03.10.2016, 10:59
Moin!
Sauber Programmieren sollte man...aber dann sollten auch die Beispiele in der MS-Doku so programmiert sein. Da ist kein Hinweis auf SET....
Nun ja …
ebs hat eine Objektvariable oWorkbook benutzt.
Diese Variablen werden mit Set = gefüllt.
Allerdings hat sich da ein kleiner Fehler eingeschlichen.
Der Übergabeparameter gehört hier in eine Klammer:
<pre style='border:thin solid #000000; padding:12px 24px; margin-left:12px; color:#000000'><span style='color:#0000EE'>Sub</span> Q() <span style='color:#0000EE'>Dim</span> oWorkbook <span style='color:#0000EE'>As</span> Workbook <span style='color:#0000EE'>Set</span> oWorkbook = Workbooks.Open(<span style='color:#FF0000'>&quot;J:\aktueller Stand\Buchstabensalat.xlsm&quot;</span>) <span style='color:#0000EE'>With</span> oWorkbook <span style='color:#0000EE'>Debug</span>.<span style='color:#0000EE'>Print</span> .Worksheets(<span style='color:#FF0000'>&quot;Tabelle3&quot;</span>).Cells(<span style='color:#FF0080'>1</span>, <span style='color:#FF0080'>1</span>).Value <span style='color:#0000EE'>End</span> <span style='color:#0000EE'>With</span> <span style='color:#0000EE'>End</span> <span style='color:#0000EE'>Sub</span></pre>

Gruß Ralf

JustMe
03.10.2016, 13:51
Also Leute nehmt es mir nicht übel...aber wie in dem Thread-Start geschrieben. Wenn ich das ganze in einem Sub mache funktioniert es und die Variable bla enthält den Wert aus Zelle 6,1.
In der Function hatet ich genau die selben Anweisungen, also Zugriff auf die selbe Datei und die gleiche Tabelle "2016".
Ich bekomme keine Fehlermeldung und im Debugger bin bleibe ich in der Zeile "With Worksheets("2016")" hängen.

Storax
03.10.2016, 13:53
Beispieldatei

JustMe
03.10.2016, 15:18
Hab es jetzt erstmal gelöst. Das Problem kam wahrscheinlich daher das ich die Datei am Ende der Funktion immer wieder geschlossen habe.

Habe jetzt in der Arbeitsmappe das öffnen und die Objektzuweisung.

Sub Workbook_Open()
'
Set oWorkBook = Workbooks.Open("c:\test\MT-Einrichter-2011.xls")


End Sub

Und im Modul 1 die Variablendeklaration und die Funktion

Option Explicit
Public oWorkBook As Workbook




Function MA_ANWESEND(KW As String) As Long
Dim i As Long

With oWorkBook.Worksheets("2016")
For i = 1 To 400
If UCase(Trim(.Cells(i, 1).Value)) = UCase(KW) Then
MA_ANWESEND = i
End If
Next i
End With

End Function


Jetzt bräuchte ich noch nen Tip wie ich den Set oWorkbook = .... hinbekomme wenn die Datei bereits geöffnet ist.

Für euren Input bin ich weiterhin dankbar.

Gruß,
Rolf

JustMe
03.10.2016, 15:20
Und ein hinweis wie die werte neu berechnet werden nachdem die Datei geöffnet wurde.

Nochmal Danke

ebs17
03.10.2016, 15:40
Was Du bei allem nicht zeigst, ist der Aufruf der Funktion ...!

In der Sub weist Du die ermittelte Anzahl einer Zelle zu. Da sieht man auch das Ergebnis.
In der Funktion wird ein Rückgabewert erzeugt, man kann aber nicht erkennen, was mit diesem Rückgabewert passiert. Wenn die Funktion ohne Zuweisung einfach so aufgerufen wird, verpufft natürlich der Rückgabewert ins Datennirwana.

Man sollte also erst einmal über Abläufe im Zusammenhang mit der Umgebung nachdenken, ehe man sich in Code-Probierereien verliert. Codes sind nichts anderes als umgesetzte Ablaufpläne.

//Edit: Danke für die obigen Korrekturhinweise.

JustMe
03.10.2016, 16:38
Die Funktion wird in einer Zelle der Tabelle aufgerufen.
z.B.
=MA_ANWESEND("KW40")
Damit kann ich prüfen ob ein Mitarbeiter in KW40 verfügbar ist.
Da fehlt zwar noch ein bisschen was aber das ist nicht Kriegsentscheidend.

Letztendlich geht es darum aus einer Datei in der die Mitarbeiter ihren Urlaub etc. eintragen zu ermitteln ob ich sie in einer bestimmten Woche verplanen kann.
Heute würde man diese Tabellen sicherlich generell anders gestalten. Die Dinger haben sich im Laufe der Jahre so entwickelt und ich möchte das Rad nicht komplett neu erfinden.

In der finalen Ausbaustufe kann ich dann in einer zweiten Liste in einer DropDown Liste die vorhandenen Mitarbeiter auswählen, und wenn er dann verplant ist erscheint er auch in der Dropdown Liste nicht mehr.

Das ist das Endziel.
Ich Taste mich halt vor und löse ein Problem nach dem anderen. Bevor ich hier gepostet hatte habe ich auch schon viel gegoogelt.

Geht es in diesem Forum darum die perfekten Programmierer auszubilden, oder geht es darum einfach mal zu helfen ?

Gruß

Storax
03.10.2016, 17:13
Last Call: Beispieldatei

Und übrigens habe ich ganz oben schon gesagt:Wenn ich das richtig sehe, wird die Datei in beiden Fällen geöffnet, nur bei der "Sub" nicht wieder geschlossen. Das solltest Du eigentlich beim Debuggen mitkriegen.Das ist Dir dann später auch aufgefallen :upps: Hab es jetzt erstmal gelöst. Das Problem kam wahrscheinlich daher das ich die Datei am Ende der Funktion immer wieder geschlossen habe.Und dann schreibst DuIn der Funktion wird die Datei definitiv nicht geöffnet, und vermutlich deswegen, geht es in der Zeile Definitiv sagt mir das, dass Du den Code nicht verstehst. Kein Problem, aber Du kannst nicht Debuggen, lädst keine Beispieldatei hoch und versuchst die Tipps, die man gibt, nicht einmal zu verstehen.

ebs17
03.10.2016, 18:14
Geht es in diesem Forum darum die perfekten Programmierer auszubilden, oder geht es darum einfach mal zu helfen ?
Nun, wenn man zum Durchqueren einer Pfütze durch vorheriges Überlegen dazu kommt, dass ein aufrechter Gang geradeaus weniger belastend ist als ein Durchkriechen, wird man nun nicht unmittelbar zum Ozeandurchquerer.
Es ist mir durchaus vertraut, dass die Ermutigung zum Nachdenken desweilen als Unzumutbarkeit, Unverschämtheit oder gar als Beleidigung empfunden wird. Ich komme aus der Datenbankenecke. Da entwickelt man, und da Daten und damit Vorgänge konzeptionell häufig vorkommen können, ist es effizienter, erst zu planen als über Try & Error sich heranzutasten. Letzteres wird in der Praxis dann nicht wirklich funktionieren. Wenn das Gehen funktioniert, um an Nachbars Zaun zu kommen, wird es trotzdem unpraktikabel sein, wenn mein Ziel dann Kapstadt oder New York ist - obwohl es "funktionieren" könnte.

Zum Thema:
Für eine einfache Zellzuweisung (ohne VBA und ohne Neuberechnung des Blattes?) eine Funktion, die eine externe Datei öffnet und auswertet, und das wahrscheinlich bei mehreren Mitarbeitern, also den Vorgang in langer langer Reihe?
Wenn es dann funktioniert, dann wahrscheinlich nicht gut. Diese Form des Herantastens an Lösungen dürfte sehr sehr langwierig sein, und so langwierig sein, dass Begleiter abspringen.

Meine Überlegung:
- Pro Mitarbeiter die gleiche Datei zu öffnen und zu schließen ist ziemlich merkwürdig - und uneffizient.
- Da die Urlaubseintragungen sicher nicht ständig laufend und überschneidend mit Deinen Auswertungen sind, benötigt man im Normalfall keine Momentanaktualität und somit keine permanente Verknüpfung.

Im Ergebnis könnte man sich also alle Werte zu den Mitarbeitern mit einer einmaligen Aktion aus der anderen Datei (oder aus mehreren) holen und in einer geeigneten Struktur in einer zusätzlichen Tabelle ablegen. Dies dann per eigenständig gestartetem Makro, oder man lässt das beim Workbook_Open der Auswertungsmappe laufen.

JustMe
07.10.2016, 09:43
@Storax: Ich finde deinen Unterstellungen ehrlich gesagt etwas unverschämt. Ich habe wahrscheinlich schon meine ersten Programme geschrieben als du noch in den Windeln gelegen hast, oder kennst du einen Atari800XL ? Und ich bin durchaus in der Lage Haltepunkte zu setzen und zu debuggen. Wenn der tolle Debugger allerdings keine vernünftigen Hinweise zum debuggen gibt (z.B. Objekt nicht referenziert oder Datei bereits geöffnet etc.) dann kann man sich das ganze auch sparen.
Ich habe auch kein Problem mit Kritik aber das ganze bitteschön mit der notwendigen Portion "Höflichkeit".
Aber vielleicht postest du ja auch nur um Sternchen zu sammeln.

Generell:
Drei einfache Hinweise hätten genügt um mein ursprüngliches Problem zu lösen. Die kamen aber nicht von hier sondern die habe ich mir selbst erarbeitet.

1. Ein global Workbook Objekt definieren

Code im Deklarationteil von Modul 1:
Public oWorkBook As Workbook

2. Die externe Datei beim Öffnen nur einmal öffnen und dabei das Workbook Objekt referenzieren (das geschieht beim öffnen der Arbeitsmappe)

Code in der Arbeitsmappe:

Sub Workbook_Open()

'
Set oWorkBook = Workbooks.Open("c:\test\MT-Einrichter-2011.xls")


End Sub



3. Die externe Datei am Ende der Funktion nicht mit schließen.

Code in der Funktion:

Function MA_ANWESEND(KW As String, MA_NAME As String) As Long

Application.Volatile

Dim i As Long
Dim j As Long
Dim name As String
Dim spalte_name As Long
Dim zeile_kw As Long

spalte_name = 0
zeile_kw = 0
i = 1
j = 0


name = UCase(MA_NAME)

' Mitarbeiter in Urlaubsliste finden

With oWorkBook.Worksheets("2016")
For i = 1 To 40
If InStr(1, UCase(Trim(.Cells(2, i).Value)), name) <> 0 Then
spalte_name = i
Exit For
End If
Next i

' Kalenderwoche in Urlaubsliste finden

For i = 1 To 400
If UCase(Trim(.Cells(i, 1).Value)) = UCase(KW) Then
zeile_kw = i
Exit For
End If
Next i

' Schauen ob der Mitarbeiter verfügbar ist

For i = zeile_kw To zeile_kw + 5
If (UCase(Trim(.Cells(i, spalte_name).Value)) = "N") Or (UCase(Trim(.Cells(i, spalte_name).Value)) = "M") Then
j = j + 1
End If
If j >= 3 Then
MA_ANWESEND = 1
Else
MA_ANWESEND = 0
End If
Next i
End With

End Function

Wie gut oder schlecht mein Code ist sei dahin gestellt. Sicherlich sind die festen Werte bei den Schleifenzähler nicht schön. Aber ehrlich gesagt es erfüllt seinen Zweck und ich habe nicht die Muse wieder tagelang nach der geeigneten Excel Funktion bze. der Objekteigenschaft zu suchen welche die letzte (wirklich letzte) verwendetet Spalten und Zeilennummer enthält.

Ich muss nach wie vor das Problem lösen wie ich das Workbook Objekt referenziere wenn die externe Datei bereits geöffnet ist (wenn ich das geschafft habe werde ich es auch noch posten).

Aber viel Hilfe erwarte ich hier nicht.

Mit Programmieren hat das alles sowieso nicht mehr viel zu tun. Es geht doch nur noch darum die richtigen Objekte und der Funktionen zu finden.
Wenn man das geschafft hat kann man sich mit der schlechten Dokumentation herumplagen.

Wie auch immer ich hoffe das ich anderen die mit einer externen Datei arbeiten müssen ein wenig geholfen habe.

@Ebs: Aufrichtigen Dank für deine konstruktive Kritik/Hinweise.

ebs17
07.10.2016, 11:29
Ich habe auch kein Problem mit Kritik aber das ganze bitteschön mit der notwendigen Portion "Höflichkeit".
Höflichkeit sollte keine Einbahnstraße sein ...

Praktische Höflichkeit (abseits von Hallo und Anreden usw.) beginnt aber m.M. nach bereits damit, die Fragen eines potentiellen Helfers, der sich immerhin schon mal die Zeit mit der Beschäftigung mit dem genannten Problem genommen hat, vollständig zu beantworten, und zwar gleich bzw. idealerweise und besonders, wenn man auf "eigene Ahnung" verweist, bereits im Vorfeld - da man sich bestimmte Nachfragen denken kann und sich selber bereits beantwortet hat.

Hinweise ... habe ich mir selbst erarbeitet
Die Referenzierung der Mappe (Objektvariable) hättest Du eingangs erwähnen können ...

Ich muss nach wie vor das Problem lösen wie ich das Workbook Objekt referenziere wenn die externe Datei bereits geöffnet ist
VBA - Aus Excel prüfen ob Word Dokument offen (http://www.ms-office-forum.net/forum/showpost.php?p=1763546&postcount=4)
Das Vorgehen ist auf eine Excelmappe wie auch auf vielfältige andere Dateitypen übertragbar.

Storax
07.10.2016, 11:54
@JustMe:
Falls Du tatsächlich so vertiefte Kenntnise hast, wie Du behauptest, dann sollte das ganze hier alles kein Problem sein. Aber Du kannst ja nicht mal Code-Tags setzen.

Du willst keine Beispieldatei liefern, trotz mehrmaliger Bitte ... Dann musst Du mit dem Echo leben.

Und gehe davon aus, dass meine Kompetenz an dieser Stelle Deine bei weitem übersteigt. Auch gehe davon aus, dass Du jünger bist als ich.

Wer sich mit Atarti brüstest, sollte erstmal TR 440 oder BS 2000 buchstabieren lernen.

End of communication