Excel Import Data Maco Problem 1

[ecugrad]

Need some help on the below Macro. To start with,I know little about Macros.

When the macro is run it doesn't delete the old data out of the spreadsheet. It moves it across 10 cells and adds the new data. I don't know why, but I know there must be a way to delete the previous data before inserting the new. Also, to run the macro we have to utilize the ALT/F8 keys. This is ok, but I'm wondering why the macro can't be run and new data inserted when the speadsheet opens. Also, how can I automatically sort by two fields utilizing the macro instead of manualy doing it. The data is being passed by a procedure in an Access database. We are using Excel 2003






Sub ImportData()
'
' ImportData Macro
' Import Text file from Document Direct
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;G:\Overpay\DailyRetailImport.txt", Destination:=Range("A2"))
.Name = "DailyRetailImport"
.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 = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
End Sub

Thanks, for the Help

Mike

 

[MacroAlan]

That is a lot of questions. This snippet begins at cell A9 amd clears everything to the end of the sheet:

    Range("A9").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    With Selection
        .ClearContents
    End With

To run as the sheet is opened, name your macro

Sub Auto_Open()

The flip side is Sub Auto_Close()




Alan

 

[ecugrad]

Thanks MarcoAlan, worked like a charm. Any, idea on the sorting piece?

Thanks again,

Mike

 

[MacroAlan]

Missed that

    Range("A1:E10").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal

Change the ranges in Key1 and Key2 to match what you need.

 

[SkipVought]

Hi,

You ONLY have to ADD a QueryTable ONE TIME.

Ever time AFTER that, to retrieve a NEW set of data REPLACING the original data, you need ONLY do a Data Refresh...

    Sheets("YourSheetName").QueryTables("DailyRetailImport").refresh

Skip,
[sub]
[red][/red]
[/sub]