Help!

PC-Problemen?
De vrijwilligers van Oplossing.be zoeken gratis met u mee!

Hulp bij posten

Recente topics

Auteur Topic: Herstellen excel bestand  (gelezen 41623 keer)

0 leden en 4 gasten bekijken dit topic.

Offline Georgyboy

  • Ervaren lid
  • ***
  • Berichten: 314
  • Geslacht: Man
  • Oplossing.be
Herstellen excel bestand
« Gepost op: 16 augustus 2019, 10:59:47 »
Goedemorgen,

1)   Steeds meer gebruik ik in bestanden via OBDC in Excel (externe query van ERP Database).
Die bestanden zijn meestal erg groot (500.000 lijnen, 10 à 20 kolommen)
Via een filter worden rijen verwijderd (vb alles van jaar 2017, 2018, …)
Daar heeft excel het moeilijk mee (duurt zéér lang)
Meestal is dit ook een Tabel welke men steeds kan vernieuwen om weer de juiste gegevens te hebben.

2)   Excel de later terug openen geeft veel problemen
Wil openen en sluit weer af

Bij het maken van een kopie bestand, kan ik dit terug openen en zie ik herstelbestanden van het originele.

3)   +/- 500.000 lijnen en 3 kolommen = +/- 15 MB (excel)
Idem als tekstbestand

Hoeveel geheugen nemen vb 1000 lijnen in beslag ? of is dit zo niet mogelijk ?
(verschil tabel, opmaak, kolommen, aantal cellen met waardes ?)
(ook tekstbestand = 15 MB)

Alvast bedankt,

Groeten,
Georgyboy

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.129
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: Herstellen excel bestand
« Reactie #1 Gepost op: 16 augustus 2019, 12:20:26 »
Hallo Georgyboy,

Welke Excel-versie gebruik je?
De nieuwere versies kunnen veel grotere bestanden aan dan v.2003 of lager.

Daar je er toch zakelijk mee werkt, is het misschien goed om te overwegen de laatste nieuwe versie (2019) van Excel aan te schaffen ?

Ook je RAM-geheugen dient groot genoeg te zijn als je in Excel zeer grote bestanden wilt invoeren of bewerken.
12 GB lijkt me hier niet overdreven.

:) SoftAid :)             
Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: Herstellen excel bestand
« Reactie #2 Gepost op: 16 augustus 2019, 13:38:25 »
Hoi,

Eerst en vooral: ik mag vermoedelijk aannemen dat je ODBC bedoelt ipv OBDC?
En vervolgens maar hopen dat je hier iets mee kan:
Ik krijg daar namelijk ook regelmatig mee te maken met dat verschil dat ik bijna altijd sql-code in mijn vba-code implementeer waardoor ik niet enkel slechts de gewenste records en/of velden in mijn Excelfile binnen haal maar ook nog eens de mogelijkheid heb om rechtstreeks gegevens uit meerdere databasetabellen kan koppelen.
De beste manier om te achterhalen hoe je daar aan begint (vermits ik uit je vraag afleid dat je dat nog nooit heb gedaan): neem een macro op en haal dan eens een tabel op, en daarna kan je met die opgenomen code aan het spelen gaan.
En daarna... wil je nooit nog zonder deze methode.

Succes!
pitufo
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline Georgyboy

  • Ervaren lid
  • ***
  • Berichten: 314
  • Geslacht: Man
  • Oplossing.be
Re: Herstellen excel bestand
« Reactie #3 Gepost op: 16 augustus 2019, 16:49:49 »
Bedankt voor de antwoorden :)

@ softAid

PC eigenschappen, denk ok ?

@ pitufo

Inderdaad ODBC, was een beetje.......... mis :)

Ik ga misschien al eens proberen met de macrorecorder en zien wat dit geeft op 1 tabel,

Je maakt me curieus om te zien hoe dit beter werkt


Groetjes

Georgyboy

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.129
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: Herstellen excel bestand
« Reactie #4 Gepost op: 16 augustus 2019, 17:16:34 »
@ softAid
PC eigenschappen, denk ok ?

NEEEEE....
Welke Excel-versie gebruik je?

:) SoftAid :)             
Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline Georgyboy

  • Ervaren lid
  • ***
  • Berichten: 314
  • Geslacht: Man
  • Oplossing.be
Re: Herstellen excel bestand
« Reactie #5 Gepost op: 16 augustus 2019, 17:48:30 »
Excel 2013

oei
NEEEEE....

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.129
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: Herstellen excel bestand
« Reactie #6 Gepost op: 16 augustus 2019, 18:38:46 »
Excel 2013
dan zit je goed....

:) SoftAid :)             
Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: Herstellen excel bestand
« Reactie #7 Gepost op: 17 augustus 2019, 10:09:38 »
Hallo,

