Macro based Import Data Inserts Cells, I want Replace

[bramweisman]

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

 

[Guest]

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.