Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: ADO in geschlossene Arbeitsmappe schreiben

ADO in geschlossene Arbeitsmappe schreiben
01.09.2018 17:45:06
Timo
Hallo zusammen,
ich war auf der Suche nach einer Möglichkeit, Werte in eine geschlossene Arbeitsmappe zu schreiben und bin dabei auf die "ADO" Möglichkeit mit Excel gestoßen.
Mit folgendem Code schreibe ich eine Seriennummer fortlaufend in Spalte "A" einer anderen Excel-Datei:
Public Sub ADO_Write()
Dim objConnection As Object
Dim strConnection As String
Dim strWorkbook As String, strWorksheet As String
Dim Seriennummer As String
strWorkbook = "C:\Datenbank\Mappe1.xls"
strWorksheet = "Tabelle2"
Seriennummer = ThisWorkbook.Worksheets("Tabelle3").Range("A1").Value
Set objConnection = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=0"""
Call objConnection.Open(strConnection)
Call objConnection.Execute("INSERT INTO [Tabelle2$A1:A10]" & _
"VALUES ('" & Seriennummer & "')")
objConnection.Close
Set objConnection = Nothing
End Sub
Nun möchte ich aber die Seriennummer nicht fortlaufend untereinander in Spalte "A" schreiben sondern möchte die aktuelle Seriennumer immer in "A1" schreiben.
Deshalb habe ich folgende Codezeile geändert:
Call objConnection.Execute("INSERT INTO [Tabelle2$A1:A1]" & _
"VALUES ('" & Seriennummer & "')"
Leider funktioniert das nicht wie gewünscht, der Wert in "A1" wird nicht überschrieben sondern es erscheint eine Fehlermeldung, dass der benannte Bereich nicht erweitert werden kann.
Ich stehe hier total auf dem Schlauch und hoffe auf eure Hilfe.
Vielen Dank und viele Grüße
Timo
Anzeige

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: ADO in geschlossene Arbeitsmappe schreiben
01.09.2018 18:40:42
Sepp
Hallo Timo,
zuerst einmal, die Datei wird trotzdem geöffnet! Du siehst es nur nicht.
In A1 schreiben geht nicht, das SQL immer nach einer Tabelle verlangt und bei einer Tabelle stehen in der ersten Zeile Überschriften. Schreibe in A1 also eine beliebige Überschrift und versuche folgenden Code. Die Seriennummer wird inA2 geschrieben.
Sub updateData()
  Dim ExcelTable As Object
  Dim Path As String, Table As String, SourceRange As String
  Dim SQL As String, Con As String
  Dim Seriennummer As String
    
  Path = "C:\Datenbank\Mappe1.xls"  ' Dateiname - Anpassen! 
  Table = "Tabelle2"                ' Tabellenname - Anpassen 
  SourceRange = "A1:A2"             ' Bereich - Anpassen! 
  
  Seriennummer = ThisWorkbook.Worksheets("Tabelle3").Range("A1").Value

  SQL = "select * from [" & Table & "$" & SourceRange & "]"

  If Right(Path, 3) = "xls" Then
    Con = "Provider=Microsoft.Jet.OLEDB.4.0;" _
      & "Extended Properties=Excel 8.0;" _
      & "Data Source=" & Path & ";"
  ElseIf Right(Path, 4) = "xlsx" Or Right(Path, 4) = "xlsm" Then
    Con = "Provider=Microsoft.ACE.OLEDB.12.0;" _
      & "Extended Properties=""Excel 12.0;HDR=YES"";" _
      & "Data Source=" & Path & ";"
  End If

  Set ExcelTable = CreateObject("ADODB.Recordset")

  ExcelTable.Open SQL, Con, 3, 3

  With ExcelTable
    .Movefirst
    .Fields(0) = Seriennummer
    .Update
    .Close
  End With
  
  Set ExcelTable = Nothing
End Sub

VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media

Code erstellt und getestet in Office 16 - mit VBAHTML 12.6.0


 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: ADO in geschlossene Arbeitsmappe schreiben
01.09.2018 19:08:16
Timo
Hallo Sepp,
herzlichen Dank für deine Erklärung und natürlich für den fertigen Code.
Es funktioniert alles so, wie ich es mir vorgestellt habe.
Viele Grüße Timo
AW: ADO in geschlossene Arbeitsmappe schreiben
01.09.2018 18:41:09
Oberschlumpf
Hi Timo
Wieso MUSS denn die Datei unbedingt geschlossen sein?
Mit...

Application.ScreenUpdate = False
WorkBooks("bekannterPfad+Name").Open
With ActiveWorkbook
.Sheets("bekannteTabelle").Range("bekannteZelle(n)").Value = Zielwert(e)
.Close True
End With
Application.ScreenUpdate = True

...ist es ein Leichtes, die Datei zu öffnen, Daten in Zellen eintragen + Datei mit Speichern schließen.
Alles automatisiert + der Anwender bekommt in Excel nix davon mit.
Ciao
Thorsten
Anzeige
AW: ADO in geschlossene Arbeitsmappe schreiben
01.09.2018 19:13:00
Timo
Hi Thorsten,
ich habe ein VBA-Projekt erstellt, was mehrere Anwender auf ihrem PC verwenden.
Es gibt aber eine gemeinsame Datei, in die Werte geschrieben werden.
Deine beschriebene Methode ist mir bereits bekannt.
Über die "ADO-Methode" läuft die ganze Sache aber bedeutend schneller ab.
Viele Grüße
Timo
Anzeige
alles klar, danke für die Erklärung...owT
01.09.2018 19:41:54
Oberschlumpf
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Anzeige
Anzeige

Infobox / Tutorial

ADO in geschlossene Arbeitsmappe schreiben


Schritt-für-Schritt-Anleitung

Um mit ADO Excel Daten in eine geschlossene Datei zu schreiben, kannst du den folgenden VBA-Code verwenden. Dieser Code öffnet die geschlossene Arbeitsmappe im Hintergrund und schreibt eine Seriennummer in die vorgegebene Zelle:

  1. Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.

  2. Füge ein neues Modul hinzu (Einfügen > Modul).

  3. Kopiere den nachstehenden Code in das Modul:

    Public Sub ADO_Write()
        Dim objConnection As Object
        Dim strConnection As String
        Dim strWorkbook As String, strWorksheet As String
        Dim Seriennummer As String
    
        strWorkbook = "C:\Datenbank\Mappe1.xls" ' Pfad zur Zielarbeitsmappe
        strWorksheet = "Tabelle2" ' Name des Arbeitsblattes
        Seriennummer = ThisWorkbook.Worksheets("Tabelle3").Range("A1").Value ' Wert aus der aktuellen Arbeitsmappe
    
        Set objConnection = CreateObject("ADODB.Connection")
        strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & strWorkbook & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=0"""
    
        Call objConnection.Open(strConnection)
        Call objConnection.Execute("INSERT INTO [" & strWorksheet & "$A2:A2] VALUES ('" & Seriennummer & "')")
    
        objConnection.Close
        Set objConnection = Nothing
    End Sub
  4. Passe den strWorkbook-Pfad und den strWorksheet-Namen nach Bedarf an.

  5. Führe das Makro aus, um die Seriennummer in die geschlossene Arbeitsmappe zu schreiben.


