Excel Database

[vvermeer]

Beste mede Tweakers,

Afgelopen 2 maanden is er met collega's hard gewerkt aan het maken van een database. In deze database zijn 637 losse Excel bestanden handmatig samengevoegd d.m.v. kopiëren plakken. Echter nu het project bijna aan zijn einde is gekomen bleken er enkele cruciale fouten in de database te zitten. Dit heeft tot gevolg dat de gehele database opnieuw gemaakt zal moeten worden.

Vanwege de tijdsdruk lijkt dit vrijwel onmogelijk en dus ben ik opzoek naar alternatieven om de database opnieuw te kunnen maken i.p.v. handmatig te kopiëren en plakken. Hiervoor heb ik gezocht in het forum en op het internet waaruit is gebleken dat het mogelijk schijnt te zijn dit via VBA te doen. Hiervoor heb ik dan ook een code gevonden die tevergeefs maar niet werkt.

Wat er exact moet gebeuren is dat er uit 4 tabbladen per Excel alle informatie gekopieerd wordt en deze in 1 centrale database komt, waar deze zelfde 4 tabbladen in zitten. Is er iemand die ons hiermee zou kunnen helpen! Alle hulp is meer dan welkom. Op de manier zoals ik het nu doe: ='C:\temp\[naam.xlsm]DatabaseElement'!$A$4:$L$121 ontstaan er teveel foutmeldingen en kan het niet snel genoeg uitgevoerd worden.

Ik hoop dat het middels dit forum opgelost kan worden. Eventueel zijn wij bereid om tegen betaling gebruik te maken van uw kennis om dit zo snel als mogelijk op te lossen (Teamviewer).

Vriendelijke groet,
Vincent Vermeer

[xleeuwx]

Is elk excel bestand zelfde kwa structuur?

Mocht dit zo zijn is het natuurlijk makkelijk te automatiseren. Eventueel door een Ontwikkelaar in te huren (8 uur werk (maken + testen))

[Speedmaster]

Waarom geen Acces database met gekoppelde tabellen naar de excel files, kwestie van alle data naar de Access database kopiëren en vervolgens ut laten poepen in het gewenste excel formaat.

[Breezers]

Om wat voor database gaat het hier om ? Een database met tabbladen zegt mij niets, tenzij je het over een frontend applicatie hebt ?

[EvilWhiteDragon]

Om je probleem beter te begrijpen zou je op zijn minst de tabelstructuur van je (huidige) database moeten posten. Verder is het handig als je verteld wat voor databasesysteem gebruikt wordt. Ik gok dat ook dit Excel is. (waarom geen Access of echte DB?).

[vvermeer]

Elk Excelbestand is het zelfde wat betreft de structuur. Echter het ene bestand bevat 120 regels informatie per tabblad de ander slechts 1. Het varieert dus in grote waardoor de selectie veel witregels en foutmeldingen bevat. Met Microsoft Acces zijn wij niet erg bekend ik zal daar eerst eens naar kijken. Alvast bedankt tot zover!

De database is een leeg Excel bestand die de zelfde tabbladen heeft als alle losse bestanden zeg maar 1 2 3 4 . Hierin moet de zelfde informatie komen maar dan van alle bestanden onder elkaar.

Het is de eerste keer waarbij wij een project doen met deze omvang het werken met deze systemen is ons momenteel nog vreemd

[ Voor 11% gewijzigd door vvermeer op 26-08-2015 17:02 ]

[xleeuwx]

vvermeer schreef op woensdag 26 augustus 2015 @ 17:00:
Elk Excelbestand is het zelfde wat betreft de structuur. Echter het ene bestand bevat 120 regels informatie per tabblad de ander slechts 1. Het varieert dus in grote waardoor de selectie veel witregels en foutmeldingen bevat. Met Mircosoft Acces zijn wij niet erg bekend ik zal daar eerst eens naar kijken. Alavst bedankt tot zover!

De database is een leeg Excel bestand die de zelfde tabbladen heeft als alle losse bestanden zeg maar 1 2 3 4 . Hierin moet de zelfde informatie komen maar dan van alle bestanden onder elkaar.

Wat ik hieruit gebruik is er dus geen Centrale Database maar wil je gewoon Excel file samenvoegen

[vvermeer]

Ja dat klopt. We moeten de Excel files samenvoegen.

[Belindo]

In plaats van in je VBA code de range te hardcoden, kun je dit ook variabel doen. Er van uitgaande dat je ten minste één kolom hebt zonder lege rijen.

Pseudo code:

Visual Basic:

NumRows = Application.Worksheetfunction.CountA(Range("A:A"))

Range("A4:L" & NumRows & "").Select

[sjroorda]

Statische of dynamische data (formules)? Als het statische data is kan je misschien een shortcut nemen:
* sla elk bestand/tabblad op als CSV (dat is vast wel ergens te automatiseren)
* met een text-merge-tooltje alle bestanden achter elkaar plakken en als nieuw CSV-bestand opslaan
* nieuwe bestand openen in Excel

Misschien niet de netste manier, maar mits er een tooltje bestaat om meerdere XLS-bestanden in batch (en incl. alle tabbladen) naar CSV te exporteren misschien wel de snelste manier.

Update: tooltje lijkt het probleem niet te zijn

[ Voor 15% gewijzigd door sjroorda op 26-08-2015 17:13 ]

[vvermeer]

Beste Belindo bedankt voor uw tip. Echter met VBA lukt het ons nog niet om een werkende code te vinden die van toepassing is op onze database. Hier zijn we momenteel naar opzoek of naar een oplossing met Access. In Access lukt het momenteel tot op een bepaalde hoogte. Echter hiervoor moeten alle macro's uitgeschakeld worden. Is hier een tool voor?

