Vanuit Acces TransferSpreadsheet acExport

[Sytse1]

Vanuit Acces VBA exporteer ik een query naar Excel

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Qry", PadNaarDir, True, "NaamSheet"

Dit werkt wel alleen wordt een oude xlsx bestand niet overschreven.
Het blijkt dat de geëxporteerde query in Excel een alleen lezen worksheet is.
Dit zou een te bewerken blad moeten zijn.
Voordat er opnieuw een bijgewerkt bestand naar Excel wordt geëxporteerd wil ik de oude aanwezige xlsx verwijderen.
Dit gaat niet. Met Kill of een Delete procedure wordt het xlsx bestand niet verwijderd.
Ook als Excel is afgesloten is de xlsx met b.v. Verkenner niet te verwijderen.
Ik krijg de melding dat het bestand in Excel is geopend.
Excel is echter afgesloten en ook niet op de achtergrond geopend.
Ik heb het vreemde idee dat dit is ontstaan na een update van Windows 10 naar 11.
Het office product is Office 2019
Wellicht herkent iemand dit probleem?
Sytse

 

[AccessGuru]

Dit gaat niet. Met Kill of een Delete procedure wordt het xlsx bestand niet verwijderd.

Met één regeltje code kan ik, dank zij mijn glazen bol, precies zien wat je fout doet. Helaas gaat dat advies je uiteraard geld kosten. De complete relevante code meesturen daarentegen zal geheid een gratis advies opleveren. De keus is aan jou .

 

[Sytse1]

AccessGuru, dat valt mij toch vies van je tegen:d
Ik had verwacht dat je het zo uit je glazen- of het andere bolletje had getoverd.
Maar ik zal het uiteen rafelen en een kleine db met de tabel, query en code maken.
Bvd, Sytse

 

[AccessGuru]

Ik had verwacht dat je het zo uit je glazen- of het andere bolletje had getoverd.

Dat kán ik uiteraard wel, maar zo'n glazen bol kost niet niks, en die kosten moeten er toch een keer uit :d.

 

[Sytse1]

Export naar Excel

AccessGuru,

Bijgaand een aangepast Acces bestand met daarin mijn probleem.
Dit is volgens mij ontstaan nadat ik een upgrade naar Windows 11 heb gedaan.
Onder Windows 10 werkte het probleemloos. (Downgrade is niet meer mogelijk)

De eerste keer wordt het bestand als een alleen lezen bestand opgeslagen.
Dit is niet de bedoeling.
Het xlsx bestand is daarna niet te verwijderen of te overschrijven.
Alleen bij een herstart van Windows is het te verwijderen.
Omdat Excel op de achtergrond onzichtbaar blijft openstaan.
Het openstaan van Excel is ook bij Taakbeheer niet zichtbaar.

Onder W10 werkte eveneens het aanpassen van de tabbladen zoals in de VBA module staat.
Ook dit werkt niet. Ik heb er een optie bijgezet voor het wijzigen van het percentage zodat je makkelijk kan zien of het laatste xlsx de vorige heeft overschreven.
Misschien zie jij wel waar ik de fout ben ingegaan.
(Zo te zien heb jij de taken Octafish overgenomen. Of is Octafish met pensioen gegaan?)

Bij voorbaat dank,
Sytse

 

[AccessGuru]

Ik zal er naar kijken. Ik vraag me wel af of Windows 11 de boosdoener is. Zelf gebruik ik die ook, dus als het bij mij niet​ optreedt, ligt het niet daar aan .

 

[Sytse1]

Ik ben benieuwd. Bedankt vast.

 

[Sytse1]

Na een halve dag zoeken en wat ik noem prutten werkt het opeens weer zoals het zou moeten.

 

[AccessGuru]

D'r zijn een paar dingen die ik niet snap, en die ook prompt fout gaan. Die had je zelf ook kunnen vinden als je niet aan het begin de optie On Error Resume Next had neergezet, want daarmee maskeer je alle fouten, en hobbelt de procedure dus vrolijk zwaaiend langs alle problemen . Maar heb heb niet het gewenste resultaat. In testsituaties dus nooit​ beginnen met [FONT=&quot]On Error Resume Next. [/FONT]Dat is heel vervelend voor het vinden van fouten. De eerste zag ik al gelijk:

     WisXlsx = "C:\TEMP\Backup"    If Not WisXlsx = " " Then
        Kill "C:\TEMP\Backup\Periodeoverzicht.xlsx"
    End If

Dat gaat natuurlijk nooit werken: eerst vul je een variabele met de tekst "C:\TEMP\Backup" en in de volgende regel ga je testen of de variabele een spatie bevat? Wat denk je dat daar uit komt? Wat je had moeten doen, is dit:

    If Not Dir("C:\TEMP\Backup\Periodeoverzicht.xlsx") = "" Then        Kill "C:\TEMP\Backup\Periodeoverzicht.xlsx"
    End If

Dan check je dus daadwerkelijk of het bestand bestaat of niet.

Volgende fout: je exporteert twee tabbladen naar je periode overzicht. Dat werkt prima. Die hebben de namen "Gecorrigeerd_70_perc" en "Periodeoverzicht_100_perc". En wat doe jij vervolgens?

    Set wkSht = wkBK.Sheets("Periodeoverzicht")

Je activeert een werkblad dat niet bestaat. Nogmaals: dat je code daar doorloopt, komt dus door die On Error Resume Next. Anders stopt je code daar, en loopt dan echt niet verder. Verander ik de naam, dan is het volgende pijnpunt (foutmelding Methode Sort mislukt):

            .Cells(1, 2).CurrentRegion.EntireColumn.Sort

Hij kan dus niet sorteren op die kolom.
Dat probleem herhaalt zich (naam en kolom) voor de tweede tabel.
De code een beetje opgeschoond, en deze werkt dus, op het sorteren na.

Private Sub Knop80_Click()
Dim xlApp As Object, wkBK As Object, wkSht As Object
Dim Proc As Long, WisXlsx As String, tFile As String, VarDD As Date


''On Error Resume Next
'Exporteer naar Excel


'Het ingevulde percentage
    Proc = DLookup("Procent", "Tbl_Procent", "[id]=1")
    
    If MsgBox("De spreadsheets met 100% en met " & Proc & "% worden opgeslagen in C:\TEMP\Backup\Periodeoverzicht.xlsx " _
        & Chr(13) & Chr(13) & " Bij keuze Ja wordt het bestand in Excel geopend", vbQuestion + vbYesNo, "Bestand openen") = vbNo Then
        MsgBox "Oke, dan openen Excel niet. Het Periodeoverzicht is wel opgeslagen." & Chr(13) & "U gaat terug naar het laatste overzicht", vbYes, "Volgende keer beter"
        Exit Sub
    End If
    
    'De map moet wel bestaandus maak de map aan!!
    'De mappen voor de backup worden gemaakt
    VarDD = Date
    If Len(Dir("C:\TEMP\", vbDirectory)) = 0 Then MkDir "C:\TEMP\"
    If Len(Dir("C:\TEMP\Backup\", vbDirectory)) = 0 Then MkDir "C:\TEMP\Backup\"
    'Eerst wordt de aanwezige Periodeoverzicht gewist.
    WisXlsx = "C:\TEMP\Backup"
    If Not Dir("C:\TEMP\Backup\Periodeoverzicht.xlsx") = "" Then
        Kill "C:\TEMP\Backup\Periodeoverzicht.xlsx"
    End If
    
    'Werk de tabel tbl_UItslagen_Periode met het percentage bij
    DoCmd.OpenQuery "Qry_Tbl_UItslagen_Periode_Proc_Bijwerken", acViewNormal, acEdit
    tFile = "C:\TEMP\Backup\Periodeoverzicht.xlsx"
    'Percentage overzicht
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Qry_UItslagen_Periode_Kruistabel_Totaal_Correctie", tFile, True, "Gecorrigeerd " & Proc & " perc"
    '100% overzicht
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Qry_UItslagen_Periode_Kruistabel_Totaal", tFile, True, "Periodeoverzicht 100 perc"
    
    'Set in Excel de de tabbladen goed
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set wkBK = xlApp.Workbooks.Open(tFile)
    Set wkSht = wkBK.Sheets("Periodeoverzicht_100_perc")
    With wkSht
        With .rows("1:1")
            .WrapText = False
            .Orientation = 90
            .Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
''            .Cells(1, 2).CurrentRegion.EntireColumn.Sort
        End With
    End With
    
    Set wkBK = xlApp.Workbooks.Open(tFile)
    Set wkSht = wkBK.Sheets("Gecorrigeerd_70_perc")
    With wkSht
        With .rows("1:1")
            .WrapText = False
            .Orientation = 90
        End With
        .Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
''        .Cells(1, 2).CurrentRegion.EntireColumn.Sort
        .Range("A1").Select
    End With


End Sub

 

[Sytse1]

AccessGuru,

Ik neem mijn hoed af en maak een buiging.
Geweldig bedankt.
Het was uitermate klungelig. Als je(ik) wat wijzig kijk verder dan je neus lang is.
Dit voor de tabbladen. De spatie is natuurlijk ook dom.
Bedankt voor je aanpassingen.:thumb:
Sytse