Häufige Fehler und Lösungen

  • Fehler: "Der benannte Bereich kann nicht erweitert werden"

    • Lösung: Stelle sicher, dass der SQL-Befehl auf einen Bereich verweist, der mindestens eine Kopfzeile hat. Wenn du in A1 schreiben möchtest, musst du sicherstellen, dass die erste Zeile Überschriften enthält.
  • Fehler: Verbindung zur Datenquelle kann nicht hergestellt werden

    • Lösung: Überprüfe den Pfad zur Arbeitsmappe und stelle sicher, dass die Datei im angegebenen Verzeichnis existiert. Achte auch darauf, dass die entsprechende Version des Microsoft Access Database Engine installiert ist.

Alternative Methoden

Falls du nicht mit ADO arbeiten möchtest, kannst du auch die folgende Methode verwenden, um Excel VBA Daten in eine geschlossene Datei zu schreiben:

Application.ScreenUpdate = False
Workbooks.Open "C:\Datenbank\Mappe1.xls"
With ActiveWorkbook
    .Sheets("Tabelle2").Range("A2").Value = ThisWorkbook.Worksheets("Tabelle3").Range("A1").Value
    .Close SaveChanges:=True
End With
Application.ScreenUpdate = True

Diese Methode öffnet die Datei, schreibt die Daten und schließt sie wieder, ohne dass der Benutzer etwas sieht.


Praktische Beispiele

Hier ist ein Beispiel, wie du den ADO-Code anpassen kannst, um mehrere Werte in verschiedene Zellen zu schreiben:

Public Sub ADO_Write_Multiple()
    Dim objConnection As Object
    Dim strConnection As String
    Dim strWorkbook As String, strWorksheet As String
    Dim Seriennummer As String
    Dim i As Integer

    strWorkbook = "C:\Datenbank\Mappe1.xls"
    strWorksheet = "Tabelle2"

    Set objConnection = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & strWorkbook & ";" & _
                    "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=0"""

    Call objConnection.Open(strConnection)

    For i = 1 To 10
        Seriennummer = ThisWorkbook.Worksheets("Tabelle3").Cells(i, 1).Value
        Call objConnection.Execute("INSERT INTO [" & strWorksheet & "$A" & (i + 1) & ":A" & (i + 1) & "] VALUES ('" & Seriennummer & "')")
    Next i

    objConnection.Close
    Set objConnection = Nothing
End Sub

Diese Methode schreibst die Werte von A1 bis A10 in die geschlossene Datei.


Tipps für Profis

  • Vermeide es, die Datei unnötig oft zu öffnen, da dies die Performance beeinträchtigen kann.
  • Nutze Transaktionen, wenn du mehrere Schreibvorgänge in einer einzigen Sitzung durchführst.
  • Setze die ScreenUpdating-Eigenschaft auf False, um das Flackern des Bildschirms zu vermeiden und die Ausführung zu beschleunigen.

FAQ: Häufige Fragen

1. Muss die Datei unbedingt geschlossen sein? Ja, die Verwendung von ADO funktioniert am besten mit geschlossenen Arbeitsmappen, um die Performance zu verbessern und Konflikte zu vermeiden.

2. Welche Excel-Version benötige ich für ADO? ADO ist in den meisten aktuellen Versionen von Excel verfügbar. Stelle sicher, dass du die richtige OLEDB-Provider-Version installierst, um mit der jeweiligen Excel-Datei zu arbeiten (z.B. .xls, .xlsx).

3. Wie kann ich Daten aus einer geschlossenen Datei lesen? Du kannst den ADO-Befehl SELECT verwenden, um Daten aus einer geschlossenen Arbeitsmappe zu lesen, ähnlich wie beim Schreiben von Daten.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige