Access dump naar excel en bewerken

Status
Niet open voor verdere reacties.
J

Jasper91

Gebruiker
Lid geworden
10 apr 2011
Berichten
37
Goedemiddag,

Ik heb een probleempje met een kopperling van Access naar Excel.
Een leverancier van ons wil graag onze orderbevestigingen voor hem in een appart excel document hebben, wat we vervolgens handmatig op zijn website zetten.
In een Access database heb ik alle orderbevestigingen naar alle leveranciers staan.
Hieruit moet ik dan vervolgens een dump maken naar Excel om deze vervolgens verder te verwerken.

Dit is een stukje uit de code:

Code: 
im Exclapp As Excel.Application
Dim Exclwkb As Excel.Workbook
Dim myRow As Long
Dim myColumn As Long

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Orderbevestigingen", "Z:\specifiek document.xlsx", False, "Sheet2"

Set Exclapp = New Excel.Application
    Exclapp.Visible = True
With Exclapp
    .Visible = True
    Set Exclwkb = .Workbooks.Open("Z:\specifiek document.xlsx", , False)
End With
'Exclwkb.Activate

    For Each nm In Exclapp.ActiveWorkbook.Names
        nm.Delete
    Next


Exclapp.Sheets("Sheet2").Select

myRow = 2
Do Until myRow = 4000
    If Exclapp.Cells(myRow, 2).Value <> "728" Or Exclapp.Cells(myRow, 2).Value <> 728 Then '728 is het leveranciersnummer van de betreffende leverancier
    Exclapp.Cells(myRow, 2).Select
        Exclapp.Selection.EntireRow.Delete
    End If
myRow = myRow + 1
Loop

De dump wordt goed gemaakt, het gaat echter mis bij het verwijderen van de overbodige rijen.
Hierin worden namelijk niet alle rijen weggehaald die weggehaald zouden moeten worden; de helft wordt wel weg gehaald, maar een 1500 overbodige records blijven staan.

Ik hoop dat jullie me kunnen helpen
Gr
 
En waarom niet gelijk het juiste gegevensbestand geëxporteerd? Maak een query waarin je vast filtert op de leverancier, en exporteer die. Dan staat er geheid niks anders in dan de juiste records :)
 
Zonder de data te zien is het lastig om de vinger op het probleem te leggen

Je kunt het volgende proberen.

Verander de OR in AND, je wil tenslotte alle regels verwijderen die aan geen van beide voldoen.
en pas de volgende regels aan
Code: 
    Exclapp.Cells(myRow, 2).Select
        Exclapp.Selection.EntireRow.Delete
tot
Code: 
Exclapp.Rows(myRow).EntireRow.Delete

Of dit je probleem oplost durf ik niet te zeggen, de code zal w.s. wel sneller lopen.
We lezen het hoe en wat wel

Mvg Leo
Of doe wat Octafih aangeeft, die heeft verstand van Acces :)
 
Laatst bewerkt:
Dankjullie wel voor jullie reacties!

Sorry voor mijn late reactie, ik ben vanochtend erg druk geweest met andere werkzaamheden.

Leofact, ik heb beide opties geprobeerd, maar ik blijf hetzelfde probleem houden.

Octafish, de database is door de jaren heen door verschillende mensen in verschillende access versies opgebouwd. Dit maakt het creeeren van query's soms erg lastig.
Ik heb er inmiddels wel 1 gemaakt en deze ook succesvol geexporteerd naar een nieuw document. Is het echter ook mogelijk om de query in een nieuw document in te voegen (vb. "Document1", beginnend bij cel "B4"?).

Ik open en exporteer de query m.b.v. de ingebouwde Macro Builder en de optie ExportWithFormatting.

Alvast bedankt.
 
De code hierboven is duidelijk geen macro, maar VBA. Waarom ben je overgestapt op een macro? Ik zou het bij VBA houden, want op basis van die code kun je (we zien maar een stukje zeg je zelf) vermoedelijk alles wel regelen. En zeker als je geen rijen hoeft te verwijderen.
 
Omdat ik bij VBA de volgende error message kreeg:
Run time error 3673: This table contains cells that are outside the range of cells defined in this spreadsheet.

Dit is mijn code:
Code: 
DoCmd.OpenQuery "SiemensPO", acViewNormal, acEdit
DoCmd.TransferSpreadsheet (acExport), acSpreadsheetTypeExcel8, "Querynaam", "Z:\Test1.xlsx", False, "Sheet1!B15:J40", False

Heeft dit ermee te maken dat de query een variabele hoeveelheid records heeft en dat de range daarom ook variabel moet zijn?
In dat laatste geval zou ik niet weten hoe ik dat moet doen... ( ik heb wel andere ranges nog geprobeerd, met meer en minder kolommen/rijen maar het probleem blijft)

Alvast bedankt
 
Waarom open je in dit voorbeeld een query? Niet dat dat de foutmelding oplevert, want dat probleem ligt denk ik in de manier waarop je de Excel vult. Range is namelijk een Excel begrip, en geen Access.
 
Zoals ik als aangaf kan ik met het acces gedeelte weinig, maar dat is bij Octafish in goede handen.

Nog even terug kijkend naar de oorspronkelijk vraag, nl het overslaan van rijen die wel zouden moeten worden gewist
Daar zou je het volgende voor kunnen proberen.
Code: 
Exclapp.Rows(myRow).EntireRow.ClearContents

Je zit dan wel met een hoop lege regels, maar dat is na één sorteerslag weg.
 
@ Octafish,
Ik open de query, zodat de output snel op het scherm gecontroleerd kan worden, zonder dat het document geopend moet worden.
Mochten er fouten in zitten, dan kunnen die op deze manier eruit gefilterd worden (is een verplichting).

Kun je me een zetje in de goede richting geven wat betreft de range?

@ leofact,
Deze optie had ik reeds geprobeerd maar loste mijn probleem ook niet op. Ik bleef records houden die ik niet wilde...
 
Controleren van een export doe je vóórdat je de export uitvoert, lijkt mij. Ik zou althans eerst controleren, en bij een oké pas op de export knop klikken. Ik zie ook helemaal geen winst om dat te combineren. Maar goed, jij blijkbaar wel :)
Als ik deze procedure zou maken, dan zou ik dus alleen de gegegens exporteren die je nodig hebt; de rest zou er niet meer in zitten. Want dan hoef je in Excel ook niks weg te gooien. Alleen nog maar de file te openen, en de records uit de query toe te voegen. Bijvoorbeeld zo:
Code: 
Function ExportExcel()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim acRng As Variant
Dim xlRow As Integer

Dim qry As QueryDef
Dim rst As Recordset
    
    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Open("Z:\specifiek document.xlsx")
    Set xlWS = xlWB.Worksheets("BB Data")
    xlRow = (xlWS.Columns("A").End(xlDown).Row)
    'Of deze regel:
    ''xlRow = xlWS.Range("A65536").End(xlUp).Row + 1
    Set qry = CurrentDb.QueryDefs("Orderbevestigingen")
    Set rst = qry.OpenRecordset
    
    Dim c As Integer
    c = 1
    xlRow = xlRow + 1
    Do Until rst.EOF
        For Each acRng In rst.Fields
            xlWS.Cells(xlRow, c).Formula = acRng
            c = c + 1
        Next acRng
        xlRow = xlRow + 1
        c = 1
        rst.MoveNext
    Loop
    
rq_Exit:
    rst.Close
    Set rst = Nothing
    Set xlWS = Nothing
    xlWB.Close acSaveYes
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
      
End Function
Zoals je ziet in het voorbeeld, wordt er geen enkele range ingesteld, en geen enkele rij weggegooid.
Overigens vraag ik me af waarom je acSpreadsheetTypeExcel8 gebruikt;
Code: 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Orderbevestigingen", "Z:\specifiek document.xlsx", False, "Sheet2"
da's een behoorlijk oud excel format dat zeker geen xlsx ondersteunt. Daarvoor heb je toch echt acSpreadsheetTypeExcel12 nodig.
 
Hi Octafish,

Dankjewel voor de hulp! Ik kan erg goed overweg met excel (wat vast te zien is in mijn code), maar access is echt een ander verhaal.

De reden dat een document gecontroleerd wordt heeft te maken met het feit dat meerdere mensen Orderbevestigingen kunnen toevoegen. 1 iemand moet de orderbevestigingen voor deze leverancier echter specifiek controleren, omdat degene die het document daadwerkelijk verwerkt geen kennis over de materialen heeft.
Het is makkelijk om de output van de query te controleren dan zelf filters toe te passen / door de record heen te gaan om de regels te controleren.

Ik heb jouw code inmiddels geimplementeerd en aangepast.
Ik krijg echter een "Run-Time error 3061; Too few parameters. Expected 1.".
Het is voor mij echter onduidelijk waarom ik deze melding krijg, ik weet zeker namelijk dat zich records in de query bevinden...
 
De parameter vraag komt omdat er dan ergens in de query iets niet goed is gedefinieerd, waardoor de export niet weet waarop gefilterd (meestal gaat het om een filter) moet worden. Het probleem moet je dus in de query zoeken.
Overigens stel ik niet de controle zelf ter discussie, maar de manier waarop je de controle hebt ingebouwd in de export; daar zie ik dus geen enkel voordeel van :)
 
Da's een goede vraag, en die is zonder de query/database niet te beantwoorden :)
 
Om toch weer even terug stappen op het Excel gedeelte,
Gaat de macro wel goed als de geïmporteerde inhoud als waarden naar een ander werkblad wordt gekopieerd en daar de routine op wordt losgelaten?
Excel slaat uit zichzelf geen rijen over. Mogelijk gaat er bij het importeren iets mis in de range?
 
Dat zou je kunnen proberen; maak dan een tijdelijke tabel aan op basis van je selectiequery, en exporteer de tabel. Daar staan dan alleen vaste waarden in, en dat zou voor export geen probleem mogen zijn.
 
Hi Leofact,

Dankjewel voor de frisse blik op het probleem!
Ik heb de tabel handmatig naar excel gekopieerd en de macro voor het verwijderen vie een knop even laten lopen.
In eerste instantie heb ik hetzelfde probleem, als ik de macro echter opnieuw laat lopen gaan er wel weer records weg.
Ik heb het bijgehouden en nadat de macro 10x heeft gelopen heb ik de informatie die ik nodig heb...
De range loopt hij elke keer volledig door, dus dat is het niet.

Kunnen jullie hier iets mee?


@Edit
Ik heb net de macro iets aangepast zodat hij alle niet gerelateerde records niet verwijderd, maar markeert.
Dit doet hij echter wel meteen, in 1 keer dus, met alle niet gerelateerde records...
 
Laatst bewerkt:
Als het markeren goed gaat bereik je met clearcontents mogelijk nu wel het gewenste resultaat.

Zo niet. Is er een mogelijkheid om hier een gedepersonaliseerde versie te plaatsen?
 
Laatst bewerkt:
Yes, dat werkte Leofact :-)!
Clearcontents verwijdert inderdaad alle overbodige info.

Wat maakt het verschil dan precies voor excel tussen
Code: 
EntireRow.Delete
en
Code: 
EntireRow.Clearcontents
?

Is er nu nog een optie om de lege rijen, die ontstaan zijn door de .Clearcontents, te verwijderen?
Ik ga er nu namelijk niet vanuit dat
Code: 
 Selection.EntireRow.Delete
nu wel werkt...

Grt
 
He, da's een stap vooruit.
Een 100%verklaring heb ik niet.
Mijn idee is dat je de rijen stap voor stap doorloopt met de lus, maar de rijen veranderen voortdurend door de gewiste rijen, waardoor de lus de fout in gaat omdat er dan een hoop tegelijk gebeurt.



Je kunt de lege rijen kwijt raken met een sorteer actie.
Dat wordt zonder bestand wel even experimenteren, omdat je de macro vanuit Acces lanceert.
Bv.
ExcelApp.Sheet2.Range("A2", Range("Z65536").End(xlUp)).Sort Range("A1")

De bedoeling is dat de gehele gebruikte range wordt gevonden en gesorteerd.
Pas daarom "Z" aan als de laatste kolom in gebruik.
En A1 als gewenste kolom om op te sorteren.
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan