PDA

View Full Version : Solved: import empty column QueryTables thnks



abdelyasid
06-13-2010, 11:52 PM
Hi, Im trying to import some txt file with "PIPE" delimiters to excel worksheet but it does not imports a blank columns :think: .
I need to import all columns does not matter if they are empty.

I hope, you can help me




rutaTxt = "TEXT;" & rutaTxt

With ActiveSheet.QueryTables.Add(rutaTxt, celdaInicial)
.Name = "B_TXT"
.AdjustColumnWidth = False
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlMSDOS
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "|"
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(xlGeneralFormat, xlGeneralFormat, xlTextFormat, _
xlGeneralFormat, xlGeneralFormat, xlGeneralFormat, xlGeneralFormat, xlGeneralFormat, _
xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, _
xlTextFormat, xlDMYFormat, xlDMYFormat)

.Refresh

abdelyasid
06-14-2010, 12:14 AM
example file

NumFactura|Referencia1|Referencia2|Referencia3|Referencia4|Importe Bruto|Descuentos/Recargos|Importe Neto|Medio de Pago|Forma de Pago|Folio de Pago|Sucursal|Hora|Fecha Vencimiento|Fecha de Pago
72770|0000000000000000000000000000000101800225|001 |||$0.10|$0.00|$0.10|Ventanilla|Efectivo|13940139031268072770 |1394|09:17:01 a.m.|00/00/0000|19/05/2010
72770|0000000000000000000000000000000015103633|002 |||$0.10|$0.00|$0.10|Ventanilla|Efectivo|13940139032134072770 |1394|09:17:47 a.m.|00/00/0000|19/05/2010
72770|0000000000000000000000000000000016303828|003 |||$0.10|$0.00|$0.10|Ventanilla|Efectivo|13940139032836072770 |1394|09:18:30 a.m.|00/00/0000|19/05/2010
72770|0000000000000000000000000000000027605492|004 |||$0.10|$0.00|$0.10|Ventanilla|Efectivo|13940139033308072770 |1394|09:18:54 a.m.|00/00/0000|19/05/2010

htt... img20.imagevenue.com/img.php?image=98114_txt_122_22lo.JPG
htt..ww... easy-share.com/1910949476/file pipe delimited.txt
sorry for the 60 seconds :( to wait

p45cal
06-14-2010, 01:12 AM
I can't test quickly, but would it help to change
.TextFileConsecutiveDelimiter = True
to .TextFileConsecutiveDelimiter = False ?

abdelyasid
06-14-2010, 01:30 AM
Solved :thumb
Thanks a lot p45cal