Ik sluit me integraal aan bij de bedenkingen over de Excel-versie, laat dat duidelijk zijn.

Maar: verlies daardoor vooral niet uit het oog dat je ook met de állerlaatste versie én met de krachtigste pc nooit in de buurt komt van de mogelijkheden en de snelheid die met mijn eerder voorgestelde methode te behalen valt. Daar kan Excel alleen maar van dromen.
Citaat
Die bestanden zijn meestal erg groot (500.000 lijnen, 10 à 20 kolommen)
Via een filter worden rijen verwijderd (vb alles van jaar 2017, 2018, …)
Daar heeft excel het moeilijk mee (duurt zéér lang)
Zelf praat ik over tabellen die dikwijls 10 keer (of méér) groter zijn. Met één simpele sql-instructie (niet op de achtergrond laten uitvoeren!) binnen de vba-code worden enkel de nodige gegevens in Excel binnengeknald nog voor je met je ogen kan knipperen.

Als je er niet uit komt mag je hier gerust je opgenomen macro posten (niet je Excel, want daar zal uiteraard niemand iets mee kunnen  ;) )

Ik kom nu wel weer in een periode terecht waarin ik veel minder tijd voor hobby's (zoals o.a. oplossing.be) zal hebben... dus (eventueel) beetje geduld (?)

Groeten,
pitufo
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline Georgyboy

  • Ervaren lid
  • ***
  • Berichten: 314
  • Geslacht: Man
  • Oplossing.be
Re: Herstellen excel bestand
« Reactie #8 Gepost op: 17 augustus 2019, 12:22:54 »
Dag pitufo,

jij bent toch een "krak" als ik dat mag zeggen waar ik ook zéér naar op kijk

inladen tabel (lukt niet nadien, loopt vast), inladen tabel gaat snel, bewerken is iets anders !
Sub Macro1()
'
' Macro1 Macro
'

'
    Application.CutCopyMode = False
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=XXXXXXXX-YYYYYY-SRV;ServerName=000.000.00.000.0000;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=XXXXXXXX   " _
        ), Array( _
        "      ;ClientVersion=12.00.134.000;CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252;" _
        )), Destination:=Range("$A$1")).QueryTable
        .CommandType = 0
        .CommandText = Array( _
        "SELECT Vrd.OudLokArtDatVolg, Vrd.Lokatie, Vrd.Artikel, Vrd.BeginVoorraadSt, Vrd.MutatieSt, Vrd.BeginVoorraadKg, Vrd.MutatieKg, Vrd.Soort, Vrd.DebCred, Vrd.Tekst, Vrd.Datum, Vrd.VolgNummer, Vrd.Partij," _
        , _
        " Vrd.PartijTekst, Vrd.Prijs, Vrd.VoorraadMutatieReden, Vrd.Reserve, Vrd.VerkoopArtikel, Vrd.ChargeNummer, Vrd.DouaneStatus, Vrd.PalletNummer, Vrd.InterneBoeking, Vrd.SoortMutatie, Vrd.Res2, Vrd.Res3" & Chr(13) & "" & Chr(10) & "" _
        , "FROM XXXXXXXX.Vrd Vrd")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tabel_Query_van_XXXXXXXX_YYYYYY_SRV"
        .Refresh BackgroundQuery:=False
    End With
End Sub

loopt vast op
.CommandType = 0
2 rijen verwijderd (gaat snel)
Sub Rijen_verwijderen()
'
' Rijen_verwijderen Macro
'

'
    ActiveSheet.ListObjects("Tabel_Query_van_XXXXXX_YYYYY_SRV").Range. _
        AutoFilter Field:=13, Criteria1:="6004"
    Rows("261034:507077").Select
    Selection.Delete Shift:=xlUp
End Sub

groetjes
Georgyboy

Offline Georgyboy

  • Ervaren lid
  • ***
  • Berichten: 314
  • Geslacht: Man
  • Oplossing.be
Re: Herstellen excel bestand
« Reactie #9 Gepost op: 17 augustus 2019, 12:57:14 »
Natuurlijk  :)
Citaat
dus (eventueel) beetje geduld (?)

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: Herstellen excel bestand
« Reactie #10 Gepost op: 17 augustus 2019, 14:25:17 »
Haha, we zijn dus in elk geval vertrokken !

Extra tip: je kan best altijd de code uitvoeren op een leeg werkblad.
In de regel met ActiveSheet.ListObjects.Add...(dus tot en met QueryTable) kunnen waarschijnlijk nog een aantal parameters worden verwijderd. Een beetje experimenteren kan nooit kwaad.
Je hebt de macro nu opgenomen, maar misschien nog niet opnieuw uitgevoerd. Als dat niet (goed) werkt zal je het met je ict-afdeling moeten opnemen, en die zullen je zeker kunnen zeggen hoe die voorgaande regel dan moet opgebouwd worden.

Zoals altijd zet de macrorecorder wel wat overbodigs in de code.
Al die 'arrays' mogen er zeker uit = zeer bevorderlijk voor de leesbaarheid.
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=XXXXXXXX-YYYYYY-SRV;ServerName=000.000.00.000.0000;" _
    & "ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=XXXXXXXX;ClientVersion=12.00.134.000;" _
    & "CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252", Destination:=Range("$A$1")).QueryTable
moet zeker genoeg (en zoals gezegd misschien nog te veel) zijn.

Tussen With en End With heb je heel waarschijnlijk maar 2 regels nodig: 'CommandText' en 'Refresh', die laatste dus zoals in mijn vorige post gezegd om te vermijden dat vba zou verder gaan terwijl dit commando op de achtergrond wordt uitgevoerd.

Bijvoorbeeld:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=XXXXXXXX-YYYYYY-SRV;ServerName=000.000.00.000.0000;" _
    & "ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=XXXXXXXX;ClientVersion=12.00.134.000;" _
    & "CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252", Destination:=Range("$A$1")).QueryTable
    .CommandText = "SELECT Vrd.OudLokArtDatVolg, Vrd.Lokatie, Vrd.Artikel, Vrd.BeginVoorraadSt, Vrd.MutatieSt, " _
        & "Vrd.BeginVoorraadKg, Vrd.MutatieKg, Vrd.Soort, Vrd.DebCred, Vrd.Tekst, Vrd.Datum, Vrd.VolgNummer, Vrd.Partij," _
        & " Vrd.PartijTekst, Vrd.Prijs, Vrd.VoorraadMutatieReden, Vrd.Reserve, Vrd.VerkoopArtikel, Vrd.ChargeNummer, " _
        & "Vrd.DouaneStatus, Vrd.PalletNummer, Vrd.InterneBoeking, Vrd.SoortMutatie, Vrd.Res2, Vrd.Res3 " _
        & "FROM XXXXXXXX.Vrd Vrd"
    .Refresh BackgroundQuery:=False
End With

Als je gegevens uit slechts één tabel ophaalt heb je niet voor elk veld "Vrd." nodig.
Hetzelfde resultaat behaal je dus met
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=XXXXXXXX-YYYYYY-SRV;ServerName=000.000.00.000.0000;" _
    & "ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=XXXXXXXX;ClientVersion=12.00.134.000;" _
    & "CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252", Destination:=Range("$A$1")).QueryTable
    .CommandText = "select OudLokArtDatVolg, Lokatie, Artikel, BeginVoorraadSt, MutatieSt, " _
        & "BeginVoorraadKg, MutatieKg, Soort, DebCred, Tekst, Datum, VolgNummer, Partij," _
        & "PartijTekst, Prijs, VoorraadMutatieReden, Reserve, VerkoopArtikel, ChargeNummer, " _
        & "DouaneStatus, PalletNummer, InterneBoeking, SoortMutatie, Res2, Res3 " _
        & "from XXXXXXXX.Vrd"
    .Refresh BackgroundQuery:=False
End With

Uiteraard hoef je niet alle velden op te halen (by the way, alle velden kan met "select * from XXXXXXXX.Vrd")
Bijvoorbeeld:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=XXXXXXXX-YYYYYY-SRV;ServerName=000.000.00.000.0000;" _
    & "ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=XXXXXXXX;ClientVersion=12.00.134.000;" _
    & "CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252", Destination:=Range("$A$1")).QueryTable
    .CommandText = "select Artikel, VolgNummer, Partij from XXXXXXXX.Vrd"
    .Refresh BackgroundQuery:=False
End With

Citaat
    ActiveSheet.ListObjects("Tabel_Query_van_XXXXXX_YYYYY_SRV").Range. _
        AutoFilter Field:=13, Criteria1:="6004"
    Rows("261034:507077").Select
    Selection.Delete Shift:=xlUp

En hier wordt het helemaal plezant. Bouw je filter in in de sql-code:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=XXXXXXXX-YYYYYY-SRV;ServerName=000.000.00.000.0000;" _
    & "ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=XXXXXXXX;ClientVersion=12.00.134.000;" _
    & "CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252", Destination:=Range("$A$1")).QueryTable
    .CommandText = "select Artikel, VolgNummer from XXXXXXXX.Vrd where Partij = 6004"
    .Refresh BackgroundQuery:=False
End With

Héél benieuwd naar je vorderingen  :)

En niet panikeren als het niet meteen lukt, ik heb het zelf ook gewoon 100% al proberend gevonden...

Groeten,
pitufo
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline Georgyboy

  • Ervaren lid
  • ***
  • Berichten: 314
  • Geslacht: Man
  • Oplossing.be
