[VBS] Recordset efficient naar Excel exporteren

[Mike Jarod]

Vraagje

Ben bezig met een VBS (dus geen VBA!) script dat connectie maakt met een Oracle DB en hier SQL select queries op afvuurt. Vervolgens moet de uitkomst van die queries geexporteerd en gemaild worden.

Nu ben ik niet zo'n ster in dit soort dingen, maar met zoeken op Google kom je een heel eind. Eigenlijk werkt alles al, alleen moet het geoptimaliseerd worden. Daar wil ik jullie hulp voor vragen.

Op het moment dat ik ga exporteren, heb ik 2 opties:
1. Plain text (tab seperated), dit werkt al goed en snel
2. Excel

Excel heeft de voorkeur omdat de lijst als werklijst voor gebruikers gebruikt gaat worden, en CSV achtige dingen probeer ik dan te vermijden.

Hier een stuk code dat de inhoud van de 'SQL uitkomst variabele' (QueryResultaat) naar excel gooit:

Visual Basic:

'nieuw excel opject maken
Set ExcelObject = createobject("Excel.Application")   

'nieuw werkboek en sheet maken
ExcelObject.Workbooks.add
Set HuidigExcelBlad = ExcelObject.Sheets.Add()

'alle cellen op tekst zetten (dit is perse nodig)
Set ExcelSelectie = HuidigExcelBlad.Range("A1:IV65536")
ExcelSelectie.Numberformat  = "@"

'naar 1e record gaan van query resultaat variabele
QueryResultaat.MoveFirst

'kolomkoppen en velden naar excel kopieren
  Set Rng = HuidigExcelBlad.Range("a1")
  j = 0
  While j < QueryResultaat.Fields.Count and j < 256
    Rng.Offset(0, j).value = QueryResultaat.Fields(j).name
    j = j + 1
  WEnd
  i = 1
  While Not QueryResultaat.EOF and i <  65536
    Set Rng = Rng.Offset(1)
    j = 0
    While j < QueryResultaat.Fields.Count and j < maxCols
      Rng.Offset(, j).Value = QueryResultaat.Fields(j).Value
      j = j + 1
    WEnd
    QueryResultaat.MoveNext
    i = i + 1
  WEnd
  HuidigExcelBlad.Columns.AutoFit

'save en sluiten
ExcelObject.DisplayAlerts = False
ExcelObject.ActiveWorkbook.SaveAs("c:\vbs\exceltest.xls")
ExcelObject.Quit

Nu heb ik de volgende vragen:

• Deze query is retetraag omdat ie alle cellen per stuk gaat vullen. 30.000 records duurt veel te lang, hoe kan ik dit sneller doen?
• (regel 9) Hoe kan ik efficienter alle cellen de opmaak tekst geven?
• (regel 5) Waarom moet ik perse nieuwe workbooks en sheets aanmaken? Ze bestaan immers al als excel 'leeg' start, ik krijg het niet voor elkaar om de bestaande sheets aan te spreken (let op het bestand bestaat nog niet, wordt aangemaakt in dit script)
• Ik heb de grootste moeite met het instellen van de properties (bv dikgedrukt etc) van de cellen. De documentatie van Microsoft vind ik echt bagger, of ik zoek niet goed. Als ik bv in Excel een macro opneem, celeigenschappen aanpas etc, dan kan ik de VBA code niet 1:1 gebruiken in VBS, althans dat lukt mij niet. Weet iemand een goede bron waar ik alle eigenschappen van cellen/ranges/sheets etc kan vinden?

Alvast bedankt

[Gomez12]

Allereerst. Ik weet niet of dit echt zo werkt, maar dit zijn theoretische verbetringen.

Mike Jarod schreef op zaterdag 11 maart 2006 @ 19:09:
Nu heb ik de volgende vragen:

• Deze query is retetraag omdat ie alle cellen per stuk gaat vullen. 30.000 records duurt veel te lang, hoe kan ik dit sneller doen?

Maak batches van ipv per cel iets te vullen. Iets in de trant van regel 18 weghalen en dan regel 19 iets maken van

Visual Basic:

Rng.Offset(0, QueryResultaat.Fields.Count).value = QueryResultaat.Fields().name

Zodat je in 1 keer een heel gebied vult.

• (regel 9) Hoe kan ik efficienter alle cellen de opmaak tekst geven?

Niet ( of als het echt langzaam gaat moet je een leeg excell bestand aanmaken met alle layout die je hebben wilt, deze openen en dan alle data erin kopieren en dan opslaan onder nieuwe naam, hoef je helemaal geen layout meer te doen in je script )

• (regel 5) Waarom moet ik perse nieuwe workbooks en sheets aanmaken? Ze bestaan immers al als excel 'leeg' start, ik krijg het niet voor elkaar om de bestaande sheets aan te spreken (let op het bestand bestaat nog niet, wordt aangemaakt in dit script)

Omdat ze nog niet bestaan als je excell op deze manier opstart, als je excell opstart vanaf het bureaublad dan opent hij ze alleen maar omdat je excel opstart zonder dat er ergens iets ingesteld is.

• Ik heb de grootste moeite met het instellen van de properties (bv dikgedrukt etc) van de cellen. De documentatie van Microsoft vind ik echt bagger, of ik zoek niet goed. Als ik bv in Excel een macro opneem, celeigenschappen aanpas etc, dan kan ik de VBA code niet 1:1 gebruiken in VBS, althans dat lukt mij niet. Weet iemand een goede bron waar ik alle eigenschappen van cellen/ranges/sheets etc kan vinden?

Zoals ik al eerder zei kan je dit ook voorkomen door van te voren een leeg excell bestand aan te maken en hier de layout in te zetten.
Maar volgens mij moet je vba code gewoon kunnen gebruiken zolang je als 1e object maar gebruik maakt van huidigexcellblad ipv wat de macro editor er als 1e voorzet.

[Mike Jarod]

Hee bedankt Zal er straks eens induiken.