Macro question opening external text files to retrieve data

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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
 
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
 
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











--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

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