[Excel 2003][VBA] gegevens uit ander bestand benaderen *

[thefal]

Ik probeer vanuit een bestand met VBA code data uit andere Excel bestanden te halen. Tot nu toe doe ik dit zo:

Visual Basic:

OPL = Application.GetOpenFilename _
    (FileFilter:="Excel Files (*.xls), *.xls", Title:="Kies het bestand waaruit gekopieerd moet worden")
Workbooks.Open Filename:=OPL

Workbooks(2).Activate
ActiveSheet.Range("A:A", "IV:IV").Copy Destination:=Workbooks(1).Sheets(1).Range("A1")
ActiveWorkbook.Close

Dit werkt prima, maar staat niet zo netjes dat dit bestand wordt geopend. Dus ik probeer het bestand op de volgende manier te openen:

Visual Basic:

Set objexcel = CreateObject("EXCEL.APPLICATION")
    Set nieuw = objexcel.Workbooks.open(Filename:=OPL)

Op deze manier wordt het bestand wel geopend, maar is niet zichtbaar voor de gebruiker. Vervolgens wil ik dus alle data uit dat bestand kopieren naar het huidige bestand. Dit lukt mij echter niet:

Visual Basic:

'dit werkt:
For i = 1 To 200
    For j = 1 To 26
        ActiveWorkbook.Sheets(1).Cells(i, j) = objexcel.Cells(i, j)
    Next j
Next i

'dit werkt
ActiveWorkbook.Sheets(1).Range("A3") = objexcel.Range("A3")

'dit echter niet
ActiveWorkbook.Sheets(1).Range("A1:B5") = objexcel.Range("A1:B5")

'dit ook niet
objexcel.Range("A1").Copy Destination:=ActiveWorkbook.Sheets(1).Range("A1")

'dit ook niet
objexcel.Range("A:A", "IV:IV").Copy Destination:=Workbooks(1).Sheets(1).Range("A1")

Weet iemand hoe ik dit toch voor elkaar kan krijgen zonder een loop te gebruiken?

[Anoniem: 113297]

de objectvariabele "nieuw" uit het tweede codefragement verwijst naar de net geopende werkmap.

Visual Basic:

nieuw.worksheets(1).range("a1:z200").copy destination:=ActiveWorkbook.Sheets(1).Range("A1")

je kan overigens ook gewoon met formules een verwijziging maken naar een gesloten werkmap of andere technieken gebruiken zoals DAO/ADO of een functie (bv. A VBA Function To Get A Value From A Closed File).

[thefal]

Helaas dit geeft dezelfde foutmelding als bij de andere 3 die niet werken:
Run-time error '1004': Application-defined or object-defined error
De "volledige" code is nu

Visual Basic:

Private Sub CommandButton1_Click()

OPL = Application.GetOpenFilename _
    (FileFilter:="Excel Files (*.xls), *.xls", Title:="Kies het bestand waaruit gekopieerd moet worden")


Set objexcel = CreateObject("EXCEL.APPLICATION")
    Set nieuw = objexcel.Workbooks.Open(Filename:=OPL)
    
    
nieuw.Worksheets(1).Range("a1:z200").Copy Destination:=ActiveWorkbook.Sheets(1).Range("A1")

End Sub

Bij de link die je hebt gestuurd staat een voorbeeld om 1200 waarden uit te lezen. Daar wordt ook een loop gemaakt om al deze waarden uit te lezen en ik wil juist geen loop want dat duurt te lang (Ik heb zo'n 12000 gevulde cellen)

[ Voor 16% gewijzigd door thefal op 23-11-2010 13:34 ]

[Lustucru]

Destination activeworkbook is een knullige, want wat is het activeworkbook? Vervang dat door thisworkbook en je krijgt al een fijnere foutmelding. Helaas kan Excel blijkbaar niet in één regel copiepasten tussen twee werkmappen in verschillende instanties van excel dus moet het eerst naar het klembord en dan naar je huidige bestand.

code:

nieuw.Worksheets(1).Range("a1:z200").Copy
ThisWorkbook.Sheets(1).Paste ThisWorkbook.Sheets(1).Range("A1")

Zou moeten werken.

[ Voor 4% gewijzigd door Lustucru op 23-11-2010 18:47 ]

[thefal]

Daar was ik inderdaad al bang voor, gezien het wel werkt met 1 cel maar niet bij meerdere cellen tegelijk. De oplossing die ik in de tussentijd heb gebruikt is inderdaad eerst kopieren dan een regel verder plakken. Dit is natuurlijk nog steeds niet ideaal, vooral voor mensen die kopieren en plakken vaak gebruiken, die hebben ineens een heel excel-sheet onder hun Ctrl-V staan

BTW als ik activeworkbook vervang door thisworkbook krijg ik nog steeds dezelfde foutmelding Anyway bedankt voor de suggestie;) Mocht iemand toch een andere manier weten dan hoor ik het graag!

Trouwens raar dat het met 1 cel per keer wel makkelijk kan (1e twee voorbeelden in OP) maar dat het met een range van meerdere cellen ineens niet kan (3e voorbeeld) Je zou verwachten als het eerste werkt dat het dan ook werkt met meerdere cellen.

[ Voor 17% gewijzigd door thefal op 24-11-2010 15:18 ]

[Anoniem: 113297]

thefal schreef op woensdag 24 november 2010 @ 15:13:
Dit is natuurlijk nog steeds niet ideaal, vooral voor mensen die kopieren en plakken vaak gebruiken, die hebben ineens een heel excel-sheet onder hun Ctrl-V staan

je kan het klembord wissen door de de cutcopymode-eigenschap van de instantie in kwestie op onwaar te zetten : objexcel.CutCopyMode = False.
nadeel is sowieso wel dat wat de gebruiker eventueel gekopiëerd had verdwenen is.

BTW als ik activeworkbook vervang door thisworkbook krijg ik nog steeds dezelfde foutmelding Anyway bedankt voor de suggestie;) Mocht iemand toch een andere manier weten dan hoor ik het graag!

thisworkbook verwijst naar de werkmap die de code bevat. als de gegevens die gekopiëerd worden uit een andere werkmap komen kan je dit instellen, alvorens je de bronmap opent, met set doelmap=activeworkbook of set doelmap=workbooks("mijnwbnaam.xls")

Trouwens raar dat het met 1 cel per keer wel makkelijk kan (1e twee voorbeelden in OP) maar dat het met een range van meerdere cellen ineens niet kan (3e voorbeeld) Je zou verwachten als het eerste werkt dat het dan ook werkt met meerdere cellen.

een eigenaardigheidje waaraan ik me ook heb laten vangen in m'n eerste antwoord, wat me eraan herinnert altijd eerst te testen voor te posten.
als het enkel het ophalen van waardes betreft (geen opmaak, formules) kan je ook DAO of ADO gebruiken. Dit benadert de gesloten werkmap als een database.

vergeet tenslotte ook niet de onzichtbare excelinstantie af te sluiten (objexcel.close false '(niet getest ))