Reset Delimiter when opening text file

[KAM]

I am opening a text file using excel vba:

Workbooks.OpenText FileName:=MyFileName, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2))

The default delimiter is tab. In the instance above, I am setting the
delimiter to semicolon.

My problem is that when the user would like to open another text file on
their own, the default settings in the open text file screen do not "reset"
back to the tab. They stay with the semicolon as being the delimiter.

Is there any way I can reset the delimiter in my code?

Thanks!

 

[Dave Peterson]

Record a macro when you do this:
Find an empty cell (lastcell.offset(1,1))
put some junk text into it.
do data|text to columns
choose all the settings you want (including delimiters)
(make sure you didn't use any of those delimiters in that cell)
finish up the wizard
clean up that cell

Since excel likes to help by remember the settings that you used last, you just
told it to remember the settings that you like best.

Before you ask, I don't think that there's a way to save the user's settings
before you start, then reapply them.

I tweaked my recorded code so it ended up like:

Dim myCell As Range

Set myCell = Activesheet.Cells.SpecialCells(xlLastCell).Offset(1, 1)

With myCell
.Value = "asdf"
.TextToColumns Destination:=.Cells, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1) ', TrailingMinusNumbers:=True
.ClearContents
End With

You could even turn off all the delimiters (all False) so that no box is
checked.

 

[KAM]

Thanks for the response. I was afraid it was going to be something like that.
I am automating some processing for a production environment and I really
hate to alter files and delete info, but it looks like I will have no choice,
if this is what I want to do.

Maybe I will create an empty workbook, perform the "cleanup" and then delete
the file.

You are right, I was looking for a way to trap the settings and had spent
some time looking before I had posted. I won't waste my time looking for
something that is not there! Thanks!

 

[KAM]

Now I have another problem with "resetting" the delimiters.
Works like a charm if I am using Tab, Comma or Semicolon as delimiters, BUT..

One of the ways I am running OPENTEXT is using the vertical bar (|) as the
OtherChar delimiter:
Workbooks.OpenText FileName:=MyFileName, Origin:=xlWindows, _
StartRow:=1, 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))

When I run the TexttoColumns to reset the data as Dave suggested, I am able
to turn Other to False (so the Other box will not be checked), but I cannot
seem to remove the actual character from the Other Character box. It does not
matter what the OtherChar is set to.

I know that technically the delimiter will not be used of "Other" is not
checked, but just having the actual character showing is upsetting my users -
really.

Thanks!

 

[Dave Peterson]

Give those users something else to care about!

Maybe you could fool them by changing the otherchar:=" " (a space character).

If your users are really nuts, ahem, observant, they'll notice that there's
still something in that box, though.

Dim myCell As Range
Set myCell = ActiveSheet.Cells.SpecialCells(xlLastCell).Offset(1, 1)
With myCell
.Value = "asdf"
.TextToColumns Destination:=.Cells, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, otherchar:=" ", _
FieldInfo:=Array(1, 1) ', TrailingMinusNumbers:=True
.ClearContents
End With

 

[KAM]

Dave,
Too funny! I was thinking about using the space myself! At least it would
look good! As long as thier eyes aren't "drawn" to that character, I'll be OK.

Thanks again!
Kathleen

 

[Dave Peterson]

I don't think I'd use it.

If the user tries to add something to that box without clearing the space, they
might be more confused why they can't type in that box. With the |, at least
they'd see that there was something there to clean up????

Dave,
Too funny! I was thinking about using the space myself! At least it would
look good! As long as thier eyes aren't "drawn" to that character, I'll be OK.

Thanks again!
Kathleen

 

[NickHK]

I doubt it will work, but what happens if you pass Chr(8), BACKSPACE ?

NickHK

 

[Dave Peterson]

I saw a little square in that Other box.

I doubt it will work, but what happens if you pass Chr(8), BACKSPACE ?

NickHK