Office: (Office 2003) Aus Access Excel VBA ausführen

rebel123 · 24. August 2010

Hallo,

ich habe in Excel mittels VBA den benutzten Bereich ermittelt und einem Namen zugewiesen. Folgendermaßen:

Code:

Sub bereich_auswählen()
ActiveSheet.Range(Cells(1, 1), Cells(Cells(rows.Count, 1).End(xlUp).row," & _ SpNr)).Name = "Bereich"
End Sub

Function SpNr() As Integer
SpNr = Application.Match("Status", Range("A1:IV1"), 0)
End Function
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Jetzt muss ich diesen Bereich beim Import von Excel nach Access benutzen und dafür vorher aber den Excel VBA aufrufen (ich kann es nicht vorher händisch machen für die Datei da es sich um mehr als 100 Dateien handelt die Importiert werden sollen --> daher mit Excel VBA Bereich festlegen).

Jetzt habe ich im Netz schon folgendes gefunden um aus Access Excel aufzurufen:

Code:

Sub ExcelAUSaccess()
Dim oXcl As New Excel.Application, ws As Excel.Worksheet
With oXcl
' ab hier ist reines Excel
.Workbooks.Open filename:="D:\mein_Pfad\excel.xls"
.Visible = True
' hier kannst du deine Excelbefehle unterbringen
.ActiveWorkbook.save
.Quit
End With
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Baue ich nun aber meine Befehle ein:

Code:

Sub ExcelAUSaccess()

Dim oXcl As New Excel.Application, ws As Excel.Worksheet
Dim SpNr as Integer
With oXcl
' ab hier ist reines Excel
.Workbooks.Open filename:="D:\mein_Pfad\excel.xls"
.Visible = True
' hier kannst du deine Excelbefehle unterbringen
SpNr = Application.Match("Status", Range("A1:IV1"), 0)
.ActiveSheet.Range("A1", SpNr).Name = "Bereich"
.ActiveWorkbook.save
.Quit
End With
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Bekomme ich eine Fehlermeldung. Daraus schließe ich, dass ich es nicht so einbauen kann.
Kann mir vlt jemand weiterhelfen und zeigen wie ich meinen Code aus Excel dort einbauen kann dass es funktioniert?

Ein GROßES Dankeschön!

:)

blackoutNO · 25. August 2010

Hallo,

ich bin mir zwar grad nicht so sicher, da die Fehlernummer+Beschreibung hilfreich bei der Problemlösung wären, aber auf den ersten Blick fällt mir folgendes auf:

Code:

Sub ExcelAUSaccess()

Dim oXcl As New Excel.Application, ws As Excel.Worksheet
Dim SpNr as Integer

With oXcl

' ab hier ist reines Excel
.Workbooks.Open filename:="D:\mein_Pfad\excel.xls"
.Visible = True
' hier kannst du deine Excelbefehle unterbringen
SpNr = .Match("Status", Range("A1:IV1"), 0)
.ActiveSheet.Range("A1", SpNr).Name = "Bereich"
.ActiveWorkbook.save
.Quit

End With
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Du hast Application.Match aus excel und wenn du es aus Access aufrusfst, dann würde Application sich auf Access beziehen, also muss das geändert werden. Und zwar mit Oxcl

fhentzsc · 25. August 2010

SpNr = Application.Match("Status", Range("A1:IV1"), 0)

Hier fehlt vor Application ein Punkt.

Gruß

Frank

rebel123 · 25. August 2010

Ja stimmt, da habe ich wohl den Punkt vergessen beim einbauen in Access. Danke! Jetzt funktionierts *froi*

rebel123 · 26. August 2010

Funktioniert zwar aber irgendwie habe ich im Taskmanager immer nach Beenden des Sub noch den EXCEL Prozess (1. Fehler). Das ausführen funktioniert auch nicht immer, ab und zu bricht er mit der Fehlermeldung ab
"'462' Der Remote-Server Computer existiert nicht oder ist nicht verfügbar" (2. Fehler bei Zeile: SpNr =).

Mein Code:
Code:

Sub myTest()

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim SpNr As Integer

With xlApp
' ab hier ist reines Excel
.Workbooks.Open filename:="D:\mein_pfad\Excel.xls"
.Visible = True
' hier kannst du deine Excelbefehle unterbringen
SpNr = .Application.Match("Status", Worksheets("report").Range("A1:IV1"), 0)

MsgBox SpNr 'eingebaut um zu teste ob er bis hierhin läuft
.ActiveSheet.Range(Cells(1, 1), Cells(Cells(rows.Count, 1).End(xlUp).row, SpNr)).Name = "Bereich"
Set objWS = Nothing
.ActiveWorkbook.save
.ActiveWorkbook.Close
Set xlSheet = Nothing
Set xlWB = Nothing
.Quit
Set xlApp = Nothing
End With

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Setze ich die = Nothing nicht richtig oder warum bleibt der prozess da?
Hoffe jemand weis da weiter *Smilie

fhentzsc · 26. August 2010

probiers mal damit:

Code:

Dim xlApp As Object, xlBook As Object, xlSheet As Object ' Excel Objekte
Dim blnExcelWasOpen As Boolean
Dim strExcelFile As String
Dim SpNr As Integer

strExcelFile = "D:\mein_pfad\Excel.xls"

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
blnExcelWasOpen = False
xlApp.Visible = False
Else
blnExcelWasOpen = True
End If
On Error GoTo 0

Set xlBook = xlApp.Workbooks.Open(strExcelFile)
Set xlSheet = xlBook.Worksheets.Add

'... mach irgendwas ...
SpNr = xlApp.Application.Match("Status", xlBook.Worksheets("report").Range("A1:IV1"), 0)
MsgBox SpNr 'eingebaut um zu teste ob er bis hierhin läuft
xlSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, SpNr)).Name = "Bereich"

Set xlSheet = Nothing

xlBook.Close False
Set xlBook = Nothing
If Not blnExcelWasOpen Then
'evtl noch xlApp.visible=true
xlApp.Quit
End If
Set xlApp = Nothing
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Es kann sein, dass Du in dieser Anweisung:
xlSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, SpNr)).Name = "Bereich"

noch weiter den Cells und Rows das Objekt xlSheet. voranstellen musst,
aber vielleicht gehts ja auch.

Gruß

Frank

rebel123 · 26. August 2010

Huhu, habe noch etwas angepasst in deinem Code (Roteintrag)

Code:

Sub access()
Dim xlApp As Object, xlBook As Object, xlSheet As Object ' Excel Objekte
Dim blnExcelWasOpen As Boolean
Dim strExcelFile As String
Dim SpNr As Integer

strExcelFile = "D:\mein_pfad\Excel.xls"

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
blnExcelWasOpen = False
xlApp.Visible = False
Else
blnExcelWasOpen = True
End If
On Error GoTo 0

Set xlBook = xlApp.Workbooks.Open(strExcelFile)
Set xlSheet = xlBook.Worksheets("report") 'er soll kein neues öffnen sonder im "report" arbeiten

'... mach irgendwas ...
SpNr = xlApp.Application.Match("Status", xlBook.Worksheets("report").Range("A1:IV1"), 0)
MsgBox SpNr 'eingebaut um zu teste ob er bis hierhin läuft
xlSheet.Range(Cells(1, 1), Cells(Cells(rows.Count, 1).End(xlUp).row, SpNr)).Name = "Bereich"
xlBook.save
Set xlSheet = Nothing

xlBook.Close False
Set xlBook = Nothing
If Not blnExcelWasOpen Then
'evtl noch xlApp.Visible = True
xlApp.Quit
End If
Set xlApp = Nothing

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Folgende Schlussfolgerungen: die Zeile "xlSheet.Range(Cells(1, 1), Cells(Cells(rows.Count, 1).End(xlUp).row, SpNr)).Name = "Bereich"" macht die Probleme. Es scheint, dass sie schon ein Prozess am laufen braucht um zu funktionieren, denn wenn ich die Sub starte und kein Prozess läuft dann bekomme ich eine fehlermeldung. Ist noch ein Prozess nach dem schließen offen dann nicht. und wenn ich die Zeile auskommentiere funktioniert es einwandfrei. hab alternativ mal "xlSheet.Range("A1", "B2").Select" eingebaut und dann funktioniert es (obwohl das ist nicht ganz das was ich will).
Da muss ich jetzt nochmal gucken ob es anders lösbar ist.
ansonsten vielen dank!

fhentzsc · 26. August 2010

Probier mal eins - geh in die Verweise und nimm das Häckchen bei Excel raus.

Ich habe den Verdacht (vorher bereits geäußert), dass du vor deine Cells(...) und Rows(...) auch das Objekt xlSheet. schreiben musst, vielleicht geht es ja dann.

Gruß

Frank

rebel123 · 26. August 2010

hey,
neuer tag neues glück.
habe es nun soweit abgeändert, dass es funktioniert *Smilie
jetzt gehts weiter!
vielen dank für die hilfe!

fhentzsc · 26. August 2010

was hast du denn abgeändert, dass es funktioniert?

Viellicht kann ich ja was dazu lernen? *mrcool

Gruß

Frank

raist10 · 26. August 2010

\@ rebel123

Wenn Du planst umfangreichere Aktionen in Excel auszuführen und die Exceldatei bleibt immer die gleiche Datei (z.B. eine Vorlage-Datei in die Du Daten zur Weiterbearbeitung einfüllst) dann könnte es vllt auch interessant sein den Code zur Ausführung direkt in Excel zu schreiben und über die Run - Methode den aus Access ausführen zu lassen.

Gruß

Rainer

rebel123 · 29. August 2010

Hallo,
@raist10: leider ist ist es nicht immer die gleiche excel datei (es sind 140 verschiedene insgesamt) und somit geht es nicht so einfach. aber danke für den tipp.

@Frank:
hab das ermitteln der spalten- und zeilenanzahl wie folgt gelöst:

'ermitteln der Spalten- und Zeilenanzahl

ColNr = xlApp.Application.Match("Excel-Status", xlBook.Worksheets("report").Range("A1:IV1"), 0)
RowNr = xlSheet.Cells((xlSheet.rows.Count), 1).End(xlUp).row
'Definition des Bereiches "ImportRange"
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(RowNr, ColNr)).Name = "ImportRange"
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

RowNr und ColNr sind jeweils vom Typ Long.

Damit funktioniert es einwandfrei und ist übersichtlich!

Office: (Office 2003) Aus Access Excel VBA ausführen

Aus Access Excel VBA ausführen

Aus Access Excel VBA ausführen

Aus Access Excel VBA ausführen

Aus Access Excel VBA ausführen

Aus Access Excel VBA ausführen - Similar Threads - Access Excel VBA

Doppelkopf Spielplan (jeder gegen jeden) VBA / Excel / Access

VBA Frage Access / Excel

hyperlink von Excel nach MS Access per VBA kopieren

Access Formular per VBA aus Excel öffnen

über VBA Access erste leere Zeile im Excel finden

Access Tabelle Per VBA code ins excel exportieren

Excel-Tabelle per VBA auslesen und in Access Tabelle übertragen