.TextFileFixedColumnWidths (from Ron de Bruin)

[Corey]

I stumbled across a great tool on this website (that was linked to Ron de
Bruin) for copying multiple text files into one Excel workbook. This could be
extremely useful to me. However, I'm stumped on altering the code for
assigning the column widths. Below is the code that was posted on his site.
With the instruction, it seems that the width can be set for each column.
When I open a single txt file I set the following column breaks: 4, 17, 36,
56, 76, 93, 104, 117, 130. However, I cannot get it to working with this
code. Please help!! Thanks!!

'Set the width for each column
..TextFileFixedColumnWidths = Array(5, 4, 8)

On a side note, I was able to write a different macro that opened and
formatted each txt file (about 40 of them), but the macro says it's too long
and cannot run. So, I had to break it down into two separate macros. I'm
hoping I can use the above method to get around using two macros.

 

[Corey]

Okay, with some additional trial and error, I was able to get this to work
with the following code:

..TextFileFixedColumnWidths = Array(4, 0, 13, 23, 18, 25, 12, 10, 12)

Only problem now is the trailing minus sign for negative numbers. There's
the option through the wizard to check this option, but I don't know the code
for including it in the Ron de Bruin macro. Anyone run into this yet? Thanks
in advance.

 

[Corey]

Nobody's responded yet, so I hope I haven't wasted anyones time...I figured
out my problem by adding the following code:

..TextFileTrailingMinusNumbers = True

However, I'm now stuck on something I didn't think would initially be a
problem. The txt files generated each month have the exact same name except
for the last 4 digits. How can I get the tab name to only be the first 8
digits (from the left) of the file name?

 

[Gord Dibben]

Corey

Adjust to suit.

Sub ChangeSign()
Dim Cell As Range
On Error Resume Next
For Each Cell In Selection. _
SpecialCells(xlConstants, xlTextValues)
If Right(Trim(Cell.Value), 1) = "-" Then
Cell.Value = CDbl(Cell.Value)
End If
Next
On Error GoTo 0
End Sub


Gord Dibben MS Excel MVP