+ Reply to Thread
Results 1 to 2 of 2

Macro based Import Data Inserts Cells, I want Replace

  1. #1
    Registered User
    Join Date
    10-20-2003
    Posts
    4

    How can I prevent QueryTables from inserting columns

    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
    .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
    Last edited by bramweisman; 07-08-2005 at 10:54 PM. Reason: Original title wasn't effective communication

  2. #2
    Scott Vincent
    Guest

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1