Zu einer Artikelnr. den Preis mit Client Access an Excel übergeben

[vohr_de]

Die Dateiübergabe mit Client Access klappt wunderbar.
In Excel habe ich es auch schon mit Hilfe diese Forums geschafft
einen Artikel mit Preis aus einer Datei zu holen.
Meine Frage nun:
In Excel will ich eine Artikelnummer eingeben und im nächsten Feld soll zu
dieser Artikelnummer der Preis aus der AS400 eingeblendet werden.
Das sollte doch möglich sein.
Fragt sich nur wie?
Kann mir einer Helfen?

[arnoldma]

Funktioniert Problemlos mit Excel Funktion SVerweis.

[vohr_de]

Funktioniert Problemlos mit Excel Funktion SVerweis.

Hallo,
habe mir die Funktion angeschaut und mit Google gesucht jedoch weiß ich nicht wie ich den Verweis auf die AS400 Datei bekomme.
Muß ich erst eine Abfrage in einem neuen Excel-Datenblatt erstellen oder geht das auch direkt?

Grüße Hubert

[arnoldma]

Habe ein Excel-Beispiel auf www.arnisoft.at/upload gestellt.

Die iSeries Daten müssen in Tabelle 1 übertragen werden.
In diesem Beispiel gehe ich davon aus, dass diese bereits vorhanden sind.
Man kann das in VBA erledigen.

Gruss
Markus
P.S. Ich hoffe, der Link ist im Forum erlaubt, sonst bitte um Meldung

[vohr_de]

Habe ein Excel-Beispiel auf www.arnisoft.at/upload gestellt.

Die iSeries Daten müssen in Tabelle 1 übertragen werden.
In diesem Beispiel gehe ich davon aus, dass diese bereits vorhanden sind.
Man kann das in VBA erledigen.

Gruss
Markus
P.S. Ich hoffe, der Link ist im Forum erlaubt, sonst bitte um Meldung

Vielen Dank,

jedoch möchte ich die Daten nicht kompl. nach Excel einlesen sonder es soll nur der
jeweilige Artikel online und tagesaktuell eingelesen werden.
Das müßte doch mit einer SQL-Abfrage gehen?

[Fuerchau]

Über MS-Query kannst du in den Kriterien Parameter definieren (im SQL-Modus mit "?").
Bei der Ausführung fragt MS-Query nach den Werten.
Gib die Abfrage dann ruhig an Excel zurück.
Über die Abfrage-Eigenschaften kannst du dann den Parametern dann gezielt einzelne Zellen zuweisen, so dass bei Änderung der Zelle die Abfrage ausgeführt wird.

Nachteil:
Du kannst einen Parameter nur 1 Zelle zuweisen !
Wenn du also mehrere Zellen hast, in die die Artikel-Nr. eingegeben werden kann, benötigst du für jede Zelle eine eigene Abfrage.

Besser wäre es, hier eine allgemeine Funktion (VBA-Modul) zu schreiben, der du die Artikel-Nr. (als Zelle) übergibst und den Preis mittels Abfrage (ADO) selber abfragst.
Das Recordset kann statisch geöffnet bleiben, ein Command-Objekt mit Parameter zuordnen und dann jeweils mittels Refresh die Abfrage wiederholen.

[vohr_de]

Leider verstehe ich eigentlich garnichts von VBA, habe früher jedoch viel mit Basic programmiert.

Folgende Abfrage habe ich in einem anderen Forum gefunden und bereits angepasst mit den entsprechenden Datainamen.
Wenn ich das richtig verstehe gebe ich diesen Code in VBA ein.
Allerdings stoppt er schon in der ersten Zeile beim Debuggen.
Wie muss ich diesen Code eingeben? und warum läuft er nicht?
Ich habe ein Feld in Excel mit dem Namen mat_nr vergeben.

With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= AS400;", Destination:=Cells(i, 14))
.CommandText = Array("SELECT IDTLPD.DTLPLE" & Chr(13) & "" & Chr(10) & "FROM LDBSVR1.IBOL01AD.IDTLPD IDTLPD" & Chr(13) & "" & Chr(10) & "WHERE IDTLPD.DTLNR= '" & mat_nr & "')")
.Name = "Abfrage von AS400"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Besten Dank

[Fuerchau]

SO einfach ist es nun auch wieder nicht.
Du musst im VBA-Editor dem Excel über "Extras->Verweise" die Komponente "Microsoft ActiveX Dataaccess 2.7" oder höher zuordnen.

Dann definierst du

public function GetPreis(Artikel as string) as double

static myConnect as new ADODB.Connection
static myCommand as new ADODB.Command
static myRecord as new ADODB.Recordset

with myConnect
if .state = adstateclosed then
.connectionstring="DSN=AS400"
.open
endif
end with
with myCommand
if .activeconnection is nothing then
.commandtext="SELECT IDTLPD.DTLPLE FROM LDBSVR1.IBOL01AD.IDTLPD IDTLPD WHERE IDTLPD.DTLNR=?"
set .activeconnection=myConnect
endif
end with
myCommand(0) = Artikel
with myRecord
if .state=adstateclosed then
.open mycommand
else
.requery
endif
if .eof = false then
GetPreis = myRecord(0)
endif
end with
end function

Dieser Code erhebt jetzt keinen Anspruch auf Vollständigkeit.
Durch die Static-Definition der Variablen bleibt die Verbindung erhalten, auch wenn die Funktion verlassen wird.

[vohr_de]

Das holen der Daten beansprucht zwar ein wenig Zeit. Ist jedoch immer noch besser als abtippen.
Abtippen von einem Bildschirm zum Nächsten wird zwar noch oft gemacht, sollte jedoch im heutigen Computerzeitalter nicht mehr notwendig sein.

Danke nochmals.

[Fuerchau]

Wenn du die Funktion noch in ein .XLA packst und über den AddIn-Manager installierst stehen sie automatisch immer zur Verfügung.
Natürlich können über diesen Weg mehrere Funktionen bereitgestellt werden.
Du solltest dir überlegen, als 2. Parameter den Feldnamen zu verwenden so dass das ganze dynamischer wird.

Von Vorteil ist dann, die Verbindung (myConnect) global zu definieren, und von jeder Funktion darauf zu referieren. Ggf. ist auch zu prüfen, ob der aktuelle Datensatz bereits der gewünschte ist, um sich das Requery zu sparen.

Dann geht das ganze auch ziemlich schnell.

Was die Antwortzeit bei dir angeht, prüfe doch per Debug (Einzelschritt) ob tatsächlich die Verbindung und das Recordset geöffnet bleiben.

[vohr_de]

Hallo,

frührer musste die Komponente "Microsoft ActiveX Dataaccess 2.7" eingefügt werden.
Welche Komponente ist es nun beim Office 2007?
Das ist wahrscheinlich die Ursache das das Makro nicht mehr funktioniert.

Ansosnten funktioniert die Datenübernahme sehr gut.

[Fuerchau]

Neu ist die Version 2.8 (set ca. 2-3 Jahren), stelle den Verweis einfach um.