[MAX3400]

vvermeer schreef op woensdag 26 augustus 2015 @ 17:13:
Is hier een tool voor?

Yup, dat is ook een stukje code wat je kan aftrappen per blad of per instance van de applicatie waardoor zaken worden uitgeschakeld of ingeschakeld.

Ben alleen benieuwd waarom Access gekozen is als oplossing; het staat nou niet echt bekend om een "solide performance" en biedt eigenlijk (maar lees dat even elders goed na) geen mogelijkheden om met meer dan 1 persoon tegelijk te werken aan de inhoud van de database; hier kunnen namelijk nog wel eens fouten optreden in de data.

[vvermeer]

Met CSV is zojuist een poging gedaan. Echter dit levert niet het gewenste resultaat op. Het pakt alleen informatie uit het eerste tabblad.

[Freddy_Fish]

Net even de trial van onderstaande plugin gedownload en die doet volgens mij wat je wilt:

https://www.ablebits.com/...ksheets-consolidation.php

[Killah_Priest]

Ik denk dat het ook tijd wordt dat de processen bij de TS herzien worden : waarschijnlijk was het goedkoper (in manuren) om voor een degelijke oplossing te gaan (een "echte" database ipv gebundelde excel sheets, op maat software, dat soort dingen).

Via Powershell (excel com object aanroepen) is het wss in een uurtje gepiept evenals met een vb script.
De tijd die jouw collega in het handmatige copy/paste werk heeft gestoken was beter besteed geweest aan het leren van PS of VB, dan was EN het werk in de helft van de tijd klaar geweest en had men ervaring opgedaan om fouten te kunnen herstellen of in de toekomst hetzelfde binnen 5 minuten voor elkaar te krijgen.

[ Voor 1% gewijzigd door Killah_Priest op 26-08-2015 19:21 . Reden: typos ]

[Rupie]

Dit heeft niets met het Windows operating system te maken, ik verplaats je topic naar CSA.

[Speedmaster]

Als het gaat om Macro's uitschakelen in de database zelf dan kan je dit terug vinden bij Opties - Vertrouwens centrum - Instellingen Vertrouwenscentrum. Hier kan je instellen hoe access moet omgaan met macro's

[_Garu_]

Ik importeer excel of csv files middels vba batch werk, die kun je prima gebruiken om dynamisch te laten bepalen hoeveel data er op een tabblad staat. Als alle velden gevuld zijn met dezelfde soort data is dat redelijk makkelijk te doen.

Maar vraag blijft wel of dit eenmalig is of dat er geregeld data bijkomt en wat je verder met de data gaat doen. Je loopt in oude excel versies nog tegen maximaal aantal regels aan en voor een beetje performance wil je niet 50MB aan formules hebben als je berekeningen ook met pivot of vba kunt regelen. Maar wellicht kun je daar iets meer over vertellen, dan is het makkelijker je te helpen

[Morrar]

In Python is dit heel erg eenvoudig, maar ik weet niet of je daar aan wilt.

Stap 1: Installeer Miniconda: http://conda.pydata.org/miniconda.html
Stap 2: Installeer openpyxl, met commando: conda install openpyxl
Stap 3: Copy - paste het onderstaande script in "merge.py" en ze deze bij de xlsx bestanden
Stap 4: Run "python merge.py"

Hieronder een simpel scriptje om alle xlsx files uit een dir in te lezen en de tabbladen achter elkaar te plakken, zoals:

Python:

# Import XLSX reader
from os import listdir
from openpyxl import load_workbook

# Get filenames from current dir
files = []
for name in listdir("."):
    if name.find(".xlsx") > -1:
        files.append(name)
        
# Loop over Excel files
for file in files:
    # Load the file
    wb = load_workbook(file)

    # Loop over (sorted) worksheets
    for sname in sorted(wb.get_sheet_names()):
        # Get the worksheet
        ws = wb[sname]
        
        # Open CSV file for with the sheet name
        outfile = open(sname + ".txt", "a")
        
        # Loop over rows, cells
        for row in ws.rows:
            for cell in row:
                # Write cell to file
                outfile.write(str(cell.value) + ";")
            
            # Write new line after row
            outfile.write("\n")
        
        # Close the file
        outfile.close()

Dit script doet het volgende:
1) Pak de namen van alle xlsx bestanden uit huidige dir
2) Open een xlsx bestand en lees de werkblad namen
3) Open een txt bestand met dezelfde naam als het werkblad
4) Plak alle cellen van het werkblad achteraan het txt bestand
5) Rinse en repeat vanaf stap 2

Het kan ongetwijfeld veel mooier, maar het is een eenvoudige oplossing


P. S. Let erop dat dit script alleen werkt als alle files exact dezelfde structuur hebben qua werkbladen en kolommen!

P. P. S. Je kunt ook gewoon Python installeren en dan pip gebruiken om openpyxl te installeren

[ Voor 9% gewijzigd door Morrar op 27-08-2015 11:36 ]

[vvermeer]

Beste Tweakers,

In de tussentijd is er veel geprobeerd en met resultaat. Afgelopen dag is er een programmeur geweest die een macro heeft geschreven waarmee alle bestanden tot 1 worden samengevoegd. De database is dus op tijd af gekomen en ik wil jullie enorm bedanken voor de inzet en moeite. Indien gewenst kan ik de macro doorsturen naar de geïnteresseerde.

mvg,
Vincent