Different querytable behaviour in Office 2003?

[Uwe M?ller]

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]

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.

 

[Uwe M?ller]

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

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