Need a Pause in a macro

S

Steve

Hi,

I have a macro that goes out and retrieves data from a text file opened
with Excel, then pastes the data back in my worksheet and closes the
text file back up. For some reason, the data that is being brought in
is being inserted in multiple columns because it is using a space
seperator now in lieu of a tab seperator. Anyhow, if I go into my
macro and remove the part that closes the file I'm retrieving the data
from everything still works??? However, that file won't close. Is
there a line that I can add to my macro to allow a pause for things to
get caught up before the workbook I'm retrieving the data from gets
closed? TIA

For some reason this always worked as it was up until today.

Please advise,

Steve
 
D

Dave Peterson

How about something to test first?

Excel likes to remember the last settings used for Data|Text to columns (or via
the import wizard).

You can play with excel's memory by do a "dummy" text to columns--it should
reset the settings, so your real retrieval will work ok.

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim myCell As Range

Set wks = Worksheets("sheet1")
With wks
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
End With

myCell.Value = "asdf"

myCell.TextToColumns Destination:=myCell, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(1, 1)

myCell.ClearContents

End Sub

========
This memory usually screws me up when I've run data|text to columns, then copy
from a different application and try to paste to excel. I'm not sure if it will
help you--I'm not sure how you retrieve the data.
 
S

Steve

thanks, worked out fine


Dave said:
How about something to test first?

Excel likes to remember the last settings used for Data|Text to columns (or via
the import wizard).

You can play with excel's memory by do a "dummy" text to columns--it should
reset the settings, so your real retrieval will work ok.

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim myCell As Range

Set wks = Worksheets("sheet1")
With wks
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
End With

myCell.Value = "asdf"

myCell.TextToColumns Destination:=myCell, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(1, 1)

myCell.ClearContents

End Sub

========
This memory usually screws me up when I've run data|text to columns, then copy
from a different application and try to paste to excel. I'm not sure if it will
help you--I'm not sure how you retrieve the data.
Click to expand...
 
Top