Formatting imported text file

[Holley]

Hello again!! I am trying to import a .txt file into excel. It is a delimited file with “|”. I am trying to import this file into excel and format at the same time. I have code to do this when importing with a fixed width, but delimited is new to me. I have the code set up to select and import but the formatting is messing me up. All Columns except for AJ should be TEXT and AJ should be Currency. Can this be done with delimited as it is with fixed width? Is this something I would do during the import or would I need to format after its imported? Any suggestions would be most appreciated!!

 

[HighAndWilder]

Hello again!! I am trying to import a .txt file into excel. It is a delimited file with “|”. I am trying to import this file into excel and format at the same time. I have code to do this when importing with a fixed width, but delimited is new to me. I have the code set up to select and import but the formatting is messing me up. All Columns except for AJ should be TEXT and AJ should be Currency. Can this be done with delimited as it is with fixed width? Is this something I would do during the import or would I need to format after its imported? Any suggestions would be most appreciated!!

Can you post a good representative sample of the imported data using XL2BB for people to work on?

Fosmatting is normally left until the data is imported.

How do you want ti formatted?

Borders, font name, font size, headings or not, headings background, headings bold or not etc etc

 

[Holley]

Thanks for the reply! I will have to wait until this evening to upload through XL2BB, its blocked here. I just want the type of text to import. General, Date, Text, Skip column... this is usually done through the wizard, but I am trying to automate the process. I have it down when importing fixed with using Arrays, but delimited is new to me.

 

[Holley]

I think I would use it the same way.. just use the column #s vs the breaks - while still assigning the format in the Array...

 Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, _
        1)), TrailingMinusNumbers:=True

Am I over simplifying this?