Avatar billede Bregenov Juniormester
27. oktober 2016 - 14:19 Der er 27 kommentarer og
1 løsning

Sammensmeltning af to makroer - Excel

Hej Eksperter!

Jeg har tidligere af en super dygtig bruger herinde fået lavet en super løsning i Excel. (avanceret makro)

Jeg vil nu gerne have justeret denne løsning.

Jeg har et Excel dokument med to makroer. Hver makro udføres ved klik på en knap og dette skal gøres i den rigtige rækkefølge.

Vores udfordring er at enkelt brugere glemmer at bruge knappen ?indlæs? prioriteter, hvilket gør at prioriteringsrækkefølgen er forkert.
Andre brugere kommer fejlagtigt til at klikke to gange på knappen ?opdater? og så er prioriteterne også forkerte.

*) Den rigtige proces er at:

1.    Klik på ?opdater? ? Nyt data hentes fra ERP system, og manuelt data indtastet overføres til arket Prioriteringsdata.
2.    Klik på ?indlæs Prioriteter? ? De manuelle prioriteter der blev overført til arket Prioriteringsdata tilbageføres og sorteres i numerisk rækkefølge.

Nogen der har mod på at få tilsendt arket og prøve at lave makroen om såldes de begge kan køres ved et klik på en knap?

VH

Kenneth
Avatar billede kim1a Ekspert
27. oktober 2016 - 18:52 #1
De to makroer skal reelt set blot sættes i enderne af hinanden. Hvis der er sammenfald mellem variable kan det kræve lidt mere, men lig evt. filen på gratisupload.dk eller via dropbox eller lignende så tror jeg hurtigt du har svaret.
Avatar billede Bregenov Juniormester
28. oktober 2016 - 10:06 #2
Hej Kim

Jeg er ikke vildt stærk til VBA, men jeg tror at de to makroer hænger sammen, og derfor skal afvikles på en helt bestemt måde for at det fungere. Jeg har prøvet at sammenskrive dem, men det fungere desværre ikke.

Jeg prøver at uploade min Excel fil og se om der er nogen der kan hjælpe!
Avatar billede Bregenov Juniormester
28. oktober 2016 - 10:16 #3
Avatar billede Bregenov Juniormester
28. oktober 2016 - 10:17 #4
Håber der er nogen der har mod på at hjælpe...
Avatar billede terry Ekspert
28. oktober 2016 - 13:29 #5
Know almost nothing about Excel but I've looked at your Excel file and added a button which calls a new macro which then executes the other tow macros.

Not sure if it works or if its what you want. Give me an email and I'll send it
Avatar billede kim1a Ekspert
28. oktober 2016 - 13:49 #6
Jeg tror Terry har nemmeste løsning forudsat den virker. Udfordringen er vi ikke kan teste pga forbindelse til database.
Avatar billede terry Ekspert
28. oktober 2016 - 14:11 #7
Avatar billede Bregenov Juniormester
28. oktober 2016 - 14:17 #8
Løsningen med at lave en makro der kører begge makroer fungere ikke. Den har jeg selv prøvet. De to forskellige makroer har variabler begge bruger, hvorfor de skal sammenskrives på en måde.
Avatar billede Bregenov Juniormester
28. oktober 2016 - 15:06 #9
Hi Terry

Thank you for your attempt. But it doesnt work. The second macro is supposed to handle the priorities and to set them right as they were before the update, and the priorities goes in the wrong lines when using your macro.
Avatar billede terry Ekspert
28. oktober 2016 - 15:07 #10
"De to forskellige makroer har variabler begge bruger"

That needs some explanation!

Dont see how executing both macro's from one macro differs from pressing each button in turn.
Avatar billede terry Ekspert
28. oktober 2016 - 15:18 #11
didn't see your comment #9 before placing mine, but still needs explanation to understand what any why its not working
Avatar billede Bregenov Juniormester
28. oktober 2016 - 15:39 #12
Hi Terry

I will try to see if I can explain it.

Thing is that I import data from our ERP system which contains jobs for our production operators. These jobs I want to manually prioritize in my spreadsheet. This I do with the column "Prio". Here I punch in numbers manually from 1 to 99. Later i will update with new jobs that are new in the ERP database, meaning new lines will show up, and old lines will disapear in the spreadsheet. I want to combine the ERP data with my manual data when I update. so The first macro takes a copy of my manual punched in "Prio" and then updates with Data from ERP system. When this is done the prio numbers are in the wrong cells as new data has moved them up and down. Therefore I run the second macro wich puts the prio's in the right places as they were before the update. Then the difficult thing comes up: Sometimes I have a filter e.g. I only look at one machine or one operators jobs. This means that there are hidden lines, and this really messes with getting the prio's in the right cells, therefore there was made changed to both macros to work eventhough there was a filter.

The change with "taking care of the filter" is what I think makes the two macros have variables both macros uses. BUT - I am no expert, and I am not sure i am right!

