Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
English German CSV issue
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;PageTimeou t=5;SafeTransactions=0;Threads=3;UserCommitSync=Ye s;" 'Prime Data Update myPrime = Worksheets(1).PivotTables("PrimeData2").CacheIndex ActiveWorkbook.PivotCaches(myPrime).Connection = myConnect ActiveWorkbook.PivotCaches(myPrime).MissingItemsLi mit = xlMissingItemsNone ActiveWorkbook.PivotCaches(myPrime).Refresh myPrime = Worksheets(1).PivotTables("PrimeData2").CacheIndex |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
English German CSV issue
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 wrote in message ups.com... 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;PageTimeou t=5;SafeTransactions=0;Threads=3;UserCommitSync=Ye s;" 'Prime Data Update myPrime = Worksheets(1).PivotTables("PrimeData2").CacheIndex ActiveWorkbook.PivotCaches(myPrime).Connection = myConnect ActiveWorkbook.PivotCaches(myPrime).MissingItemsLi mit = xlMissingItemsNone ActiveWorkbook.PivotCaches(myPrime).Refresh myPrime = Worksheets(1).PivotTables("PrimeData2").CacheIndex |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rename Worksheets From German to English Sheet1 | Setting up and Configuration of Excel | |||
this formulas is in English ... what is it in German? | Excel Discussion (Misc queries) | |||
German translation for english formulas | Excel Discussion (Misc queries) | |||
is it possible to covert formula commands from english to german? | Excel Programming | |||
Trouble with german / english dateformatting | Excel Programming |