VBA code wijzigen

jan excel · 24 dec 2013

Hallo VBA Experts,

Onderstaand VBA code opent een windows scherm waarbij een bestand kan worden geselecteerd en geladen. De code kopieert uit het geselecteerde bestand in een werkblad de data en plakt deze gegevens naar een zg. verzamel (hierin staat dus deze VBA code)
Echter in het tweede deel dient uit dezelfde file de gegevens uit een andere werkblad te worden gekopieerd naar de verzamel file, ik krijg het niet voor elkaar om dit alles in één keer te doen zonder dat de betreffende file opnieuw dient te worden ingeladen. (Dus op nieuw selecteren via het windows scherm).

Zie de opmerking in de VBA code tussen deze tekens >>>>>>>>>>>>> <<<<<<<<<<<<<<<<< daaronder begint de code opnieuw met het openen van het windows scherm om een file te kunnen selecteren, wat dus eigenlijk niet nodig is omdat ik dezelfde file weer dient te openen/laden als in het begin. Het moet alleen de data uit een ander werkblad kopieren en in een ander werkblad van de verzamel te plakken.
Hopelijk begrijpen jullie aan de hand van onderstaande code wat ik bedoel en jullie mij kunnen helpen.

Verwacht dat de VBA-code ook te lang is dus verbeter voorstellen zijn ook welkom.

Code:

Private Sub CommandButton5_Click()
Application.ScreenUpdating = False
  Sheets("menu").Select
 CommandButton5.BackStyle = fmBackStyleOpaque
CommandButton5.BackColor = &HFF&
  
  Application.ScreenUpdating = False

     Sheets("activiteitsuren").Visible = True
 Sheets("activiteitsuren").Select
Sheets("activiteitsuren").Visible = False
Sheets("menu").Select
Application.ScreenUpdating = False

With Application
    .EnableEvents = False
    .DisplayAlerts = False

    .Dialogs(1).Show "E:\data\uren\magzaijn1\"
    With ActiveWorkbook
    
    Application.DisplayAlerts = False
Sheets("Uren_Magazijn").Visible = True
 Sheets("Uren_Magazijn").Select
 Range("A2:L" & Range("A4555").End(xlUp).Row).Select
 