But the file you maked, didnt put the prio's in the right places :-)
Avatar billede terry Ekspert
28. oktober 2016 - 16:11 #13
Sounds rather complicated.

Silly question, but does it work with using buttons in right order, also when you only look at one machine or one operator?

How do you get the data from ERP system into Excel?

If there are no manual operations between clicking the two buttons then the only thing I can think of which can be different is that the spreadsheet hasnt been fully updated between the two macro's being execute, if you see what I mean?
So maybe some update event needs placing between macros, not expert at Excel :-(
Avatar billede terry Ekspert
28. oktober 2016 - 16:16 #14
I'm really just guessing now, but try putting ActiveWorkbook.Save between lines of code where macros are executed
Avatar billede store-morten Ekspert
28. oktober 2016 - 19:36 #15
Forslag:
Deaktiver: "Indlæs prioriteter", Så denne ikke kan vælges før "Opdater"

Når der trykkes på "Opdater"
køres makro og "Opdater" Deaktiveres, "Indlæs prioriteter" Aktiveres.

Når der trykkes på "Indlæs prioriteter"
køres makro og "Indlæs prioriteter" Deaktiveres, "Opdater" Aktiveres.

Opret to knapper som: AcitveX-objekter
CommandButton1 = Opdater
CommandButton2 = Indlæs prioriteter

kode på Ark1:
Private Sub CommandButton1_Click()
GemPrioOpdateringHentPrio
End Sub

Private Sub CommandButton2_Click()
OpdaterFilterPrio
End Sub


Og tilføj kode i Module1:
    With ActiveWorkbook.Worksheets("Jobliste").ListObjects( _
        "Tabel_Forespørgsel_fra_AAOEDW").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
  ActiveWorkbook.Worksheets("Jobliste").CommandButton1.Enabled = True
    ActiveWorkbook.Worksheets("Jobliste").CommandButton2.Enabled = False

End Sub
Rem zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
Public Sub GemPrioOpdateringHentPrio()
    houseKeeping
    førOpdatering
   

ActiveWorkbook.RefreshAll

    efterOpdatering
    Afslutning
   
    ActiveWorkbook.Worksheets("Jobliste").CommandButton2.Enabled = True
    ActiveWorkbook.Worksheets("Jobliste").CommandButton1.Enabled = False

End Sub
Avatar billede Bregenov Juniormester
29. oktober 2016 - 08:24 #16
Hej Morten,
Det vil jeglige prøve. Men det er stadig to knapper ik?
Du kan ikke lave det om til en knap?
Avatar billede terry Ekspert
29. oktober 2016 - 09:48 #17
If there are no manual processes between pressing each of the buttons (in correct order) then its logical to have only one button. No idea why its not working with one although as I mentioned in my previous comment, I think it has something to do with updates not being saved before running new code.
Avatar billede terry Ekspert
29. oktober 2016 - 09:50 #18
That's if it has worked 100% correctly before :-)
Avatar billede terry Ekspert
03. november 2016 - 14:18 #19
did we find a solution?
Avatar billede Bregenov Juniormester
03. november 2016 - 14:51 #20
Hi Terry

No solution found :-(
Avatar billede terry Ekspert
03. november 2016 - 15:04 #21
That's a pity.
Without having access to data so its possible to test then its not easy to find a solution. If you have a way to do that then that could help.
Avatar billede Bregenov Juniormester
03. november 2016 - 15:27 #22
If you are using teamviwer I can give you access to my pc...
Avatar billede terry Ekspert
03. november 2016 - 15:48 #23
Is that a good idea? :-)

It could take some time to understand the code so I'd need access for some time, so maybe it isn't...

I was thinking that maybe simulating data import from ERP system in some way. Hows that done?
Avatar billede Bregenov Juniormester
03. november 2016 - 17:18 #24
All the "refresh data" does is to add and remove lines in the spreadsheet from our ERP.

I dont know how to simulate that...
Avatar billede terry Ekspert
03. november 2016 - 20:27 #25
could maybe be cut and paste from data in a file?
Avatar billede Bregenov Juniormester
04. november 2016 - 18:24 #26
Hi Terry

The guy who created the two macros did'nt have access to the ERP data...
I do not know how to simulate the ERP data.

I think there is a conflict in the two macros, or maybe they are linked together somehow I don't understand.

When I run them seperately everything works correct! But when combining them into one the last step (macro) does'nt work....
Avatar billede terry Ekspert
05. november 2016 - 11:27 #27
I'm no expert at Excel so dont really want to start digging into code I know little about, although I still dont understand why running code from both macros doesn't work. :-(

Cant you ask the guy who made the code help ?
Avatar billede Bregenov Juniormester
26. november 2016 - 19:45 #28
Jeg fandt ud af at problemet lå i at mine makroer ikke fungerede fordi hentningen af data fra ekstern kilde ikke var afviklet før næste makro blev sat i gang. Jeg løste det ved at deaktivere baggrundskørsel. Så ventede anden makro på at hentningen fra ekstern kilde var gennemført.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester