Opgelost Excel kopieren met robocopy

[stefano]

Beste,

Is er een mogelijkheid om met Robocopy een Excel-bestand bestand1.xlsx welk formules bevat te kopieren naar een bestand2.xlsx waarin de formules vervangen zijn door de waarden ?

Dank alvast voor de hulp.

Stefano

 

[edmoor]

Wat Robocopy kan weet ik niet, nog nooit gebruikt.
Maar formules naar waarden in een document kan simpel met een stukje VBA:

Sub FormulesNaarWaarden()
    For Each sh In ThisWorkbook.Sheets
        For Each cl In sh.UsedRange
            cl.Value = cl.Value
        Next cl
    Next sh
End Sub

 

[Senso]

Volgens mij kan Robocopy dat niet. Wel bestanden en mappen kopiëren maar geen inhoud wijzigen.

Schakelopties:

Een schakeloptie is een slash (/), meestal gevolgd door één letter of cijfer.

Voor hulp bij de syntaxis, parameters en schakelopties van een MS-DOS opdracht voert u op de opdrachtregel de opdrachtnaam in, gevolgd door /?. Je kunt ook de opdracht help invoeren, gevolgd door de opdrachtnaam. Voor hulpinformatie over de opdracht robocopy bijvoorbeeld kunt u uit een van de volgende instructies kiezen:

robocopy/? of help robocopy of robocopy/?|more

MS-DOS geeft informatie over de syntaxis van de opdracht, de parameters en de schakelopties weer. Voor een overzicht van alle MS-DOS opdrachten en een korte beschrijving van het doel van elke opdracht, voert u de opdracht help of help|more zonder parameters of schakelopties in.

 

[stefano]

OK, dank jullie, ik ga er mee aan de slag.

 

[stefano]

Ik heb wat zitten testen maar loop vast bij het opslaan op een bericht 'Fout 28 Onvoldoende stackruimte'
Bestand met code in bijlage. Wat ik graag zou bekomen is dat in het bestand de formules vervangen worden door waardes, daar heb ik de code van Ed gebruikt, daarna wil ik het bestand opslaan op een andere plaats en naam.

De opbouw van mijn bestand lijkt vreemd maar het is zoals dit door collega's op het werk gebruikt wordt.
Bld 1 wordt om de twee minuten automatisch geüpdatet en automatisch opgeslagen. Tijdens dit opslaan zou ik het dan willen kopiëren met vaste waarden ipv formules en het opslaan op een andere locatie.

dank voor de hulp

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'code Ed
For Each Sh In ThisWorkbook.Sheets
For Each cl In Sh.UsedRange
cl.Value = cl.Value
Next cl
Next Sh
'toegevoegd door mezelf
ActiveWorkbook.SaveAs "c:\data\beurs\beurs2.xlsx", FileFormat:=xlWorkbookNormal
End Sub

 

[stefano]

Ow, zal mijn vraag moeten verplaatsen naar Excel me dunkt want deze zit ondertussen niet meer in de juiste categorie...

 

[edmoor]

Je hebt de code in een event module gezet.
Maak er dan dit van:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  
    'code Ed
    Application.EnableEvents = False
    For Each Sh In ThisWorkbook.Sheets
        For Each cl In Sh.UsedRange
            cl.Value = cl.Value
        Next cl
     Next Sh
    Application.EnableEvents = True
      
    'toegevoegd door mezelf
    ActiveWorkbook.SaveAs "c:\data\beurs\beurs2.xlsx", FileFormat:=xlWorkbookNormal

End Sub

 

[stefano]

Gelukt, waarvoor dank.
Om het overschrijven van het bestaande bestand uit te voeren heb ik er nog een Display.Alerts = False aan toegevoegd.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    'code Ed
    Application.EnableEvents = False
    For Each Sh In ThisWorkbook.Sheets
            For Each cl In Sh.UsedRange
                cl.Value = cl.Value
            Next cl
        Next Sh
    Application.EnableEvents = True
        
    'toegevoegd door mezelf
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "c:\data\beurs\beurs2.xlsx", FileFormat:=xlWorkbookNormal
    Application.DisplayAlerts = True
End Sub

Nog een vraagje : kan je eigenlijk nog een bestand opslaan als .xslx met vba-code er in ?

 

[edmoor]

Een .xlsx bestand bevat per definitie geen VBA code.
Je kan een bestand met VBA code wel als .xlsx opslaan met FileFormat:=51
Die 51 is xlOpenXMLWorkbook
Dat bestand bevat dan geen VBA code meer.

 

[stefano]

Dag Ed ,


Vandaag eens getest op het werk maar toch nog een aantal problemen.
Het oorspronkelijke bestand (.xlsm) connecteert met een externe gegevensbron en is voorzien van een paswoord. Dit bestand moet 24/24 open blijven staan zo niet dient telkens ingelogd te worden met naam en paswoord.
Bij het wegschrijven naar een nieuwe locatie loop ik er tegen aan dat het nieuwe bestand .xlsx open staat. Ik kan het niet afsluiten.
Eigenlijk zou ik het willen wegschrijven naar een nieuwe locatie zonder dat het nieuwe bestand geopend wordt. Ik weet niet of dit kan.
Ik plak er nog eens de code bij. Kan je nog eens bekijken aub? Dank alvast

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'code om formules om te zetten in waarden
    Application.EnableEvents = False
    For Each Sh In ThisWorkbook.Sheets
            For Each cl In Sh.UsedRange
                cl.Value = cl.Value
            Next cl
    Next Sh

'code om bestand met waarden weg te schrijven als nieuw bestand op server

    Application.EnableEvents = True
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveCopyAs "\\SVRMID001\DATA\INFRONT2SAP\beurs.xlsx", FileFormat:=51
'   ActiveWorkbook.SaveAs "\\SVRMID001\DATA\INFRONT2SAP\beurs.xlsx", FileFormat:=51
    Application.DisplayAlerts = True
   Workbook.Close "\\SVRMID001\DATA\INFRONT2SAP\beurs.xlsx"


End Sub

 

[edmoor]

In plaats van SaveAs kan je dan SaveCopyAs gebruiken, maar dan kan je hem niet met een andere extensie opslaan.
En je wilt dus bij iedere wijziging in een willekeurige cel in het hele werkboek het bestand opslaan?

 

[stefano]

Klopt
Het eerste bestand wordt via een AutoSave telkens opgeslagen (om de twee minuten).
Dan liep er een script via Robocopy die dit bestand kopieerde naar een andere locatie.
Het bestand werd intussen omgezet van formules naar waarden bij het verplaatsen/kopiëren maar er is nergens een code te vinden.
Vandaar de pogingen om het op die manier te gaan doen.

 

[edmoor]

Probeer het eens zo:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Beurs As Workbook
    Dim SavePath As String
   
'code om formules om te zetten in waarden
    Application.EnableEvents = False
    For Each Sh In ThisWorkbook.Sheets
        For Each cl In Sh.UsedRange
            cl.Value = cl.Value
        Next cl
    Next Sh
    Application.EnableEvents = True

'code om bestand met waarden weg te schrijven als nieuw bestand op server
    SavePath = "\\SVRMID001\DATA\INFRONT2SAP"
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    ActiveWorkbook.SaveCopyAs SavePath & "\beurs.xlsm"
    Set Beurs = Workbooks.Open(SavePath & "\beurs.xlsm")
    Beurs.SaveAs SavePath & "\beurs.xlsx", 51
    Beurs.Close False
    Kill SavePath & "\beurs.xlsm"
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[stefano]

Met deze code wil Excel het bestand niet overschrijven omdat het in gebruik is.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Beurs As Workbook
    Dim SavePath As String
  
'code om formules om te zetten in waarden
    Application.EnableEvents = False
    For Each Sh In ThisWorkbook.Sheets
        For Each cl In Sh.UsedRange
            cl.Value = cl.Value
        Next cl
    Next Sh
    Application.EnableEvents = True

'code om bestand met waarden weg te schrijven als nieuw bestand op server
    SavePath = "\\SVRMID001\DATA\INFRONT2SAP"
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

 ==>   ActiveWorkbook.SaveCopyAs SavePath & "\beurs.xlsm"
    Set Beurs = Workbooks.Open(SavePath & "\beurs.xlsm")
    Beurs.SaveAs SavePath & "\beurs.xlsx", 51
    Beurs.Close False
    Kill SavePath & "\beurs.xlsm"
  
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[edmoor]

Werkt bij mij prima.
Dan is er bij jou iets met rechten aan de hand.

 

[stefano]

Gelukt na heropstarten. Dank je wel !