Microsoft Excel

Herbers Excel/VBA-Archiv

PasteSpecial funktioniert nicht | Herbers Excel-Forum


Betrifft: PasteSpecial funktioniert nicht von: Chritian Zumbrunnen
Geschrieben am: 22.11.2009 18:28:48

Ich habe ein altes Makro, welches "FileSearch" enthielt erfolgreich an Excel 2007 angepasst.
Leider hat das Makro einen kleinen Schönheitsfehler: Von den Werten, welche aus den externen Excel-Dateien in die eigene Tabelle kopiert werden, kann keine Summe gebildet werden.
Das Problem lässt sich lösen, wenn statt einem gewöhnlichen Paste ein PasteSpecial Paste:=xlPasteValues verwendet wird.

Offenbar kann ich aber nicht die Worksheet.PasteSpecial-Methode verwenden, sondern muss auf die Range.PasteSpecial-Methode ausweichen.

Ich habe alles mögliche versucht und das Script verschiedentlich angepasst, aber habe es einfach nicht hingekriegt. Ein Problem scheint zu sein, dass beim Wechsel von einer Datei zur anderen, wo einfegügt werden soll, das Clipboard seinen Inhalt verliert... oder so ähnlich.

Hat jemand einen Tipp, wie ich da hinkriegen könnte.

Option Explicit
Option Compare Text
Sub zusammensetzen()

Dim i
Dim myfolder
Dim mytabelle
Dim myrange
Dim mylines
Dim offset
Dim myfilename

Dim fso As Object
Dim fol As Object
Dim fil As Object

'Where you define what to "zusammensetzen"
myfolder = Range("D4")              'Pfad zu den Arbeitsrapporten
mytabelle = Range("D6")             'Name der zu bearbeitenden Tabelle
myrange = Range("D9")               'Bereich in der Tabelle, welcher kopiert werden soll ( _
Feldwert(e)):
mylines = Range(myrange).Rows.Count 'zum Berechnen der Zeile zum nächsten Einfügen
offset = Range("D11")               'Position, bei welcher mit Einfügen begonnen werden soll

i = 0
Set fso = CreateObject("Scripting.FileSystemObject")
Set fol = fso.GetFolder(myfolder)

For Each fil In fol.Files
    i = i + 1
    Workbooks.Open Filename:=fil
    myfilename = Right(fil, Len(fil) - Len(myfolder))
    Sheets(mytabelle).Activate
    Range(myrange).Copy
    ActiveWorkbook.Close False
    ActiveSheet.Paste Destination:=Worksheets("Auswertung").Range(offset).offset(i * mylines -  _
mylines, 1)
    Worksheets("Auswertung").Range(offset).offset(i * mylines - mylines).Value = myfilename
Next fil

  

Betrifft: AW: PasteSpecial funktioniert nicht von: Daniel
Geschrieben am: 22.11.2009 18:43:37

Hi
es kann durchaus sein, daß Excel beim Schließen der Datei seine interne Zwischenablage löscht.
Daraus folgt, daß du die Daten erst einfügen musst, bevor du die Quelldatei schließt.
Dh. .PASTE (bzw .PASTESPECIAL) muss vor ActiveWorkbook.Close erfolgen.
allerdings musst du dann bei .PASTE vollständig referenzieren, da Workbook und Sheet mit angeben.


Gruß, Daniel


  

Betrifft: AW: PasteSpecial funktioniert nicht von: Chritian Zumbrunnen
Geschrieben am: 22.11.2009 19:40:04

Danke für den enorm schnellen Hinweis!

Wie kann ich den auf die Arbeitsmappe in welcher das Makro enthalten ist zugreifen?

DieseArbeitsmappe.Worksheets("Auswertung").Range(offset).offset(i * mylines - mylines, 1). _
PasteSpecial Paste:=xlPasteValues

funktioniert nicht.

Mit ActiveWorkbook.Close False dachte ich, sollte verhindert werden, dass die Zwischeablage geleert wird, aber offenbar funktioniert das nicht. Es genügt das Arbeitsmappe zu wechseln, um die Daten zu verlieren.

Falls es wirklich das wäre. Tatsächlich kann ich aber nach der Fehlermeldung auf die Arbeitsmappe wechseln eine Position selektieren und mit Ctrl (Strg) + V den aktuellen und richtigen Inhalt vom Clipboard einfügen.


  

Betrifft: AW: ThisWorkbook von: Daniel
Geschrieben am: 22.11.2009 21:18:27

referenziert die Arbeitsmappe, in der der Code steht.

Gruß, Daniel


  