Selection.Copy
Sheets("Uren_Magazijn").Visible = False
ThisWorkbook.Activate
Sheets("Uren").Visible = True
Sheets("Uren").Select
Lrow = Sheets("uren").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("uren").Cells(Lrow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        
         'onderstaande toegevoegd om te switchens van wel/niet lege cellen anders wordt nieuwe gegevens niet zichtbaar
        
        Sheets("uren").Select
    ActiveWorkbook.Worksheets("uren").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("uren").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "A1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("uren").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("$A$1:$R$5000").AutoFilter Field:=1, Criteria1:="<>"
    
    'onderstaand toegevoegd om te sorteren van nieuw naar oud
     
     ActiveWorkbook.Worksheets("uren").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("uren").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "A1:A5000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("uren").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

   ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        
        
        
        
        
Range("A5").Select
Sheets("Uren").Visible = False
Sheets("uren per order_machine").Visible = False

 Sheets("menu").Select
        
        
        
        
        .Close
    End With
    
    .DisplayAlerts = True
    .EnableEvents = True
End With


'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

'onderstaand VBA code haalt activiteitsuren binnen echter het zelfde bestand moet hiervoor opnieuw worden geopend,
'graag zou ik dat de code gewoon doorloopt met het zelfde resultaat en op het eind dit bestand afsluit zo als het nu ook doet.

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


With Application
    .EnableEvents = False
    .DisplayAlerts = False
  
     .Dialogs(1).Show "E:\data\uren\magzaijn1\"
  
    With ActiveWorkbook
    Application.DisplayAlerts = False
Sheets("activiteitsuren").Visible = True
 Sheets("activiteitsuren").Select
 Range("A2:P" & Range("A4555").End(xlUp).Row).Select
 
Selection.Copy
Sheets("activiteitsuren").Visible = False
ThisWorkbook.Activate
Sheets("activiteitsuren").Visible = True
Sheets("activiteitsuren").Select
 Sheets("menu").Select
  Sheets("activiteitsuren").Select
Lrow = Sheets("activiteitsuren").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("activiteitsuren").Cells(Lrow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("A5").Select
Sheets("activiteitsuren").Visible = False
Sheets("Activiteiten_overzicht").Visible = False


        
        
        
        .Close
    End With
    
    .DisplayAlerts = True
    .EnableEvents = True
End With
 CommandButton5.BackStyle = fmBackStyleOpaque
CommandButton5.BackColor = &H8000000F

Application.ScreenUpdating = True


End Sub

groet,
Jan E

MDN111 · 25 dec 2013

Dag Jan E !

Je code is inderdaad nogal lang om te analyseren, maar ik dacht begrepen te hebben dat je de gebruiker een bestand wil laten selecteren en vervolgens gegevens uit twee sheets van dat bestand over te brengen naar een verzamelbestand.

In bijlage twee bestanden. HM-Data.xls is een bestand met gegevens in twee sheets. Het bestand HM.xls is het verzamelbestand met een macro dat de gegevens uit de twee sheets overbrengt naar het verzamelbestand.

Zet de twee bestanden in dezelfde folder, open HM.xls, klik op de knop en selecteer het bestand HM-Data.xls.

De macro is zeer zeer rudimentair, maar kan misschien dienen om verder uit te bouwen.

Grtz,
MDN111.

jan excel · 26 dec 2013

Hallo MDN,

Sorry voor late reactie, kerstdagen is familiefeest vandaar.
Zal jou voorbeelden bestuderen en kijken of dit in mijn project kan worden toegepast.
Laat het nog wel weten maar niet vandaag.

groet,
Jan E

jan excel · 27 dec 2013

Hallo allen,

De files van MDN111 als basis gebruikt en momenteel strand ik op twee punten.

1. Zou graag bij openen van bestand de pad c:\test\data\ standaard willen openen en daarin betreffende file selecteren echter dit lukt mij niet met deze code.
Is het mogelijk met code GetOpenfilename ook een pad in te geven.

2. De file waaruit ik mijn gegevens dient te halen start op met een userform, echter bij einde van code sluit het de file niet af omdat het op de userform blijft hangen.

Weet iemand de code aan te passen zodat bovenstaande 2 punten gerealiseerd kan worden / geen probleem meer oplevert.

zie bijlages.
Bekijk bijlage HM.xls Bekijk bijlage HM-Data.xls

MDN111 · 27 dec 2013

Hallo Jan Excel !

Vraag 1: De macro in Module1 van het bestand HM.xls in bijlage is aangepast.

Vraag 2: Die vraag is niet duidelijk ! In HM-Data staat een lege form (???)

Grtz,
MDN111.

jan excel · 28 dec 2013

Beste MDN111,

Bedankt voor je aanvulling van de code, het naar de juiste pad gaan werkt nu.

Echter userform gebruikers menu is alleen nog een probleem voor mij.
In het vorige voorbeeld had ik deze niet automatisch geladen en nu wel.
Orgineel van het project geprobeerd na te bootsen echter niet zelfde effect.

Bij dit bestand komt direct het userform in beeld en bij het origineel bij einde van VBA-code.
Helaas kan ik het origineel niet posten i.v.m. gevoelige informatie.

Dacht zelf met instructie Unload Me het op te lossen echter dit lijdt tot een foutmelding.
Als u voor mij het probleem bij het voorbeeld bestand kunt oplossen dan kan ik dit misschien dit ook toepassen in het orgineel maar dan op het eind van de VBA code.

Hopelijk weet u of iemand anders op dit forum de oplossing, zal jullie zeer dankbaar zijn.

zie bijlages
Bekijk bijlage HM-Data.xls Bekijk bijlage HM (3).xls

groet,
Jan E

MDN111 · 28 dec 2013

Dag Jan Excel !

Sorry, maar de exacte probleemdefinitie is mij nog steeds niet duidelijk !!!

Voor zover ik kan zien gebeurt er het volgende scenario:

1. Klik op de knop "Klik hier" in HM.xls.
2. HM-Data wordt geopend en de Userform wordt geactiveerd.
3. Er zijn nu twee mogelijkheden:
4. Eerste mogelijkheid: klik op Afsluiten: er worden geen gegevens overgebracht, HM-Data.xls wordt gesloten.
5. Tweede mogelijkheid: klik op Annuleren: er worden wel gegevens overgebracht, HM-Data.xls wordt gesloten.

De vijfde stap lijkt mij niet logisch. Niettegenstaande men op Annuleren klikt, worden er toch gegevens overgebracht (???).

Wat is het verschil tussen Afsluiten en Annuleren ? Ik bedoel niet de VBA-code (want die kan ik lezen), maar ik bedoel welke scenario's er in beide gevallen moeten volgen.
En ontbreekt er dan geen knop "Overbrengen" ?

Grtz,
MDN111.

jan excel · 29 dec 2013

Hallo MDN111,

Projectfile nog even beter bestudeert en weet nu de oorzaak waarom de projectfile niet sluit na overhalen van gegevens.
De gegevens worden wel overgehaald nadat ik via het gebruikersmenu naar een bepaalde sheet ga echter de file sluit niet.
het is ook niet de bedoeling om eerst via het gebruikersmenu naar een sheet te gaan ! (dit was aleen om te testen)

Het is de bedoeling dat de gebruiker de file selecteert (in dit voorbeeld dus de test file) en dan de gegevens worden overgezet en vervolgens de test file meteen afsluit.
Je kunt in het gebruikersmenu deze file wel opslaan het wordt dan opgeslagen inclusief datum.
Dit is zo gedaan om te voorkomen dat zg blanco file (test.xls) niet wordt overschreven.
De blanco file wordt dagelijks door gebruiker ingevuld en vervolgens via het gebruikersmenu opgeslagen iclusief de datum.

Deze files met de datum worden dan weer ingeladen in de verzamelfile HM(3).xls
Deze extra informatie erbij gezet zodat u beter een beeld kunt vormen van de bedoelingen.

zie bijlage test file en kijk naar de VBA codes in this workbook, userform en module1.
Hieruit kun je opmaken dat de file niet op gebruikelijke wijze kan worden afgesloten dit is door de VBA codes onmogelijk gemaakt.
Dit moet ook zo blijven want de gebruiker dient alle handelingen via gebruikersmenu uit te voeren.

Nu is de vraag of je aan de hand van deze gegevens de VBA code in de file HM(3).xls kunt aanpassen zodat de gegevens uit de test file worden overgezet en daarna deze test file afsluit.
Hopelijk is nu wel duidelijk wat de bedoeling is.

In ieder geval bij voorbaat bedankt.
Jan E

Bijlagen

Bekijk bijlage test.xls
Bekijk bijlage HM (3).xls

MDN111 · 29 dec 2013

Dag Jan excel !

Het wordt stilaan duidelijk wat je bedoelingen zijn. Om de gebruiker van de gegevensbestanden (Test.xls) te verplichten om alle bewerkingen via de Userform "gebruikersmenu" te verrichten, maak je gebruik van de event procedures Workbook_Open() en Workbook_BeforeClose(). Maar dan kom jij er aan met je verzamelbestand (HM (3).xls) met daarin een macro die alleen maar gegevens wil overbrengen zonder geconfronteerd te worden met de Userform en zonder geconfronteerd te worden met een popup die je verzoekt om het bestand via de Userform te sluiten.

De oplossing is dat je bij het openen en sluiten van het gegevensbestand (Test.xls) de event-procedures even buiten strijd zet door Application.EnableEvents op False te zetten en nadien weer op True. De macro GetDataIntoFile() van HM(3).xls (zie bijlage) is aangepast (lijnen 33, 35, 64 en 66). Door deze aanpassing wordt het gegevensbestand (Test.xls) vanuit de macro geopend, er worden gegevens overberacht, en terug gesloten zonder dat het gegevensbestand (Test.xls) daarbij in beeld geweest is.

In je laatste bericht schrijf je "Nu is de vraag of je aan de hand van deze gegevens de VBA code in de file HM(3).xls kunt aanpassen zodat de gegevens uit de test file worden overgezet en daarna deze test file afsluit.". Het openen en sluiten werkt nu wel, maar het overbrengen van de gegevens zelf, heb ik (nog) niet bekeken. Hier zou je ook wat duidelijker moeten zijn over wat je exact wil. Moet de volledige inhoud van sheet A naar sheet V? Moet sheet V steeds onderaan aangevuld worden met nieuwe gegevens? ...? Als je wil dat ik het overbrengen van de data onder de loep neem, dan graag eerst uw duidelijke "eisen", a.u.b.

Grtz,
MDN111.

jan excel · 30 dec 2013

Beste MDN111,

MDN111 duizend maal dank, de code werkt nu perfect.
Betreft het overhalen van de gegevens had ik er al in staan zo te zien werkt dit ook goed.
Mocht ik toch nog tegen onvoorziene problemen aan lopen dan wil ik nog graag van je kennis gebruik maken.
Super bedankt nogmaals !!

groet,
Jan E.

VBA code wijzigen

jan excel

Gebruiker

MDN111

Gebruiker

Bijlagen

jan excel

Gebruiker

jan excel

Gebruiker

MDN111

Gebruiker

Bijlagen

jan excel

Gebruiker

MDN111

Gebruiker

jan excel

Gebruiker

MDN111

Gebruiker

Bijlagen

jan excel

Gebruiker

Nieuwste berichten

Wij waarderen jouw privacy