Trying to automatically import data

[takformaten]

Folks, I am going NUTS!!!

OK, what I am trying to do is make a command button. (Easy), that when you press ist imports data from a .txt file, formats it [delimiters etc.] (Easy) and then pastes it into a newly created worksheet. (arrrgh)

Code is: Private Sub CommandButton2_Click()
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Data\WS#CPD03_01.TXT" _
, Destination:=Range("A1"))
.Name = "WS#CPD03_01"
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 2, 1, 1, 2, 2, 2, 1, 2, 1)
.TextFileFixedColumnWidths = Array(19, 70, 3, 37, 26, 9, 11, 20, 26, 4)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

On clicking the button my Excel tells me the "destination range was not on the same worksheet that the query table was being created on" (Run time error) When I click on help I only see a grey box.

Is it me? Is it Excel? Is there help?

 

[santeria]

I use a button to import txt files, however, the key thing is that the file it is brought into is a fresh version of a template file.

The start I had before I had help from people here, was that I macro-ed out by recording the task, before I proceeded to query things.

 

[takformaten]

Sorry, Santeria, I don't get it... what do you mean "fresh version of a template file?"

I tested the macro recording thing and got exactly the same error...

 

[santeria]

For each day file I use, when I import the txt file, I clear the page, and then the macro imports the text file. That way, the summary links I use have fresh data.

The "Fresh Version of a Template" is basically taking a file from scratch in each case of the import.
I also use a fresh template for each day's data.

 

[Cbrine]

You might want to try qualifying you destination range better, change this

Destination:=Range("A1")

to this

Destination:=Activesheet.range("A1")

 

[takformaten]

That was it! Thanks a lot!

Merci!!!