Betrifft: AW: ThisWorkbook von: Chritian Zumbrunnen
Geschrieben am: 22.11.2009 22:39:24

Danke!

ActiveWorkbook.Close False macht wohl genau das Gegenteil von dem was ich erwartet habe:
Es schliesst die Aktive Arbeitsmappe

Ich habe den Code hingekriegt.
Nun wäre es noch schön am Schluss direkt die Summe auszugeben.

Das habe ich versucht mit: .FormulaR1C1 = "=SUM(R[-i]C:R[-1]C)
aber dieser Teil des Makros führt nur zu einer Fehlermeldung: Anwendungs- oder objektdefinierter Fehler.

Naja. Ich werde mich vielleicht später noch einmal damit auseinandersetzen. Jetzt ist Zeit zum Schlafen.

Hier noch der funktionierende Code (ausser den letzten 3 Zeilen):

Hinweise, wie ich die Summe hinkriegen kann oder über anderweitige Verbesserungen nehme ich dankbar entgegen.

Option Explicit
Option Compare Text
Sub zusammensetzen()

Dim i
Dim myfolder
Dim mytabelle
Dim myrange
Dim mylines
Dim offset
Dim myfilename

Dim fso As Object
Dim fol As Object
Dim fil As Object

'Where you define what to "zusammensetzen"
myfolder = Range("D4")              'Pfad zu den Arbeitsrapporten
mytabelle = Range("D6")             'Name der zu bearbeitenden Tabelle
myrange = Range("D9")               'Bereich in der Tabelle, welcher kopiert werden soll ( _
Feldwert(e)):
mylines = Range(myrange).Rows.Count 'zum Berechnen der Zeile zum nächsten Einfügen
offset = Range("D11")               'Position, bei welcher mit Einfügen begonnen werden soll

i = 0
Set fso = CreateObject("Scripting.FileSystemObject")
Set fol = fso.GetFolder(myfolder)

For Each fil In fol.Files
    i = i + 1
    Workbooks.Open Filename:=fil
    myfilename = Right(fil, Len(fil) - Len(myfolder))
    Sheets(mytabelle).Activate
    Range(myrange).Copy

    ThisWorkbook.Worksheets("Auswertung").Range(offset).offset(rowOffset:=(i * mylines -  _
mylines), columnOffset:=1).PasteSpecial Paste:=xlPasteValues
    ThisWorkbook.Worksheets("Auswertung").Range(offset).offset(i * mylines - mylines).Value =  _
myfilename
    
    ActiveWorkbook.Close False
    
Next fil
    ThisWorkbook.Worksheets("Auswertung").Activate
    ThisWorkbook.Worksheets("Auswertung").Range(offset).offset(rowOffset:=(i * mylines -  _
mylines + 2), columnOffset:=1).FormulaR1C1 = "=SUM(R[-i]C:R[-1]C)"
    
End Sub




  

Betrifft: AW: ThisWorkbook von: Daniel
Geschrieben am: 23.11.2009 00:20:38

HI

1. es ist in der Regel keine gute Idee, einer Variablen den die gleiche Benennung zu geben wie einer VBA-Funktion (hier "offset")

2. wenn man den Wert einer Variablen in einen String einfügen will, dann muss man das so machen

Dim i as long
dim txt as string
i = 100
txt = "Hier soll jetzt der Wert der Variablen i stehen: " & i & " dh die Zahl einhundert"

dies hätte als Ergebnis Hier soll jetzt der Wert der Variablen i stehen: 100 dh die Zahl _
einhundert


dieser Code
Dim i as long
dim txt as string
i = 100
txt = "Hier soll jetzt der Wert der Variablen i stehen:  i  dh die Zahl einhundert"

hätte als Ergebnis: Hier soll jetzt der Wert der Variablen i stehen: i dh die Zahl einhundert

ich hoffe es ist klar, das die Formel mit dem i nicht funktioniert, sondern nur mit dem Zahlenwert der Variablen i

Gruß, Daniel


  

Betrifft: AW: ThisWorkbook von: Christian Zumbrunnen
Geschrieben am: 23.11.2009 10:31:59

Danke Daniel



zu 1) Du hast vollkommen Recht. Keine Ahnung wie ich auf diesen Namen kam, der ist noch nicht einmal besonders selbsterklärend. Habe ihn geändert.



zu 2.)



Super und einleuchtend.

Ich habe einen String schon versucht mit "." zusammenzusetzen, aber das war PHP Code und nicht VBA.



Nun funktionierts. Cool!



Danke vielmals für dein Antworten. Das hat mir sehr geholfen.