macro to import file (help with a piece of code)

I actually use a macro to import a txt file with a lot of columns

Below there is a part of the code that allows me to import the file...
I want to import only the first 6 columns (from A to g) and actually I
import all the columns and then erase the exceeding with

Columns("G:IV").Select
Selection.Delete Shift:=xlToLeft

How can I import them without doing the delete procedure...?
I think there is sthg to do with

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)

But I don't understand what....



With ActiveSheet.QueryTables.Add(Connection:=fileToOpen _
, Destination:=Range("A1"))
.Name = "Push"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 3
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
Columns("G:IV").Select
Selection.Delete Shift:=xlToLeft

first initialize an array to pass to TextFileColumnDataTypes:

dim v(0 to 255) as Long
for i = 0 to 255
if i <= 7
v(i) = 1
else
v(i) = 9
end if
Next

9 means to skip the column

then change you code:

.TextFileColumnDataTypes = v

--
Regards,
Tom Ogilvy

"uriel78" <[email protected]> wrote in message
news:[email protected]...
> I actually use a macro to import a txt file with a lot of columns
>
> Below there is a part of the code that allows me to import the file...
> I want to import only the first 6 columns (from A to g) and actually I
> import all the columns and then erase the exceeding with
>
> Columns("G:IV").Select
> Selection.Delete Shift:=xlToLeft
>
> How can I import them without doing the delete procedure...?
> I think there is sthg to do with
>
> .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
>
> But I don't understand what....
>
>
>
> With ActiveSheet.QueryTables.Add(Connection:=fileToOpen _
> , Destination:=Range("A1"))
> .Name = "Push"
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .PreserveFormatting = True
> .RefreshOnFileOpen = False
> .RefreshStyle = xlInsertDeleteCells
> .SavePassword = False
> .SaveData = True
> .AdjustColumnWidth = True
> .RefreshPeriod = 0
> .TextFilePromptOnRefresh = False
> .TextFilePlatform = 850
> .TextFileStartRow = 3
> .TextFileParseType = xlDelimited
> .TextFileTextQualifier = xlTextQualifierNone
> .TextFileConsecutiveDelimiter = True
> .TextFileTabDelimiter = True
> .TextFileSemicolonDelimiter = False
> .TextFileCommaDelimiter = False
> .TextFileSpaceDelimiter = True
> .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
> .TextFileTrailingMinusNumbers = True
> .Refresh BackgroundQuery:=False
> Columns("G:IV").Select
> Selection.Delete Shift:=xlToLeft
>
>

Thank you very much, it does exactly what i was looking for!!


"Tom Ogilvy" <[email protected]> ha scritto nel messaggio
news:%[email protected]...
> first initialize an array to pass to TextFileColumnDataTypes:
>
> dim v(0 to 255) as Long
> for i = 0 to 255
> if i <= 7
> v(i) = 1
> else
> v(i) = 9
> end if
> Next
>
> 9 means to skip the column
>
> then change you code:
>
> .TextFileColumnDataTypes = v
>
> --
> Regards,
> Tom Ogilvy

:-(
it seems to work 'til yesterday....

now it doesn't...

I just want to import only those 7 columns and I want values already on the
sheet to be replaced by the data imported without moving any rows or
columns...how can I do...I'm struggling...:-(



"Tom Ogilvy" <[email protected]> ha scritto nel messaggio
news:%[email protected]...
> first initialize an array to pass to TextFileColumnDataTypes:
>
> dim v(0 to 255) as Long
> for i = 0 to 255
> if i <= 7
> v(i) = 1
> else
> v(i) = 9
> end if
> Next
>
> 9 means to skip the column
>
> then change you code:
>
> .TextFileColumnDataTypes = v
>
> --
> Regards,
> Tom Ogilvy
>
> "uriel78" <[email protected]> wrote in message
> news:[email protected]...
> > I actually use a macro to import a txt file with a lot of columns
> >
> > Below there is a part of the code that allows me to import the file...
> > I want to import only the first 6 columns (from A to g) and actually I
> > import all the columns and then erase the exceeding with
> >
> > Columns("G:IV").Select
> > Selection.Delete Shift:=xlToLeft
> >
> > How can I import them without doing the delete procedure...?
> > I think there is sthg to do with
> >
> > .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
> >
> > But I don't understand what....
> >
> >
> >
> > With ActiveSheet.QueryTables.Add(Connection:=fileToOpen _
> > , Destination:=Range("A1"))
> > .Name = "Push"
> > .FieldNames = True
> > .RowNumbers = False
> > .FillAdjacentFormulas = False
> > .PreserveFormatting = True
> > .RefreshOnFileOpen = False
> > .RefreshStyle = xlInsertDeleteCells
> > .SavePassword = False
> > .SaveData = True
> > .AdjustColumnWidth = True
> > .RefreshPeriod = 0
> > .TextFilePromptOnRefresh = False
> > .TextFilePlatform = 850
> > .TextFileStartRow = 3
> > .TextFileParseType = xlDelimited
> > .TextFileTextQualifier = xlTextQualifierNone
> > .TextFileConsecutiveDelimiter = True
> > .TextFileTabDelimiter = True
> > .TextFileSemicolonDelimiter = False
> > .TextFileCommaDelimiter = False
> > .TextFileSpaceDelimiter = True
> > .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
> > .TextFileTrailingMinusNumbers = True
> > .Refresh BackgroundQuery:=False
> > Columns("G:IV").Select
> > Selection.Delete Shift:=xlToLeft
> >
> >
>
>

:-) evrything gets ok this morning, maybe last night my brain was too
hot....:-)



"uriel78" <[email protected]> ha scritto nel messaggio
news:[email protected]...
> :-(
> it seems to work 'til yesterday....
>
> now it doesn't...
>
> I just want to import only those 7 columns and I want values already on
the
> sheet to be replaced by the data imported without moving any rows or
> columns...how can I do...I'm struggling...:-(