Trying to automatically import data

takformaten

Board Regular
Joined
Jan 19, 2004
Messages
75
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. :oops:

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

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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.

(y)
 

takformaten

Board Regular
Joined
Jan 19, 2004
Messages
75
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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.

(y)
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
You might want to try qualifying you destination range better, change this

Destination:=Range("A1")

to this

Destination:=Activesheet.range("A1")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,705
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top