overzetten data 10 bestanden naar 1 master data bestand

[JAYFAYZ]

Beste,
Ik ben op zoek naar een methode voor het volgende en denk/vrees dat dit via VBA zal moeten gebeuren.
Momenteel bestaan er 10 xls-bestanden in de welke elke werknemer zijn reporting zet (op dezelfde uniforme manier).
Het aantal kolommen waarin input komt is steeds dezelfde (A-M) maar het aantal rijen kan meer zijn (omdat bijvoorbeeld 1 persoon meer werk heeft gedaan en dus meer rijen/dossiers moet invoegen).

Deze data van deze 10 xls-bestanden wil ik automatisch transfereren naar een master data bestand waarin dezelfde kolomen staan (A-M) en waar dus alles mooi netjes onder elkaar komt te staan zonder enige verdere manipulatie. Komt er op neer dat alle data gewoon AUTOMATISCH gekopieerd wordt en dat alle data (zonder lege regels) achter elkaar komt te staan.
Bedoeling is dat op elk moment dit master data document kan geraadpleegd worden met up 2 date info die in de 10 excel bestanden staat.
Hoe gebeurt dit het beste?


Kolom Range is A-M (staat vast; dit zijn zowel in Master Data als in input doc dezelfde variabele)
Aantal rijen (hier komt dan eigenlijk elke input voor kolommen A-M van elke user (er zijn 10 verschillend users, dus 10 verschillende xls-documenten)
Concreet zien dus master data doc en input doc(10 xlsbestanden) er hetzelfde uit (met dezeflde variablen A-M), maar ik wil dat ook elke info van elk document (user) onder elkaar komt zonder dat er lege rijen staan.

Kan iemand me op weg helpen welke code ik dien te werken? Dan zoek ik het mogelijks zelf daarna verder uit.

Alvast dank!

 

[wampier]

en denk/vrees dat dit via VBA zal moeten gebeuren.

Nee hoor kan ook prima met VB.net / C# / C++ / etc.

In principe is deze vraag al een aantal keer voorbij gekomen. Je kunt inderdaad VBA gebruiken om andere files te openen, lijnen te kopiëren en dan weer te sluiten. Hoe moeten de files herkent worden? Ik neem aan dat de file elke week een andere naam heeft? Staan ze in een vaste directory?

Graag ook eventueel een klein voorbeeldbestand zonder gevoelige informatie.

 

[JAYFAYZ]

antwoord

Hoi,
Bedankt voor het snelle antwoord!
De documenten die herkend dienen te worden hebben de naam als volgt NAAM_Voornaam.xls. (Deze docs blijven steeds geldig aangezien we ook meerjaarlijke rapportage nodig zullen hebben. Op basis van Filters kunnen ze dan terug steeds opnieuw "from scratch" beginnen.


Al deze inputdocumenten staan steeds op dezelfde locatie bijvoorbeeld: Z:\test\input\ (voorbeeld in bijlage is NAAM_Voornaamtest.xls). Deze user heeft bijvoorbeeld 19 dossiers (tem rij 20) ingegeven.
Het masterdata doc staat op Z:\test\masterdata\ (Test Master Data.xls) en hierin komt dus zowel input voor NAAM_Voornaamtest.xls die 19 dossiers heeft ingegeven en ook vanuit de andere users (andere 9 docs) maar zoals je ziet kan het zijn dat andere users bijvoorbeeld in hun document (user 2 van 21 tem 35) minder dossiers in te vullen hebben. Wil ook dat dit volledig naadloos op elkaar aansluit.
Als het geen excel functie is heb ik liever Marcro (vba) , maar ben er geen held in en volop opzoekende.
Is dit de nodige info die je vroeg?

gr

 

[JAYFAYZ]

Nee hoor kan ook prima met VB.net / C# / C++ / etc.

In principe is deze vraag al een aantal keer voorbij gekomen. Je kunt inderdaad VBA gebruiken om andere files te openen, lijnen te kopiëren en dan weer te sluiten. Hoe moeten de files herkent worden? Ik neem aan dat de file elke week een andere naam heeft? Staan ze in een vaste directory?

Graag ook eventueel een klein voorbeeldbestand zonder gevoelige informatie.

zie bericht bovenstaand...

 

[wampier]

In principe is dit de basis:

Sub invoeren()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

    Set huidig = ThisWorkbook

    'vanaf hier herhalen
    Set openen = Application.Workbooks.Open("y:\naam_voornaamtest.xlsx")
    openen.Sheets(1).UsedRange.Offset(1).Resize(openen.Sheets(1).UsedRange.Rows.Count - 1).Copy huidig.Sheets(1).UsedRange.Offset(1).Cells(1, 1)
    openen.Close (False)
    'einde herhaling

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

End Sub

natuurlijk wel even het juiste pad instellen voor de file. In principe kun je dit in een loop zetten met een reeks, of gewoon 10x de relevante code kopieren met de juiste namen.

Je kunt de code invoeren door je master data testfile te openen: ALT-F11 -> invoegen menu -> module en dan de bovenstaande code plakken Even de directory aanpassen en het zou moeten werken

 

[JAYFAYZ]

Hoi,
Dit werkt zeker al!
Als ik het dus voor de 10 wil doen, dan doe ik bijvoorbeeld voor de 2de dezelfde handeling? Een loop zetten is wat moeilijk voor me, maar eventueel wel:

Sub invoeren()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

    Set huidig = ThisWorkbook

    'vanaf hier herhalen
    Set openen = Application.Workbooks.Open("y:\naam_voornaamtest.xlsx")
    openen.Sheets(1).UsedRange.Offset(1).Resize(openen.Sheets(1).UsedRange.Rows.Count - 1).Copy huidig.Sheets(1).UsedRange.Offset(1).Cells(1, 1)
    openen.Close (False)
    'einde herhaling

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

    Set huidig = ThisWorkbook

    'vanaf hier herhalen
    Set openen = Application.Workbooks.Open("y:\naam_voornaamtest2.xlsx")
    openen.Sheets(1).UsedRange.Offset(1).Resize(openen.Sheets(1).UsedRange.Rows.Count - 1).Copy huidig.Sheets(1).UsedRange.Offset(1).Cells(1, 1)
    openen.Close (False)
    'einde herhaling

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True


End Sub

Of zie ik dit verkeerd?
Als er nu (in de toekomst) ooit een extra kolom zou bijkomen (du nog een kolom N en eventueel zelfs O) is dit dan makkelijk aan te passen?
Als ik dit weet, ga ik zeker verder kunnen en kan ik spoedig op "opgelost" klikken.
Many thanks!

 

[wampier]

Ik heb aangeduid welk stuk je moet copieeren dat zijn slechts 3 lijnen.

Het huidige script kopieerd alle relevante tekst. uitbreidingen worden dus meegenomen

 

[JAYFAYZ]

ok ik ga proberen! Ps als ik kolommen extra wille kopieren moet ik niets toevoegen?
Bedankt!

 

[wampier]

Nee, het huidige script kopieert in feite alles behalve de bovenste regel.

 

[JAYFAYZ]

Ps: heb het net geprobeerd. Als ik dit dus dit in het script herhaal (nu had ik het al 4 keer gedaan) krijg ik telkens enkel de gegevens van de file die ik als laaste in de code heb staan, de andere worden niet weergegeven :s
Mogelijks dat de gegevens van user 2 die van user 1 overschrijven?

 

[wampier]

paste je code eens hier (tussen code brackets, zie "ga geavanceerd knop")

 

[JAYFAYZ]

is dit ok?

Sub invoeren()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

Set huidig = ThisWorkbook


Set openen = Application.Workbooks.Open("y:\naam_voornaamtest.xlsx")
openen.Sheets(1).UsedRange.Offset(1).Resize(openen.Sheets(1).UsedRange.Rows.Count - 1).Copy huidig.Sheets(1).UsedRange.Offset(1).Cells(1, 1)
openen.Close (False)
Set openen = Application.Workbooks.Open("y:\naam_voornaamtest2.xlsx")
openen.Sheets(1).UsedRange.Offset(1).Resize(openen.Sheets(1).UsedRange.Rows.Count - 1).Copy huidig.Sheets(1).UsedRange.Offset(1).Cells(1, 1)
openen.Close (False)
Set openen = Application.Workbooks.Open("y:\naam_voornaamtest3.xlsx")
openen.Sheets(1).UsedRange.Offset(1).Resize(openen.Sheets(1).UsedRange.Rows.Count - 1).Copy huidig.Sheets(1).UsedRange.Offset(1).Cells(1, 1)
openen.Close (False)
Set openen = Application.Workbooks.Open("y:\naam_voornaamtest4.xlsx")
openen.Sheets(1).UsedRange.Offset(1).Resize(openen.Sheets(1).UsedRange.Rows.Count - 1).Copy huidig.Sheets(1).UsedRange.Offset(1).Cells(1, 1)
openen.Close (False)

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

End Sub

 

[wampier]

Ik zie het al, foutje van mijn kant, ik edit de post zo met de verbetering

 

[JAYFAYZ]

super!!

 

[wampier]

Ik heb een kleine aanpassing gedaan. grappig genoeg gaat dit alleen fout als er slechts 1 regel wordt gebruikt in de file

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

    Set huidig = ThisWorkbook

    'vanaf hier herhalen
    Set openen = Application.Workbooks.Open("y:\file.xlsx")
    openen.Sheets(1).UsedRange.Offset(1).Resize(openen.Sheets(1).UsedRange.Rows.Count - 1).Copy huidig.Sheets(1).UsedRange.Offset(1000).End(xlUp)
    openen.Close (False)
    'einde herhaling

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

Wat later dan gehoopt, moest even een collega hier helpen

 

[cow18]

  'vanaf hier herhalen
  For Each naam In Array("jan", "piet", "klaas", "paul")
    Set openen = Application.Workbooks.Open("y:\" & naam & ".xlsx")
    openen.Sheets(1).UsedRange.Offset(1).Resize(openen.Sheets(1).UsedRange.Rows.Count - 1).Copy huidig.Sheets(1).UsedRange.Offset(1000).End(xlUp)
    openen.Close (False)
  Next
  'einde herhaling

 

[JAYFAYZ]

Hoi,
Heb nu 2 maal ingevoegd.
Nog steeds overwriting van 2de data tov 1ste data

Sub invoeren()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

Set huidig = ThisWorkbook


Set openen = Application.Workbooks.Open("y:\file.xlsx")
openen.Sheets(1).UsedRange.Offset(1).Resize(openen.Sheets(1).UsedRange.Rows.Count - 1).Copy huidig.Sheets(1).UsedRange.Offset(1000).End(xlUp)
openen.Close (False)
Set openen = Application.Workbooks.Open("y:\file2.xlsx")
openen.Sheets(1).UsedRange.Offset(1).Resize(openen.Sheets(1).UsedRange.Rows.Count - 1).Copy huidig.Sheets(1).UsedRange.Offset(1000).End(xlUp)
openen.Close (False)


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub

 

[cow18]

For Each naam In Array("jan", "piet", "klaas", "paul")
  Set c = huidig.Sheets(1).UsedRange.Offset(1000).End(xlUp).Offset(1).cells(1)  'volgende lege rij
  Set openen = Application.Workbooks.Open("y:\" & naam & ".xlsx")
  openen.Sheets(1).UsedRange.Copy c                        'alles kopieren
  c.EntireRow.Delete                                       '1e rij weg
  openen.Close (False)
Next

 

[wampier]

Hmm, bij mij werkte het wel goed. zelfs met 3 files getest. Anders kijk ik morgen nog een keer. Je kunt ook even de regel van Cow proberen mocht je nog aanwezig zijn.

 

[JAYFAYZ]

Bedankt beiden! Ben even verder gegaan op info Wampier aangezien ik met deze code reeds bezig was. Het lukt (net getest met 2 input excels naar 1 datasheet). 1 ding nog: in het master data document wordt de laatste regel van het eerste inputdocument overschreven door de eerste rij van het 2de document? (ofwel wordt deze laatste regel van 1e input document niet mee overgenomen? Hetzelfde geld voor de eerste regel van het eerste input document. In masterdata document krijg ik op regel 1 de 2e regel uit het inputdocument (dus de eerste regel wordt niet mee overgenomen?)

Concreet:

NAAM_Voornaamtest1.xls (A1) zie ik niet in Masterdatadoctest in A1. In de plaats daarvan staat in laatstgenoemde A2 uit NAAM_Voornaamtest1.xls
Ook in Masterdatadoctest de laatste inputrij uit Naam_Voornaamtest1.Xls wordt niet weergeven, in de plaats daarvaan komt A1 uit NAAM_Voornaamtest2.Xls