MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Formatting data

  Asked By: Caleb    Date: Aug 12    Category: MS Office    Views: 741

I've got a program that open the files of a folder and apply them a
formatting. One file by sheet. The files are text. At first, it open
the file with "delimiter tab". At second, the columns A to L are
deleted. At third, the column A is formated with "delimiter
other ;". The pb is: "once the first file are made, the others files
are not made correctly. The formatting are bad. It seems that the
option "delimiter other ;" is applied at opening of the following

Who to avoid this?

Here's an extract of the program which is included in a loop which
skim the folder:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & rep & fic, Destination:=Range("A1"))
.name = proc
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Selection.Delete Shift:=xlToLeft
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar:=";", _
FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Selection.ColumnWidth = 18.71
Selection.NumberFormat = "0000000000"



2 Answers Found

Answer #1    Answered By: Fabian Jones     Answered On: Aug 12

This is a problem with excel. Whenever you open  a file  after setting text  to
columns, excel will apply the same text to columns  split for everything
thereafter whether using vba or not. The best thing is to add another step at
the end of your loop  which resets text to columns before looping to the next

Answer #2    Answered By: Clariss Ferrrari     Answered On: Aug 12

Indeed, I've written a second  loop. One loop  to open  the files  in
each sheet, another loop to format each sheet.

Didn't find what you were looking for? Find more on Formatting data Or get search suggestion and latest updates.