[EXCEL] VBA Knop

[Nickhubers1988]

Mijn vraag
Is het mogelijk om in een sharepoint omgeving een VBA knop te maken om excel data naar een andere excel bestand te exporteren?

Het doel is om de gedane werkzaamheden van verschillende projecten af te vinken en terug te laten vinden in een totaaloverzicht.

De data die geëxporteerd moet worden is een template die voor elk project opnieuw gebruikt wordt en in een ander map geplaatst wordt. Het totaaloverzicht blijft in eenzelfde map staan(onedrive).

Aangezien het exporteren naar een specifieke cel in het andere bestand moet gebeuren, had ik het idee om de juiste regel aan te sturen door die te koppelen aan een overeenkomende 'job files nummer'.

Met een afbeelding probeer ik het te verhelderen.



Relevante software en hardware die ik gebruik
Office 365, Onedrive, Sharepoint omgeving(teamsite), Windows 10

Wat ik al gevonden of geprobeerd heb
Door de teleurstelling van excel naar word middels Bookmarks en Field Forms wil ik mij niet meer wagen aan nieuw onderzoek, aangezien het twee werkdagen heeft gekost en niks opgeleverd. Nu hoop ik dat iemand die zegt JA dat kan en mij een beetje in de juiste richting kan sturen om het te kunnen ontwikkelen.

[Atanamir]

Dit kan wel lastig zijn denk ik doordat je in je VBA code naar het adres van het totaaloverzicht moet refereren.

Ik heb even kort gezocht, en wellicht dat dit je verder helpt:
https://stackoverflow.com...s-in-one-drive-sharepoint

Kort gezegd:
- je moet de CID code van de gebruiker achterhalen
- obv die code een netwerklocatie mappen
- met die gemapte netwerklocatie kun je naar de bestanden refereren.

[Joep]

Als je de Onedrive-folder synchroniseert op de computer waarop je werkt, is de vba-code heel simpel. Of wil je dat de code werkt als je het Excel-bestand online in de browser opent?

[Nickhubers1988]

Dank voor de reacties! De one drive wordt op alles devices op de computer gesynchroniseerd, dus hoop inderdaad daarmee een makkelijke manier te vinden.

Echter als dat niet werk zal ik de geboden oplossing van Atanamir verder onderzoeken.

Nu is mijn vraag hoe en welke vba code kan ik daarbij gebruiken? Zelf een path wijzigen in de code dat gaat mij nog wel af, maar een code hiervoor schrijven gaat mij een beetje mijn petje te boven. Mocht iemand mij hiermee op weg kunnen helpen code of linken waar ik hier gericht informatie over kan vinden dan heel graag!

[Paul1987]

Nickhubers1988 schreef op zaterdag 7 april 2018 @ 23:01:
Dank voor de reacties! De one drive wordt op alles devices op de computer gesynchroniseerd, dus hoop inderdaad daarmee een makkelijke manier te vinden.

Echter als dat niet werk zal ik de geboden oplossing van Atanamir verder onderzoeken.

Nu is mijn vraag hoe en welke vba code kan ik daarbij gebruiken? Zelf een path wijzigen in de code dat gaat mij nog wel af, maar een code hiervoor schrijven gaat mij een beetje mijn petje te boven. Mocht iemand mij hiermee op weg kunnen helpen code of linken waar ik hier gericht informatie over kan vinden dan heel graag!

Ik vind de beschrijving die je geeft nog wat onduidelijk. Als ik het lees denk ik het volgende te zien.

1. Je hebt een overzichtsdocument en één of meerdere specifiek(e) document(en)
2. Je wil op basis van een job nr het overzichtsdocument met daarin een gegevens matrix aan laten vullen

Als dit klopt dan:
1. Mis ik, in het voorbeeld, de criteria op basis waarvan de juiste positie in de matrix geselecteerd wordt.
2. Je geeft aan dat je iets wilt exporteren d.m.v een template, maar het is mij onduidelijk wat je precies wilt exporteren.

Verder is het zeker mogelijk om dit te doen, sharepoint kun je d.m.v vba laten uit en inchecken. Onedrive is niets meer dan een lokale folder die d.m.v een synchronisatie tool aan de cloud dienst verbonden is.

Het is echter wel afhankelijk van wat je wilt of de VBA module(s) meer of minder complex worden.
Ik denk dat je een heel eind moet komen met
1. de sharepoint functionaliteit, EN
2. met de If functie in VBA

Je kunt heel makkelijk een template maken, openen met excel en opslaan in een bestand met een naamgeving op basis van cel inhoud of op basis van bijvoorbeeld een unieke naam d.m.v een timestamp o.i.d.

[Nickhubers1988]

Dank voor je reactie!

Paul1987 schreef op zondag 8 april 2018 @ 10:17:
[...]


Ik vind de beschrijving die je geeft nog wat onduidelijk. Als ik het lees denk ik het volgende te zien.

1. Je hebt een overzichtsdocument en één of meerdere specifiek(e) document(en)Klopt
2. Je wil op basis van een job nr het overzichtsdocument met daarin een gegevens matrix aan laten vullenKlopt

Als dit klopt dan:
1. Mis ik, in het voorbeeld, de criteria op basis waarvan de juiste positie in de matrix geselecteerd wordt.Op basis van het jobnummer, zoals je bij punt 2 hierboven vermoedde, wil ik graag van meerdere documenten de koppeling maken met het overzichtsdocument, waarbij de job nummer voor de codering in VBA het regelnummer kan vinden en dat de data gestuurd wordt vanuit de andere documenten naar die regel in de door mij aangewezen kolommen met de specifieke tekst 'gedaan' of 'herkeuring'.
2. Je geeft aan dat je iets wilt exporteren d.m.v een template, maar het is mij onduidelijk wat je precies wilt exporteren.Hopelijk geeft het antwoord hierboven voldoende informatie?

Verder is het zeker mogelijk om dit te doen, sharepoint kun je d.m.v vba laten uit en inchecken. Onedrive is niets meer dan een lokale folder die d.m.v een synchronisatie tool aan de cloud dienst verbonden is.

Het is echter wel afhankelijk van wat je wilt of de VBA module(s) meer of minder complex worden.
Ik denk dat je een heel eind moet komen met
1. de sharepoint functionaliteit, EN Kun je hier wat specifieker in zijn?
2. met de If functie in VBA Ben ik heel brutaal als ik gewoonweg vraag naar een voorbeeld code? Het toepassen vd code en kleine wijzigingen maken naar alle andere documenten gaat mij nog wel af vermoed ik, maar een hele code te schrijven weet ik niet waar ik moet beginnen.

Je kunt heel makkelijk een template maken, openen met excel en opslaan in een bestand met een naamgeving op basis van cel inhoud of op basis van bijvoorbeeld een unieke naam d.m.v een timestamp o.i.d.
Eerlijk gezegd snap ik niet wat je hiermee bedoeld.

Graag deel ik wel wat achtergrond informatie hoe het voor ons in de praktijk eruitziet mbt het idee. Voorbeeld. Voor één project voeren wij meerdere keuringen uit van testen op locatie tot aan testen in het lab, echter is de administratie tijdrovend en niet overzichtelijk geworden door nieuwe projecten. Daardoor willen wij onze opdrachtgevers en onszelf een dynamische overzichtsschema geven, zodat de opdrachtgevers weten waar wij zitten met hun project en voor onszelf is het van belang dat wij ook weten waar wij staan, dit omdat als alles gedaan is dat er dan pas een rapport opgesteld kan worden

[Paul1987]

Wat betreft SharePoint heb je meerdere functies. De meest basale en belangrijke zijn volgens mij:
1. Workbooks.CheckOut <workbook>.FullName
2. ActiveWorkbook.CheckIn

Deze heb je altijd nodig om een document te kunnen bewerken en beschikbaar te stellen voor andere gebruikers.

Wat betreft de koppeling tussen de documenten is het belangrijk dat je gestandaardiseerd gaat werken.
Je kunt vanuit het hoofddocument natuurlijk makkelijk importeren d.m.v. vba. een workbook openen / kopiëren etc is geen rocketscience. De manier waarop je de juiste informatie selecteert is wel belangrijk.
Wanneer je met een matrix (meer dan 2 oriëntatie punten) werkt zoals jij nu doet is het belangrijk dat deze overal het zelfde zijn. Je selecteert de regel op basis van een jobID, vervolgens moet je de juiste kolom nog selecteren op basis van een specifieke waarde. Je zult in het 'job document' dus een cel moeten hebben waar deze omschrijving staat.

Hieronder een heel snel voorbeeld, ik maak hier geen gebruik van losse documenten. Maar het schetst wel hoe je het mogelijk aan kunt pakken. Als je nog veel meer hulp nodig hebt kan ik misschien beter een V&A dienst aanbieden ter inhuur :-P

Visual Basic .NET: Module1

Sub test()

Dim wb As Workbook
Dim Bron_ws As Worksheet
Dim Doel_ws As Worksheet
Dim Job_ID As Range
Dim Job_descr As Range
Dim Job_status As Range

Dim rij_nr As Double
Dim kolom_nr As Double


Set wb = ActiveWorkbook
Set Bron_ws = wb.Sheets("Blad2") 'Bron = document per project/fase
Set Doel_ws = wb.Sheets("Blad1") 'Doel = Hoofd document
Set Job_ID = Bron_ws.Range("B2") 'Job-ID, ingevuld in bron
Set Job_descr = Bron_ws.Range("B8") 'Job omshrijving moet overeenkomen met kolom naam
Set Job_status = Bron_ws.Range("B9") 'Job status mag een vrije tekst zijn, kun je koppelen aan een auto opmaak waardoor je keurig een kleurtje meegeeft aan de cel.


Dim Last_R

With Doel_ws 'Definieert de laatste (gevulde) cel in kolom A, dit om ervoor te zorgen dat je niette veel rijen raadpleegt bij een zoek opdracht.
Last_R = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Dim R
For R = 2 To Last_R 'sluit de eerste rij uit aangezien daar de kolom kop staat.
If Doel_ws.Cells(R, 1).Value <> "" Then 'als de cel leeg is wordt deze overgeslagen
If Doel_ws.Cells(R, 1).Value = Job_ID.Value Then 'vergelijking tussen Job ID bron en geselecteerde cel op rij = R
rij_nr = R 'Gevonden rij nr vastleggen voor 'later'
End If
End If
Next R

With Doel_ws 'definieert de laatste (gevulde) cel in rij 1, dit om ervoor te zorgen dat je niet teveel kolommen doorloopt bij de zoekopdracht.
    last_c = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

Dim C
For C = 1 To last_c
If Doel_ws.Cells(1, C).Value <> "" Then 'als de cel leeg is wordt deze overgeslagen
If Doel_ws.Cells(1, C).Value = Job_descr.Value Then 'vergelijking tussen Job descr bron en geselecteerde cel op kolom = R
kolom_nr = C 'Gevonden kolom nr vastleggen voor 'later'
End If
End If
Next C

Doel_ws.Cells(rij_nr, kolom_nr).Value = Job_status.Value 'Job status overnemen naar doelblad in gevonden rij/kolom

End Sub

[Nickhubers1988]

Paul, dank voor je antwoord. Ik heb je ook een berichtje gestuurd.

Ik ben even gaan sleutelen en ik heb het werkend gekregen. Met een werkend drukknop(ook handig), misschien handiger om de data te exporteren wanneer opgeslagen is en sluit... Naja een zorg voor later.
Alleen zoals je al schetste is dit in één worksheet.

