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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

"Rob van Gelder" <[email protected]> wrote in message news:<[email protected]>...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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