Category Archives: Excel

Referentie naar excel in een VBA project

Als ik met VBA werk in Access heb ik bijna altijd een verwijzing nodig naar Excel. Ik importeer en exporteer nogal veel data met excel.
Dit doe je eenvoudig door in de VBA editor via het menu Uitvoeren –> verwijzingen een vinkje te plaatsen bij de excel object Library.
Er is echter een probleem wanneer je bvb op de ene pc office 2010 (office14) draaien hebt en op een andere office 2013 (office15).
Immers, wanneer je de vba code op de off15 bewerkt dan zal de referentie naar excel deze zijn naar de excel15 object library. Hierdoor zal het vba project niet werken op de office14 computer.
Oplossing: kopieer de excel object library vanop de computer met de laagste office versie en plaats die op de pc met de hoogste versie. Om het overzichtelijk te houden in mijn directories plaatste ik dus de excel14 object lib in de map c:/program files(86)/microsoft office/office14 dir. Indien deze nog niet bestaat is het handig deze aan te maken. Zo dien je, in het vba project, niet te gaan browsen naar de plaats waar je de library neerplaatste.

Excel: Compileerfout ThisWorkBook

Een klant meldt mij dat er bij het openen van Excel steeds een boodschap komt die meldt:
Compileerfout ThisWorkBook.
Vervolgens klikt de klant op OK en kan gewoon verder werken in Excel.
Voor de klant stoorde het niet echt maar zo een dingen, die wil ik dan zeker opgelost zien te krijgen.
Op één of andere manier zorgde de Eurotool, die standaard in excel 2003 geïntegreerd zit ervoor dat die melding verscheen.

Je kan die tool, een invoegtoepassing, dus maar beter uitschakelen. Dit kan via het menu extra, vervolgens kies je voor invoegtoepassingen en daar doe je het vinkje weg bij ‘Hulpprogramma’s voor de Euro’.

Vanaf nu start de klant dus terug met een bugvrije excel op. En ik kreeg daar een pilsje voor.

Excelsheets in batch naar pdf afdrukken

Wat kostte het mij toch een moeite om verschillende sheets uit één workbook naar pdf af te drukken.
Het afdrukken via de menuopdracht lukte perfect. Ook toen dat ik dit via een macro automatiseerde.
Nadeel was dat je steeds de naam van het bestand diende op te geven in de ‘opslaan als’ box.

Ik dacht het te doen werken met het prtofilename attribuut van het printout commando.
Mijn pagina’s werden opgeslagen in de juiste directory met de juiste naam (de naam van de pagina) doch als ik ze wou openen kreeg ik een foutmelding van acrobat reader. De bestanden waren ook merkelijk groter (tot 3 maal) dan de bestanden bij een individuele afdruk.

Maar ik drijf door, zo een dingen wil ik dan echt wel kunnen oplossen.
En opeens vond ik dan pdfCreator. Ik tweakte de code die ik vond op excelguru en creëer vanaf nu pdf bestanden in batch vanuit Excel.

Excel en #N/B (#N/A)

In excel vind ik het een vies gegeven dat we een #N/B = niet beschikbaar(in het Engels een #N/A = not available) waarde terugkrijgen. Hierdoor worden eventuele verdere functies zoals sommen niet mooi afgemaakt.

De fout komt er doordat bvb een vertikale opzoeking geen resultaat oplevert.
De weergave van de #N/B kan onderdrukt worden dmv de functie ISNB

Bvb:

=ALS(ISNB(VERT.ZOEKEN(B5;'[MyOtherExcelSheet.xls]Pagina1'!$A$3:$B$212;2;0));"";VERT.ZOEKEN(B5;'[MyOtherExcelSheet.xls]pagina1'!$A$3:$B$212;2;0))

In excel 2007 kan je gebruik maken van als.fout(VERT.ZOEKEN(B5;'[MyOtherExcelSheet.xls]Pagina1'!$A$3:$B$212;2;0));"")

Een recordset snel naar excel exporteren

Met visual basic kan je natuurlijk door een ganse recordset loopen en record per record naar excel gaan wegschrijven.
Gemakkelijker gaat het als je de recordset in één keer, in zijn geheel exporteert naar excel.

