Macro based Import Data Inserts Cells, I want Replace
 

I recorded a macro to import data from a text file. When I record i
interactively, I replace data, but when I integrate this script into
macro it always shoves old data over, which screws up my cel
references in other formulas. How can I modify this code to replac
data instead?

Sheets(HOTPAGE).Select
Cells(8, 1).Select
' Selection.Delete Shift:=xlUp
Selection.Clear
With Sheets(HOTPAGE).QueryTables.Add(Connection:= _
"TEXT;" & HOTSAUCE, Destination:=Sheets(HOTPAGE).Cells(8, 1))
.FieldNames = True
.RowNumbers = False
' .FillAdjacentFormulas = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
' .TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
' .TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

Thanks a bunch,

Bram Weisma

--
bramweisma
-----------------------------------------------------------------------
bramweisman's Profile: http://www.excelforum.com/member.php...nfo&userid=174
View this thread: http://www.excelforum.com/showthread.php?threadid=38577

Macro based Import Data Inserts Cells, I want Replace
 

It looks like your problem is that you are calling the ADD method for the
QueryTable repeatedly. You probably want to call the Refresh method of the
QueryTable to update it after it has been created. Try the following:

Add this line to your create statement:

..Name = HOTSAUCE

Where HOTSAUCE is a valid name for the QueryTable.

In the code that does the refresh simply put this:

ActiveSheet.QueryTables(HOTSAUCE).Refresh

I think this will work.

The only other thing that could have an impact is the RefreshStyle proprty.
Look it up in Excel help and it will tell you what the options do.

Hope this helps.

By the way, this is new to me, but it looks useful for linking to SQL
Server, which is what I do on a daily basis.

Thanks for opening my eyes to something new.
--
Happy Coding,

Scott


"bramweisman" wrote:


I recorded a macro to import data from a text file. When I record it
interactively, I replace data, but when I integrate this script into a
macro it always shoves old data over, which screws up my cell
references in other formulas. How can I modify this code to replace
data instead?

Sheets(HOTPAGE).Select
Cells(8, 1).Select
' Selection.Delete Shift:=xlUp
Selection.Clear
With Sheets(HOTPAGE).QueryTables.Add(Connection:= _
"TEXT;" & HOTSAUCE, Destination:=Sheets(HOTPAGE).Cells(8, 1))
.FieldNames = True
.RowNumbers = False
' .FillAdjacentFormulas = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
' .TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
' .TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

Thanks a bunch,

Bram Weisman


--
bramweisman
------------------------------------------------------------------------
bramweisman's Profile: http://www.excelforum.com/member.php...fo&userid=1746
View this thread: http://www.excelforum.com/showthread...hreadid=385776