(Dit zeg ik terwijl ik zelf verder zoek) Wie o wie kan mij in de juist richting sturen om het te linken aan een ander excel bestand(doel document) F:\Onedrive\Sports Labs\Sports Labs EU - Documenten\SPORTS LABS EU\JOB FILES\Test met de sheet Totaaloverzicht.
Het doel van het bron document is alleen exporteren, liefst zonder dat het bestand geopend wordt.
Kortom: Knop> exporteren naar andere workbook(totaaloverzicht) zonder visueel te openen > opslaan > sluiten van het document(totaaloverzicht)

Edit: Helaas werkt dit hieronder niet. Hij opende het bestand wel inclusief een fout

code:

Sub test()

Dim wb As Workbook
Dim Bron_ws As Worksheet
Dim Doel_ws As Worksheet 'ik vermoed dat dit Workbook moet zijn
Dim Job_ID As Range
Dim Job_descr As Range
Dim Job_status As Range

Dim rij_nr As Double
Dim kolom_nr As Double



Set wb = ActiveWorkbook
Set Bron_ws = wb.Sheets("Sheet1") 'Bron = document per project/fase
Set Doel_ws = wb.Sheets("Totaaloverzicht") 'Doel = Hoofd document 'En ik vermoed hier dat het juist bestand wordt geopend
Set Job_ID = Bron_ws.Range("J6") 'Job-ID, ingevuld in bron
Set Job_descr = Bron_ws.Range("K5") 'Job omshrijving moet overeenkomen met kolom naam
Set Job_status = Bron_ws.Range("K6") 'Job status mag een vrije tekst zijn, kun je koppelen aan een auto opmaak waardoor je keurig een kleurtje meegeeft aan de cel.


Dim Last_R

With Doel_ws 'Definieert de laatste (gevulde) cel in kolom A, dit om ervoor te zorgen dat je niette veel rijen raadpleegt bij een zoek opdracht.
Last_R = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Dim R
For R = 2 To Last_R 'sluit de eerste rij uit aangezien daar de kolom kop staat.
If Doel_ws.Cells(R, 1).Value <> "" Then 'als de cel leeg is wordt deze overgeslagen
If Doel_ws.Cells(R, 1).Value = Job_ID.Value Then 'vergelijking tussen Job ID bron en geselecteerde cel op rij = R
rij_nr = R 'Gevonden rij nr vastleggen voor 'later'
End If
End If
Next R

With Doel_ws 'definieert de laatste (gevulde) cel in rij 1, dit om ervoor te zorgen dat je niet teveel kolommen doorloopt bij de zoekopdracht.
    last_c = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

Dim C
For C = 1 To last_c
If Doel_ws.Cells(1, C).Value <> "" Then 'als de cel leeg is wordt deze overgeslagen
If Doel_ws.Cells(1, C).Value = Job_descr.Value Then 'vergelijking tussen Job descr bron en geselecteerde cel op kolom = R
kolom_nr = C 'Gevonden kolom nr vastleggen voor 'later'
End If
End If
Next C

Application.ScreenUpdating = False
    Workbooks.Open Filename:="F:\Onedrive\Sports Labs\Sports Labs EU - Documenten\SPORTS LABS EU\JOB FILES\Test\TOTAALOVERZICHT SPORTSLABS EU.xlsx"
'Hiermee hoop ik het bestand te openen

Doel_ws.Cells(rij_nr, kolom_nr).Value = Job_status.Value 'Job status overnemen naar doelblad in gevonden rij/kolom

Windows("F:\Onedrive\Sports Labs\Sports Labs EU - Documenten\SPORTS LABS EU\JOB FILES\Test\TOTAALOVERZICHT SPORTSLABS EU.xlsx").Close
    Application.ScreenUpdating = True
'Hiermee hoop ik het bestand te sluiten. Daartussen moet nog wel ergens het opslaan gebeuren

End Sub

[ Voor 71% gewijzigd door Nickhubers1988 op 10-04-2018 12:30 ]

[Nickhubers1988]

Uiteindelijk heb ik met behulp van Paul1987's code en wat kleine aanpassingen de VBA functie werkend gekregen. Voor de liefhebbers die er ook op vastlopen in de toekomst hieronder de code.

code:

Sub test()

Dim wb As Workbook
Dim wbd As Workbook
Dim Bron_ws As Worksheet
Dim Doel_ws As Worksheet
Dim Job_ID As Range
Dim Job_descr As Range
Dim Job_status As Range

Dim rij_nr As Double
Dim kolom_nr As Double


Set wb = ActiveWorkbook
Set wbd = Workbooks.Open("F:\Onedrive\Sports Labs\Sports Labs EU - Documenten\SPORTS LABS EU\JOB FILES\Test\TOTAALOVERZICHT SPORTSLABS EU.xlsx")
Set Bron_ws = wb.Sheets("Sheet1")
Set Doel_ws = wbd.Sheets("Totaaloverzicht")
Set Job_ID = Bron_ws.Range("J6") 'Job-ID, ingevuld in bron
Set Job_descr = Bron_ws.Range("K5") 'Job omshrijving moet overeenkomen met kolom naam
Set Job_status = Bron_ws.Range("K6") 'Job status mag een vrije tekst zijn, kun je koppelen aan een auto opmaak waardoor je keurig een kleurtje meegeeft aan de cel.


Dim Last_R

With Doel_ws 'Definieert de laatste (gevulde) cel in kolom A, dit om ervoor te zorgen dat je niette veel rijen raadpleegt bij een zoek opdracht.
Last_R = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Dim R
For R = 2 To Last_R 'sluit de eerste rij uit aangezien daar de kolom kop staat.
If Doel_ws.Cells(R, 1).Value <> "" Then 'als de cel leeg is wordt deze overgeslagen
If Doel_ws.Cells(R, 1).Value = Job_ID.Value Then 'vergelijking tussen Job ID bron en geselecteerde cel op rij = R
rij_nr = R 'Gevonden rij nr vastleggen voor 'later'
End If
End If
Next R

With Doel_ws 'definieert de laatste (gevulde) cel in rij 1, dit om ervoor te zorgen dat je niet teveel kolommen doorloopt bij de zoekopdracht.
    last_c = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

Dim C
For C = 1 To last_c
If Doel_ws.Cells(1, C).Value <> "" Then 'als de cel leeg is wordt deze overgeslagen
If Doel_ws.Cells(1, C).Value = Job_descr.Value Then 'vergelijking tussen Job descr bron en geselecteerde cel op kolom = R
kolom_nr = C 'Gevonden kolom nr vastleggen voor 'later'
End If
End If
Next C


Doel_ws.Cells(rij_nr, kolom_nr).Value = Job_status.Value 'Job status overnemen naar doelblad in gevonden rij/kolom

wbd.Save
wbd.Close



End Sub

[Nickhubers1988]

Ik heb nog eigenlijk nog een vraag. Nu is het zo dat op deze computer One Drive op station letter F staat en de verwijzing wordt ook gemaakt in de code naar het doelbestand op de F station. Echter kan het maar zo zijn dat de stationsletter op één van de andere apparaten een andere letter is. Is het mogelijk om de code aan te passen dat de stationsletter niet uitmaakt? Of moet de stationsletters van alle andere apparaten worden aangepast naar dezelfde letter?

[Atanamir]

Dat was de situatie waar ik in mijn eerdere antwoord naar refereerde. Op je eigen pc is je onedrive map makkelijk te vinden, maar voor andere gebruikers weet je het niet.

De webpagina waar ik naar linkte lost dat op door een netwerkschijf te mappen bij de gebruikers, waardoor je daarna altijd dezelfde letter kunt gebruiken...