Re: Herstellen excel bestand
« Reactie #11 Gepost op: 17 augustus 2019, 15:23:25 »
Wat een uitgebreide info pitufo,
heb wel méér geduld hoor  ;D (tof dat je dit zo aanpakt !)
dit is helemaal je ding hé !

voor mij ineens veel info, maar neem aan dat je er ook zo aan begonnen was, maar dan zonder de info die je reeds geeft  ;)

PS heb gevoelige info verborgen met XXX en YYYY en 0000.000...….
DSN=XXXXXXXX-YYYYYY
ServerName=000.000.00.000.0000
FROM XXXXXXXX.Vrd Vrd"

Dit zijn maar 2 rijen uit de méér dan 500.000 via een filter
Citaat
.CommandText = "select Artikel, VolgNummer from XXXXXXXX.Vrd where Partij = 6004"

Offline Georgyboy

  • Ervaren lid
  • ***
  • Berichten: 314
  • Geslacht: Man
  • Oplossing.be
Re: Herstellen excel bestand
« Reactie #12 Gepost op: 17 augustus 2019, 16:54:28 »
Hier voorbeeld hoe het nu gaat,

Via OBDC naar excel = groot bestand en dan in excel filteren, wat lukt maar neemt veel tijd op grote bestanden.

Hier gegevens uit 1 tabel, maar meestal uit meerdere tabellen

Groetjes,
Georgyboy

Offline pitufo

  • Ambassadeur
  • *****
  • Berichten: 1.343
  • Geslacht: Man
Re: Herstellen excel bestand
« Reactie #13 Gepost op: 17 augustus 2019, 17:43:51 »
Hoi,

Zelf vind ik het, minstens in de ict-wereld, héél nuttig om de dingen juist te benoemen.
Citaat
Dit zijn maar 2 rijen uit de méér dan 500.000 via een filter
Citaat
    .CommandText = "select Artikel, VolgNummer from XXXXXXXX.Vrd where Partij = 6004"
Het was uiteraard maar een voorbeeld. Daaruit kunnen we met zekerheid afleiden dat het 2 kolommen zijn, hoeveel rijen zal uiteraard afhangen van hoe dikwijls 6004 voorkomt in het veld 'Partij'.

Dat je servername en dergelijke had gewijzigd had ik heus wel door, hoor. Was in mijn voorbeelden gewoon doorgegaan met de gekregen code.

Uit je Word-document:
Citaat
Hier reeds een filter op artikel 1260101 (natuurlijk zou dit zeer omslachtig zijn om dit telkens zo te doen

In feite kan alles wat je nu stap voor stap in Excel doet vervat zitten in je vba-code.
Met beide gegevens in gedachten kan je dan komen tot bv:
.CommandText = "select (veld) [,veld]... from XXXXXXXX.Vrd where artikel = 1260101"
Wat zien we verder? Na ".CommandText =" staat er gewoon... een tekst!
Dat impliceert dat je die niet letterlijk in je code moet schrijven. Je kan daar perfect variabelen gebruiken (we zijn al aan les 2!).
De user kan om een artikel worden gevraagd via een in te vullen Excel-cel, een inputbox, een userform, noem maar op.
Dat artikelnummer steken we binnen onze 'gewone' vba in een variabele (ik noem die even art_nr om geen verwarring te stichten).
En dan kan je code worden:
    .CommandText = "select ... from XXXXXXXX.Vrd where artikel = " & art_nr
Citaat
Hier gegevens uit 1 tabel, maar meestal uit meerdere tabellen
Inderdaad, precies wat ik in mijn eerste reactie al meldde, maar misschien eerst leren stappen(?), een marathon lopen kan later altijd nog.
Wat ik daarom eerst graag zou zien is dat je zonder langs 'Gegevens/Van andere bronnen...' te passeren een ophaling van, voor mijn part willekeurige, gegevens zou realiseren, dus uitsluitend met vba.

Citaat
dit is helemaal je ding hé !
Ja en neen, ik heb zo meerdere dingen, ook 'gewoon' Excel en 'gewoon' vba, om maar iets te noemen, om nog maar te zwijgen over alles wat ik buiten de ict-wereld uitspook  ;)

Nog veel experimenteerplezier!
pitufo
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline Georgyboy

  • Ervaren lid
  • ***
  • Berichten: 314
  • Geslacht: Man
  • Oplossing.be
Re: Herstellen excel bestand
« Reactie #14 Gepost op: 17 augustus 2019, 18:25:34 »
Nu begint het hé

Citaat
Sub

End Sub

Maar alles daartussen

Citaat
t is all niet dat oar snien, 't is all die kruljes legn
(als je aan iets begint, moet je het ook kunnen afwerken) ja, Georgyboy  ;D


 


www.combell.com