Excel: SVERWEIS (VLOOK UP) knifflig!

@Urs2

1. Was meinst du mit "Augenwischerei"?

2. Ich kann deinen VBA-Code schon nachvollziehen. Wie lange hast du fuer den kompletten Code gebraucht?

3. Ich persoenlich habe nur geringe Erfahrung mit VBA. Zudem nutzte ich ein englischsprachiges Excel. Ich weiss nicht wie aufwendig der Code fuer meine Fragestellung ist und wie lange es dauert, aber koenntest du das fuer mich probieren? Ich wuerde dann nur uebersezten.

Schema:

1. Datei oeffnen - Nummer wird kreiert
2. Dateneingabe
3. Speichern
3.1 pdf.-file (in PW-geschuetzten Ordner)
3.2 xls.-file (in oeffentlichen Ordner)
4. Datei schliesst sich
> oder so aehnlich

Ich nutze uebrigens free-pdf+gohstscript.

Viele Gruesse
Markus

 

@Urs2

1. Was meinst du mit "Augenwischerei"?

2. Ich kann deinen VBA-Code schon nachvollziehen. Wie lange hast du fuer den kompletten Code gebraucht?

3. Ich persoenlich habe nur geringe Erfahrung mit VBA. Zudem nutzte ich ein englischsprachiges Excel. Ich weiss nicht wie aufwendig der Code fuer meine Fragestellung ist und wie lange es dauert, aber koenntest du das fuer mich probieren? Ich wuerde dann nur uebersezten.

Schema:

1. Datei oeffnen - Nummer wird kreiert
2. Dateneingabe
3. Speichern
3.1 pdf.-file (in PW-geschuetzten Ordner)
3.2 xls.-file (in oeffentlichen Ordner)
4. Datei schliesst sich
> oder so aehnlich

Ich nutze uebrigens free-pdf+gohstscript.

Viele Gruesse
Markus

 

...dass der Passwortschutz von Excel nicht über alle Zweifel erhaben ist. Die Zellen mit den SVerweisen in Deiner Datei waren ja auch passwortgeschützt (..warum eigentlich, wir sollten sie ja sehen?) - jetzt sind sie nicht mehr passwortgeschützt...

So schnell bin ich nicht, ich weiss aber nicht wie lange ich gebraucht hatte, so etwas mache ich nie am Stück.

Beim Code gibt es nichts zu übersetzen... er ist ja bei allen Sprach-Versionen schon englisch...

1. Die ganze Datei ist dann wohl eine Vorlage, die man öffnet, bearbeitet, die Ausgabe speichert und den Rest verwirft. Richtig?

2. Die Art der Eingabe ist noch offen...

3. Als XLS speichern ist OK. Als PDF... das ist sicher ein Druckertreiber? Soweit auch OK.
Aber das ist dann eine andere Anwendung, die Speichern muss... wie man der beibringt im richtigen, und erst noch passwortwortgeschützten Ordner zu speichern...?

4. Schliessen beider XLS ist wieder OK. Das muss wohl sein, damit man wieder ein leeres Ausgabeblatt vor sich hat...

Bis auf die PDF sollte das kein grösseres Problem sein, aber zuerst muss von Dir die Eingabeart definiert werden...
Woher stammen eigentlich die vielen Daten? Werden die eingetippt? Oder online bezogen?

Gruss Urs

 

Genau so hatte ich mir das vorgestellt.

1. Ja

2. Dateneingabe siehe letztes Template: Dropdown in Spalte A und B. Eingabe der Menge in Spalte D. Das ist meine Eingabeart.(?) Oder meinst du eine Schaltflaeche, die den VBA anwirft?

3. Sagen wir einfach, das wir erstmal nur eine xls.-Datei in einem anderen Ordner als den des Templates speichern.

4. Ja

Die Datensaetze sind in einer Datenbank im Unternehmen. Sie werden dann (einmalig) in das Template importiert. Sie koennen aber ergaenzt oder geloescht werden, wenn sich Lieferanten etc. aendern.

Viele Daten? Im jetzigen Template ist gerade ersteinmal ein Bruchteil enthalten.

 

{<Schaiss-Forum! Das Mistvieh hatte meinen Beitrag gefressen... oder getötet... jetzt halt nochmals...>}

...das hatte ich mir auch so vorgestellt!
Nach Deinem ersten Beitrag hatte ich gedacht, so etwas könnte ich für mich auch brauchen. Ich hatte getestet, zwar mit Deinen Daten, aber mit Blick auf meine eher kleinere und eher statische Datenbasis...
In den letzten Tagen hatte ich nicht ausschliesslich an Dein Problem gedacht... aber vergessen hatte ich Dich nicht!

Meine grosszügige Erklärung, ein einzelnes Tabellenblat liesse sich leicht in einer anderen XLS-Datei speichern, funktioniert in unserem Fall nicht so einfach...

Da genau in diesem Blatt Buttons und ein Teil der Makros sind, gibt es Aerger!
Je nach Methode werden diese Dinger teilweise oder ganz mitkopiert. Man kann zwar dann auf die Buttons klicken, passieren tut aber nichts, ausser dem Erscheinen von Fehlermeldungen, weil ja nur ein Teil des Codes noch vorhanden ist...
Diesen Code entfernen, mit sinnvollem Aufwand, sehe ich nicht...

Es ist mir sowieso nicht ganz eingängig, warum das Ausgabesheet als eigenständige XLS-Datei gespeichert werden sollte. Welche Beweiskraft hätte so etwas, mit der sich laufend ändernden Datenbasis?

Dein PDF-Erzeuger dürfte wohl als Drucker auch Kommando- und damit Scriptzeilen akzeptieren, um die PDF, ohne User-Eingaben, in einen nicht gesperrten Ordner ablegen zu können?
Wenn nötig, könnte man dann immer noch die Nutzdaten, unformatiert - zur weiteren Verarbeitung, an eine andere xyz-Datei anhängen.

Die Nummerieung und Datierung des Blattes sind dagegen kein Problem...

Ich werde mich heute Nacht mit einer ergänzten XLS melden...

Gruss Urs

 

@Urs2

Das Ausgabesheet muss gespeichert werden. Es geht mir ja nicht um die Daten in den Blaettern 'supplier' und 'products'. Viel wichtiger ist quasi eine Kopie des Kaufbelegs + Informationen fuer das Rechnungswesen ('sheet1'). Denn nachdem der Produktionsbereich bestellt hat, muss der ausgedruckte Beleg (oder eine xls.-Kopie) der Person vorliegen, die schliesslich die Informationen in SAP bucht. Das ist der Hintergrund.

Und zur Nummerierung. Wir haben uns gedacht die Datei so zu bennen. "PO0000001.xls". Damit liessen sich die Belege ueber 50 Jahre speichern.

Ich habe folgenden Code probiert, aber der hat ein anderes Format und ich konnte ihn auch nicht an das gewuenschte Format anpassen:

Sub save()
Dim dname As String
Dim pfad As String
Dim x As Byte
Dim y
x = 1
pfad = "C:\Documents and Settings\TermMarkusS\Desktop\test"
dname = Format("PO")
Application.DisplayAlerts = False
If Dir(pfad & "/" & dname & ".xls") = "" Then
ActiveWorkbook.SaveCopyAs Filename:=pfad & "/" & dname & ".xls"
Else
If Dir(pfad & "/" & dname & "-" & x & ".xls") = "" Then
ActiveWorkbook.SaveCopyAs Filename:=pfad & "\" & dname & "-" _
& x & ".xls"
Else
If Dir(pfad & "/" & dname & "-" & x & ".xls") <> "" Then
If Len(Dir(pfad & "/" & dname & "-" & x & ".xls")) < 20 Then
'Die Zahl 20 muß je nach Verzeichnisnamen angepasst werden
Do Until Dir(pfad & "/" & dname & "-" & x & ".xls") = ""
y = Mid(Dir(pfad & "/" & dname & "-" & x & ".xls"), 16, 1)
'Die Zahl 16 hängt von der Länge des Verzeichnisses ab
x = y + 1
Loop
Else
Do Until Dir(pfad & "/" & dname & "-" & x & ".xls") = ""
y = Mid(Dir(pfad & "/" & dname & "-" & x & ".xls"), 16, 2)
'Die Zahl 16 hängt von der Länge des Verzeichnisses ab
x = y + 1
Loop
ActiveWorkbook.SaveCopyAs Filename:=pfad & "/" & dname & "-" _
& x & ".xls"
End If
End If
End If
End If
Application.DisplayAlerts = True
End Sub

(Wenn man keine Buttons auf einem zu speichernden Blatt anlegen kann - vielleicht tu's ein Short-Cut?)

(Ja. Eine pdf-Datei wuerde ich ueber das Print-Menue speichern.)

Was mir noch aufgefallen ist: Wenn ich das ausgefuellte Blatt ueber ein Makro speichere, dann wird (jedesmal) eine neue Datei angelegt. Man darf also nur das 1. Mal ueber ein Makro speichern und dann (bei Bedarf und Korrekturen) nur noch ueber normal speichern.

Und liesse sich der Dateiname (also die Bestellnummer) auf das 'sheet1' projezieren?

Es gruesst Markus

 

Hi Markus,

Wie einem doch Excel immer überrascht - mit einem fast gleichen Code kann ich heute abspeichern, was gestern nicht ging...
Wenn es bei Dir nicht gehen sollte, den schon bezeichneten Code in Tabelle1 einfach auskommentieren.
Bis jetzt habe ich leider noch keinen Weg gefunden, die Spaltenbreiten anpassen zu lassen.

Der Code ist im VB-Editor jetzt in drei Ordnern > aEingabe_frm + aNumDoc + Tabelle1

Da Excel alles vergisst, wenn es geschlossen wird, muss die verfügbare Nummer ausserhalb gespeichert werden. Ich habe dazu eine INI-Datei genommen. Es ist später gleich wo sie liegt (solange der Code angepasst wird), alle Benutzer der Vorlage müssen aber Lese- und Schreibzugriff haben.

Bedingungen, dass mein Beispiel funktioniert >
- die numDocument.ini muss im Verzeichnis "c:\temp\" liegen
- die abgespeicherte Eingabeseite kommt nach "c:\trash\" , der Ordner muss vorhanden sein.

Ich habe mit einem anderen Packer gezippt und auch eine als SQX komprimierte Version angehängt (bei den INI und SQX, dieTXT-Endung wegmachen). Jetzt wären es mehr als 100kB... hoffentlich geht es diesmal.

Schau es Dir an!

Gruss Urs

 

Hi Markus,

Ich habe noch etwas verbessert.
Jetzt wird eine echte Kopie des ersten Sheets gespeichert, mit allen Formatierungen, die Buttons werden entfernt.

Den Macrocode habe ich noch nicht weggebracht. Aus Excel heraus sind die Macros allerdings nicht sichtbar/benutzbar. Da es Private Subs sind, werden sie dort gar nicht angezeigt - nur im VB-Editor könnte man sie sehen...

http://www.siteupload.de/p744029-VersuchDropdownsmitVBA2xlszip.html
Es ist eine XLS, den ZIP einfach wegmachen...

Die INI-Datei vom letzten Beitrag gehört dazu nach c:\temp\
Die Ausgabe erfolgt jetzt auch nach c:\temp\ (nicht \trash)

Gruss Urs

 

Hi Markus,

Damit im gespeicherten Einzelblatt gar kein ausführbarer Code enthalten ist, habe ich dort nur Verweise auf ein anderes, im Einzelblatt nicht vorhandenes Modul gelassen. Version 3 >>

http://www.siteupload.de/p744761-VersuchDropdownsmitVBA3xlszip.html
= XLS-Datei, einfach ZIP-Endung wegmachen.

Schau Dir das an, wenn es wieder geht...

Gruss Urs

 

@Urs

Zum einen dies...

 

@Urs

Zum anderen das...

Ich krieg's einfach nicht zum laufen.
Die numDocument ist im richtigen Ordner.
Auch die Kopie einer Bestellung.

Ich benutze eine englische Version von Excel. Kann es sein, dass ich bestimmte Begriffe umbennen muss? Eigentlich nicht.

(In deiner Version vom 7.5. sind keine Makros enthalten.)

Viele Gruesse,
Markus

 

@deltafox

Ich benutze Excel2003 unter XP. Und Du?

Du hast doch die Version aus meinem #29? Die andere hatte ich gelöscht.
Deine ZIP konnte ich wieder nicht öffnen...

Der Fehler in #30 ist Unverträglichkeit der Datentypen. Eine Zahl soll zu einem Text addiert werden...
Bei mir funktioniert es so, Dein Excel ist wohl empfindsamer...

Versuche es so:
- Im Modul aNumDoc, zuoberst bei den Deklarationen ist die Zeile
Dim numDoc
> ergänze diese Zeile so
Dim numDoc As Variant

Gruss Urs

 

Korrektur... da Dein Excel so viel sensibler ist, machen wir es richtig.
Die Variable numDoc wird als Integer und als String benötigt, also >

Im Modul aNumDoc, zuoberst bei den Deklarationen, die Zeile
Dim numDoc (oder "Dim numDoc As Variant", wie vorher empfohlen)
> muss lauten
Dim numDoc As String

Gleiches Modul, unten in Function GetSetDocNumber, die Zeile
newNumDoc = numDoc + 1 (= die Fehlerzeile)
> muss lauten
newNumDoc = CInt(numDoc) + 1
(...CInt = cint, mit i wie integer...)

Gruss Urs