Hi,

I have a macro that has been written to open to different text files
(using Excel), retreiving some data and bringing it back into my
workbook, (bits shown below). At the end of my macro, I have a column
that I have to use the Text-to-Columns feature using a comma as the
delimiter. All of this works fine until I go to use the macro on a
different file, then it remembers the comma delimiter from that last
part of the macro and therefore won't work again from the beginning
when opening the two text files to retreive data, which by the way
were defaulting to us a "tab" delimiter. I tried adding some
information to macro to the part where I open the text files, however
my macro kept stopping there. Any help would be certainly
appreciated.



Thanks,

Steve


Dim wkbCurrent As Workbook
Set wkbCurrent = ActiveWorkbook

Workbooks.Open Filename:="\\smdsldb01\jobscan\Alt_Items_List.txt"



Dim wkbCurrent2 As Workbook
Set wkbCurrent2 = ActiveWorkbook

Workbooks.Open Filename:="\\smdsldb01\jobscan
\Drawing_Nbr_List.txt"



Columns("L:L").Select
Selection.TextToColumns Destination:=Range("L1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Best to explicitly disable the unwanted separators and enable the desired
separator. From the Recorder, this splits on the character "c":


Sub Macro1()
Selection.TextToColumns Destination:=Range("C4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="c", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
End Sub

--
Gary''s Student
gsnu200712


"Steve" wrote:

> Hi,
>
> I have a macro that has been written to open to different text files
> (using Excel), retreiving some data and bringing it back into my
> workbook, (bits shown below). At the end of my macro, I have a column
> that I have to use the Text-to-Columns feature using a comma as the
> delimiter. All of this works fine until I go to use the macro on a
> different file, then it remembers the comma delimiter from that last
> part of the macro and therefore won't work again from the beginning
> when opening the two text files to retreive data, which by the way
> were defaulting to us a "tab" delimiter. I tried adding some
> information to macro to the part where I open the text files, however
> my macro kept stopping there. Any help would be certainly
> appreciated.
>
>
>
> Thanks,
>
> Steve
>
>
> Dim wkbCurrent As Workbook
> Set wkbCurrent = ActiveWorkbook
>
> Workbooks.Open Filename:="\\smdsldb01\jobscan\Alt_Items_List.txt"
>
>
>
> Dim wkbCurrent2 As Workbook
> Set wkbCurrent2 = ActiveWorkbook
>
> Workbooks.Open Filename:="\\smdsldb01\jobscan
> \Drawing_Nbr_List.txt"
>
>
>
> Columns("L:L").Select
> Selection.TextToColumns Destination:=Range("L1"),
> DataType:=xlDelimited, _
> TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
> Tab:=False, _
> Semicolon:=False, Comma:=True, Space:=False, Other:=False,
> FieldInfo _
> :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
>
>

Excel likes to help. If you've ever imported a comma delimited text file or run
Data|Text to columns (delimited by a comma), then excel will figure you want to
do this when you try to paste the next time.

But excel's memory is forgotten if you close and reopen excel. Another way is
to do a dummy Data|text to columns.

put asdf in an empty cell
select that cell
data|text to columns
delimited
but uncheck all the options
and finish up
And then clean up that dummy cell

=====
You could add something like this to the bottom of your code:

'add to the top of your code
Dim DummyCell As Range


'your code here

With ActiveSheet
Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
With DummyCell
.Value = "asdf"
.Cells.TextToColumns Destination:=.Cells(1), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 1)
.ClearContents
End With
End With

End Sub

Steve wrote:
>
> Hi,
>
> I have a macro that has been written to open to different text files
> (using Excel), retreiving some data and bringing it back into my
> workbook, (bits shown below). At the end of my macro, I have a column
> that I have to use the Text-to-Columns feature using a comma as the
> delimiter. All of this works fine until I go to use the macro on a
> different file, then it remembers the comma delimiter from that last
> part of the macro and therefore won't work again from the beginning
> when opening the two text files to retreive data, which by the way
> were defaulting to us a "tab" delimiter. I tried adding some
> information to macro to the part where I open the text files, however
> my macro kept stopping there. Any help would be certainly
> appreciated.
>
> Thanks,
>
> Steve
>
> Dim wkbCurrent As Workbook
> Set wkbCurrent = ActiveWorkbook
>
> Workbooks.Open Filename:="\\smdsldb01\jobscan\Alt_Items_List.txt"
>
> Dim wkbCurrent2 As Workbook
> Set wkbCurrent2 = ActiveWorkbook
>
> Workbooks.Open Filename:="\\smdsldb01\jobscan
> \Drawing_Nbr_List.txt"
>
> Columns("L:L").Select
> Selection.TextToColumns Destination:=Range("L1"),
> DataType:=xlDelimited, _
> TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
> Tab:=False, _
> Semicolon:=False, Comma:=True, Space:=False, Other:=False,
> FieldInfo _
> :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

--

Dave Peterson

On Mar 28, 8:20 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Excel likes to help. If you've ever imported a comma delimited text file or run
> Data|Text to columns (delimited by a comma), then excel will figure you want to
> do this when you try to paste the next time.
>
> But excel's memory is forgotten if you close and reopen excel. Another way is
> to do a dummy Data|text to columns.
>
> put asdf in an empty cell
> select that cell
> data|text to columns
> delimited
> but uncheck all the options
> and finish up
> And then clean up that dummy cell
>
> =====
> You could add something like this to the bottom of your code:
>
> 'add to the top of your code
> Dim DummyCell As Range
>
> 'your code here
>
> With ActiveSheet
> Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
> With DummyCell
> .Value = "asdf"
> .Cells.TextToColumns Destination:=.Cells(1), _
> DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> ConsecutiveDelimiter:=False, Tab:=False, _
> Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
> FieldInfo:=Array(1, 1)
> .ClearContents
> End With
> End With
>
> End Sub
>
>
>
>
>
> Steve wrote:
>
> > Hi,
>
> > I have a macro that has been written to open to different text files
> > (using Excel), retreiving some data and bringing it back into my
> > workbook, (bits shown below). At the end of my macro, I have a column
> > that I have to use the Text-to-Columns feature using a comma as the
> > delimiter. All of this works fine until I go to use the macro on a
> > different file, then it remembers the comma delimiter from that last
> > part of the macro and therefore won't work again from the beginning
> > when opening the two text files to retreive data, which by the way
> > were defaulting to us a "tab" delimiter. I tried adding some
> > information to macro to the part where I open the text files, however
> > my macro kept stopping there. Any help would be certainly
> > appreciated.
>
> > Thanks,
>
> > Steve
>
> > Dim wkbCurrent As Workbook
> > Set wkbCurrent = ActiveWorkbook
>
> > Workbooks.Open Filename:="\\smdsldb01\jobscan\Alt_Items_List.txt"
>
> > Dim wkbCurrent2 As Workbook
> > Set wkbCurrent2 = ActiveWorkbook
>
> > Workbooks.Open Filename:="\\smdsldb01\jobscan
> > \Drawing_Nbr_List.txt"
>
> > Columns("L:L").Select
> > Selection.TextToColumns Destination:=Range("L1"),
> > DataType:=xlDelimited, _
> > TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
> > Tab:=False, _
> > Semicolon:=False, Comma:=True, Space:=False, Other:=False,
> > FieldInfo _
> > :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Thanks to both of you for the help.... I ended up using Daves reply in
my spreadsheet and everything worked out fine....regards, Steve