[Excel/VBA] Exporteren naar CSV, onafhankelijk van locale

[Reinier]

Ik heb een aantal Excelbestanden die via ons intranet (deels via Citrix, deels via gewone pc's) beschikbaar zijn voor een stuk of 50 gebruikers. Deze bestanden bevatten een hele hoop macro's. Een van de functies is het exporteren van wat data (een selectie op een van de werkbladen) naar CSV. Deze exportfiles worden vervolgens batchgewijs ingelezen in SQL (via een DTS-package).

Het opslaan als CSV-bestand gaat gewoon zo:

Visual Basic:

Sheets("WISSEL").Select
ActiveWorkbook.SaveAs Filename:=  CSVnaam, FileFormat:= xlCSVMSDOS

En daar loop ik tegen een probleem aan. De DTS-package verwacht de puntkomma als scheidingsteken, maar bij het exporteren wordt de landinstelling (locale) van de gebruiker aangehouden. In sommige gevallen levert dat een komma als scheidingsteken op en m'n package struikelt daar nogal over

Mijn vraag is of iemand weet hoe je kan opslaan als CSV waarbij de landeninstelling genegeerd wordt, of expliciet op Nederlands/Europees (of whatever, zolang het maar een puntkomma gebruikt) ingesteld wordt. Misschien via FileFormat?

Google levert weinig nuttigs op en ook op GoT vind ik geen antwoord. Er zijn wel wat topics zoals dit, maar ik kan daar niet zoveel mee. En er is wel een workaround: regel voor regel door de huidige selection lopen en achtereenvolgens elke waarde plus een puntkomma naar het textbestand schrijven, maar dat levert een enorme perfomanceverslechtering op (vanwege het loopen). Deze workaround staat hier.

Wie helpt mij

[onkl]

Misschien een API call om de locale tijdelijk goed te zetten? Ehh, kon dat? In VBA? Ik dacht dat er wel functionele truc was.
Oh ja, hier
Ik weet alleen niet of dit werkt binnen een Citrix omgeving.

[Boss]

Misschien kan je hier wat mee:

Visual Basic:

Application.DecimalSeparator = "."
Application.ThousandsSeparator = ","
Application.UseSystemSeparators = False

en dan met de parameters die voor jou van toepassing zijn!

[alexbl69]

Even een kickje voor dit (nog steeds bestaande) probleem.

Als ik vanuit Excel een bestand opsla als .CSV, wordt als scheidingsteken de punt-komma ';' en als decimaal scheidingsteken de komma ',' gebruikt. Precies zoals ik het wil.

Doe ik dit echter vanuit VBA, dan worden de punt-komma's gewijzigd in komma's, en de decimale komma's worden punten (123.12 ipv 123,12).

De door mij gebruikte code is:

code:

ActiveWorkbook.SaveAs filename:="test.csv", FileFormat:=xlCSV, CreateBackup:="false"

Nu is het grappige dat er wel een oplossing in de help staat. Namelijk de optionele parameter 'Local'. Bij het opslaan door VBA worden standaard de landinstellingen van VBA gebruikt (Engels-Amerikaans). Door de toevoeging van 'Local:=True' zou de instelling van Windows moeten worden gepakt (Nederlands, dus correct). Maar het treurige is dat toevoeging van deze parameter een foutmelding geeft. (Fout 1004 - Methode SaveAs van object _Workbook is mislukt).

Het ligt mijns inziens niet aan de syntax, want als ik 'Local:=False' gebruik lukt het opslaan wel, maar dan wel met de verkeerde instellingen.

Heeft iemand deze noot al eens eerder gekraakt?

[alexbl69]

Grappig, op mijn XP computer (werk) werkte 'Local:=true' niet, op m'n Vista PC thuis dus wel!

[Reinier]

Mijn oorspronkelijke probleem is al lang en breed opgelost door in plaats van het werkblad op te slaan als CSV-file dit stukje VBA te gebruiken:

VBScript:

Public Sub ExportToTextFile(FName As String, _
    Sep As String, SelectionOnly As Boolean)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String


Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
    With Selection
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
Else
    With ActiveSheet.UsedRange
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
End If

Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = ""
        Else
            CellValue = Cells(RowNdx, ColNdx).Value
            If IsNumeric(CellValue) Then
                CellValue = Replace(CellValue, ",", ".")
            End If
        End If
        WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Close #FNum

End Sub

Werkt prima

[alexbl69]

Great! Dit is nog veel beter!

[Reinier]

Het komt trouwens hier vandaan