Immediately run macro when import text data.

[geniusideas]

Currently I run macro after the data is imported to excel file.How to
run macro immdiately after the data is imported.Meaning when i press
refresh button --> select text file---->the macro run immediately.Pls
help.

http://microsoft-excel-macro.blogspot.com

 

[Guest]

Why don't you combine the two macros. Import the data in the same macro that
you want to run immediately after the data is imported? I did a learn macro
on a data import and this is the code I got.

With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\TEMP\Test.txt", _
Destination:=Range("C17"))
.Name = "Test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(1, 2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Application.CommandBars("External Data").Visible = False

 

[Tom Ogilvy]

http://support.microsoft.com/kb/213187/en-us
XL2000: How to Use the Query BeforeRefresh and AfterRefresh Events

http://support.microsoft.com/kb/182735/en-us
XL97: How to Use the Query Before and AfterRefresh Events


methods presented should be good in any version after and including xl97.

 

[Tom Ogilvy]

That is the way you would create a query. the OP said refresh the query -
while he wasn't totally clear, if the querytable already exists then.

Activesheet.QueryTables(1).Refresh BackgroundQuery:=False

would be the only command needed.