English German CSV issue

[davidbruce17]

Hi,

I'm fairly new to this so please go easy

I've created a report using excel pivot tables which connect to a CSV
file, I have automated the updating of the tables via VBA and
everything works as expected here in the UK. I transfered this report
to a colleague in Germany and he gets a runtime error 1004 - too few
parameters.

We both use Excel XP but English and German versions respectively.

I suspect that when refreshing the pivotcache connection string and
reading the csv file, German Excel is not seeing the commas as
seperators hence the too few parameters message.

Is there a work around that will force the German excel to read the CSV
file as it would in the UK version of excel?

Many thanks,

David.

My VBA code is quite simplistic and errors on the
ActiveWorkbook.PivotCaches(myPrime).Refresh command:

Sub Data_Refresh()

Dim myConnect As String
Dim myPrime As Integer
Dim myApp As Integer

'Prime Data Query String
myConnect = "ODBC;CollatingSequence=ASCII;DBQ="
myConnect = myConnect & ActiveWorkbook.Path
myConnect = myConnect & ";DefaultDir="
myConnect = myConnect & ActiveWorkbook.Path
myConnect = myConnect & ";Driver={Microsoft Text Driver (*.txt;
*.csv)};DriverId=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"

'Prime Data Update
myPrime = Worksheets(1).PivotTables("PrimeData2").CacheIndex
ActiveWorkbook.PivotCaches(myPrime).Connection = myConnect
ActiveWorkbook.PivotCaches(myPrime).MissingItemsLimit =
xlMissingItemsNone
ActiveWorkbook.PivotCaches(myPrime).Refresh
myPrime = Worksheets(1).PivotTables("PrimeData2").CacheIndex

 

[Jean-Yves]

Hi David,

I posted a question about using ADO to query a text file and the possibility
to indicate the delimiter.
After a long search :
First query will using the default delimiter as specified in the regional
settings
To force/recognise the delimeter it is "enough " to create a text file name
"schema.ini"
wich can contain the parameter/attributes to be used.
This file has to be located in the ssame directory as the text file.
Google to microsoft support schema.ini Text file driver ODBC
It works great and easy to use, once you know you need one.

Regards
Jean-Yves