Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different querytable behaviour in Office 2003?
Hi!
I've been using a querytable to import a csv-file into a worksheet with Office 2k. The text file contains blank lines which need to be preserved for several reasons. After upgrading to Office 2003, those blank lines disappear after the call to qt.Refresh. Here's the whole macro: Set qt = wb.Worksheets(1).QueryTables.Add("Text;" + "fred.csv", [A1]) With qt .Name = p .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFileThousandsSeparator = "." .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = True .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileDecimalSeparator = "," .Refresh BackgroundQuery:=False End With Is it that I'm to dumb to find the answer in the VBA Help? Regards, Uwe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different querytable behaviour in Office 2003?
I run Office 2003
I created a dummy CSV file with the a b c d e f on separate lines separated by a blank line. That worked ok and the blank lines came through when using the code you supplied. What I tried doing is saving my CSV file in Unix format. This means instead of Windows format which ends a line as CR LF, it ends a line as just LF It imported without blank lines. -- Rob van Gelder - http://www.vangelder.co.nz/excel "Uwe M?ller" wrote in message om... Hi! I've been using a querytable to import a csv-file into a worksheet with Office 2k. The text file contains blank lines which need to be preserved for several reasons. After upgrading to Office 2003, those blank lines disappear after the call to qt.Refresh. Here's the whole macro: Set qt = wb.Worksheets(1).QueryTables.Add("Text;" + "fred.csv", [A1]) With qt .Name = p .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFileThousandsSeparator = "." .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = True .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileDecimalSeparator = "," .Refresh BackgroundQuery:=False End With Is it that I'm to dumb to find the answer in the VBA Help? Regards, Uwe |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different querytable behaviour in Office 2003?
"Rob van Gelder" wrote in message ...
I run Office 2003 I created a dummy CSV file with the a b c d e f on separate lines separated by a blank line. That worked ok and the blank lines came through when using the code you supplied. What I tried doing is saving my CSV file in Unix format. This means instead of Windows format which ends a line as CR LF, it ends a line as just LF It imported without blank lines. My CSF files are created on a Linux machine and therefor by default only have LF line endings. After I modified the script to terminate lines with CR LF, everything went fine. Thanks for the really great help, Rob! -- Rob van Gelder - http://www.vangelder.co.nz/excel "Uwe M?ller" wrote in message om... Hi! I've been using a querytable to import a csv-file into a worksheet with Office 2k. The text file contains blank lines which need to be preserved for several reasons. After upgrading to Office 2003, those blank lines disappear after the call to qt.Refresh. Here's the whole macro: Set qt = wb.Worksheets(1).QueryTables.Add("Text;" + "fred.csv", [A1]) With qt .Name = p .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFileThousandsSeparator = "." .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = True .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileDecimalSeparator = "," .Refresh BackgroundQuery:=False End With Is it that I'm to dumb to find the answer in the VBA Help? Regards, Uwe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Very annoying behaviour of Excel 2003 | Excel Discussion (Misc queries) | |||
strange behaviour in Excel 2003 when importing XML | Excel Discussion (Misc queries) | |||
Upgrading from Office 2003 Small Bus to Office 2003 Pro | Setting up and Configuration of Excel | |||
Office 2000/Office 2003 Excel not printing landscape vise versa | Setting up and Configuration of Excel | |||
Problem sharing spreadsheet between Office10 (Office XP) and Office 11 (Office 2003) | Excel Programming |