Dim RS As New ADODB.Recordset
CREATERECORDSETONSERVER RS, dbOCMW ' function to create a recordset on the serverside

RS.Open "select * from MyQuery where jaar = " & cmbJaar.Text

'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer

iNumCols = RS.Fields.Count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = RS.Fields(i - 1).Name
Next

'Add the data starting at cell A2
oSheet.Range("A2").CopyFromRecordset RS

'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With

oApp.Visible = True
oApp.UserControl = True

'Close the Database and Recordset
RS.Close

In a module we have
Sub CREATERECORDSETONSERVER(rs)
Set rs = New ADODB.Recordset
rs.ActiveConnection = db ' is the active connection with the database
rs.CursorLocation = adUseServer
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
End Sub

[tags]visual basic, excell, microsoft office[/tags]

Aantal rijen te weten komen in excel via visual basic6

Een gebruiker van een programma weet altijd graag of het programma met een actie bezig is. Handig om het verloop van een proces aan te duiden is natuurlijk een progressiebar. Maar die progressiebar moet waarden hebben om enigszins een juiste weergave van een proces weer te geven.

Stel je wil een excel sheet importeren in een database. In die excel sheet staan er x aantal rijen ingevuld.
Hoe kan je nu de maximumwaarde aan de progressiebar geven (zijnde het aantal ingevulde rijen in de excel sheet);
Je zou natuurlijk een for next (zolang cell(i,1) <> “”) kunnen gebruiken maar op zich heb je hier dan ook een progressiebar nodig omdat een grote excel sheet wel eventjes kan loopen.

Ik maak gebruik van het volgende
dim cnt as double
Dim xlTmp As Excel.Application
Set xlTmp = New Excel.Application
cnt = xlTmp.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

[tags]visual basic, excel[/tags]

Excel en Visual basic

Een excel bestand openen en opslaan als txt bestand met visual basic. Dit diende ik te verwezenlijken in navolging van wat ik hier diende te schrijven.
Klant vond het nogal omslachtig (moeilijk) om het bestand te openen en dan via bestand – opslaan als – tekstbestand, te moeten werken.
Oplossing .

If Dir(App.Path & "/Betalingen.xls") <> "" Then
Dim xlTmp As Excel.Application
Set xlTmp = New Excel.Application
xlTmp.Workbooks.Open App.Path & "/Betalingen.xls"
xlTmp.ActiveWorkbook.SaveAs App.Path & "/Betalingen.txt", xlText
xlTmp.Workbooks.Close

Else
MsgBox "Het excel bestand werd niet gevonden", vbInformation + vbOKOnly
End If

[tags]excel, visual basic[/tags]

Importeren van een excel sheet via visual basic

Tijdje geleden dat ik hier eens op dit blogje was. Het lijkt alsof ik de afgelopen maand niet gewerkt heb, maar niets is minder waar. Ik heb gewoon geen reden gehad om hier te posten. Het liep allemaal te makkelijk 🙂 .

Verleden week kreeg ik de vraag van een klant om een excel sheet, afkomstig van een export uit hen boekhouding, via VB te importeren in een access database. Makkie dacht ik bij mezelf toen de klant mij die opdracht gaf. Voorheen konden ze een plat tekstbestand exporteren dus ik had al een routine zitten dacht ik om dat excel bestandje in te lezen. Maar, hmm, excel is als het ware ook een database en ik kan dat bestand dus niet zomaar openen en uitlezen.

Wat heb ik dan gedaan. Excel sheet openen, menu bestand aanklikken – opslaan als – tekstbestand (tabgescheiden). Voila routine kan mits enkele aanpassingen wel gebruikt worden. Nu ja, enkele – een gans pakje – maar dat deert me niet. Ik heb geen verder zoekwerk naar hoe ik een excel sheet via VB in access kan krijgen. Er is uiteraard de mogelijkheid om een connectie naar de excel sheet te maken en die dan als tabel te importeren in access. Maar dat heb ik dus niet nodig. Het excel bestand hetwelk ik aangeleverd krijg is om bestaande gegevens in een bestaande tabel (facturen) aan te passen.

[tags]excel, visual basic, import[/tags]