Text import spilling over into new columns

[jalea148]

I'm importing several small 2 column data files of dates with variations of the following macro:

Sub ENPURP()
'
' ENPURP Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Administrator\My Documents\Dropbox\Files\P_EN_UR.CSV" _
, Destination:=Range("$AG$16"))
.Name = "P_EN_UR"
.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 = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(3, 3)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

After importing a few rows correctly the macro starts creating new columns and saves the data there.

The macro was captured using Data ==> From Text. This import ran ok.

 

[Joe4]

It looks like you set it to use both Tab and Comma as delimiters.
I would take a look at the data in your file, to see if you have some extra unintended delimiters in there that you weren't expecting.

 

[jalea148]

It looks like you set it to use both Tab and Comma as delimiters.
I would take a look at the data in your file, to see if you have some extra unintended delimiters in there that you weren't expecting.

Thanx for your suggestion.
I changed TextFileTabDelimiter = True to TextFileTabDelimiter = False and checked the text file for delimiters. There were none other than the commas. To insure there were no hidden characters, the file was opened with Notepad and resaved. The problem remains.

 

[Joe4]

Can you post a small sample of your problem data, and what the output you are getting looks like?
There are tools you can use the post screen shots (see here: http://www.mrexcel.com/forum/about-board/508133-attachments.html).
You can practice posting them in the "Test Here" forum to ensure that you have it working correctly.

 

[jalea148]

Links for 3 images are below:
the text file being imported,
a portion of the Excel 2007 spreadsheet before the import and
a portion after the import.

Numerous new columns were added. The data inserted does not correspond to the import file.

[I tried to insert the image from a URL, but the insert image window did not respond to the OK key; also, I couldn't find the Attachment button in Reply.]

 

[Joe4]

There is no Attachment button, you would have to use the methods described in the link I provided. No matter, I can use the links you provided.

OK, I am looking at the files. Can you explain exactly what is happening?
When you run your code to import the test file your provided, where is it placing the data?
Are you overriding (or wanting to override) the old data starting in cell AG16?
Does it shift the existing data?

 

[jalea148]

There is no Attachment button, you would have to use the methods described in the link I provided. No matter, I can use the links you provided.

OK, I am looking at the files. Can you explain exactly what is happening?
When you run your code to import the test file your provided, where is it placing the data?
Are you overriding (or wanting to override) the old data starting in cell AG16?
Does it shift the existing data?

My intent was to overwrite to existing data. I just cleared the fields and ran the import - there were not any problems. Problem solved; thanx!
Can the macro be amended to allow overwriting of the existing data?

 

[Joe4]

You should be able to do what you did manually (clear the data first) and run the code. If you use the Macro Recorder, and record yourself performing that task, it will give you the code you need to do it automatically.

Let us know if you run into any problems.