Data Import Macro in Excel and Named Ranges 1

[Joycelet]

Hi

I have a macro that uses Data Import to import data into a spreadsheet each week the data is the same format each time. My problem is that each time I run the code it changes the named range so then my vlookups etc don't work

How can I get it to keep the same named range

heres the code:

Sub DataImport()
'
' DataImport Macro
' Macro recorded 31/08/2006 by  Nicola Joyce
'

'
    Cells.Select
    Selection.Clear
    Selection.QueryTable.Delete
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Exported Data.txt", _
        Destination:=Range("A1"))
        .Name = "Exported Data"
        .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 = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    Range("A2").Select

 

[SkipVought]

Hi,

Why delete and add the query table?

Sub DataImport()
'
' DataImport Macro
' Macro recorded 31/08/2006 by  Nicola Joyce
'

'
    With ActiveSheet.QueryTables(1)
        .Connection = "TEXT;C:\Exported Data.txt"
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    Range("A2").Select
    
End Sub

Instead of ActiveSheet, I'd recommend referencing the sheet explicitly.

Skip,

_[red][/red]

 

[Joycelet]

HI Skip

I'm not sure what you mean about delete and add the query table - is there a better way of updating the data?

Thanks

 

[SkipVought]

Once you have added a QueryTable, all you need do is Data/Refresh, unless you are pointing to a different file. Do really don't need to run the code.

Skip,

_[red][/red]

 

[Joycelet]

Oh right - I guess not!

Just need to get it to Data/Update on a click of a button which is fine

Thanks for pointing me in the correct direction

 

[SkipVought]

Check out the Data/Data Range Properties.

Skip,

_[red][/red]

 

[Joycelet]

Hi

It still keeps renamaing the data range and adding new ranges each time for example Exported_Data and Exported_Data1.

How would I get it to just update without redefining the name?

thanks

 

[Joycelet]

Hi Ignore the last post - I've managed to sort it, me being stupid!

 

[SkipVought]

If you are ADDING a QT each time the QT name changes.

Just Data/Refresh.

Skip,

_[red][/red]

 

[camwild79]

Hi, whenever i refresh a pivot table, it often asks me 'Do you want to replace the contents of the destination cells..' this is really annoying since this has to happen within some code that runs automatically and when this diolog box comes up asking this question, nothing else can happen until you answer it! is there any way that i can make it either not ask me questions at all, or simply default to answering yes to it??? please help! thanks very much

i have tried it with both these different code options

Set pvtTable = Worksheets("Positions").Range("C9").PivotTable
pvtTable.RefreshTable

And this code does it too..

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

 

[SkipVought]

camwild79,

Please post NEW questions in a NEW thread.

Skip,

_[red][/red]