Dynamic data type for columns

[meldrape]

Hello there,

I recorded a macro to import some data into excel from a text file. I had to set each column as text datatype. Is there a way I can make it dynamic so I can use this module for other imports? Here's the line that sets the datatype:

.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
        2, 2)

Is there any way I can make that a dynamic array or a dynamic datatype for the entire import? Thank you in advance.

 

[EBGreen]

You could try making it Variant.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[meldrape]

Many thanks for your speedy reply. Please excuse my ignorance (or not) and I probably didn't explain it properly but what exactly do you mean? Some of the imports might have 30 columns, some might have 7. What I am looking for is a way to set the array for any number of columns, instead of 2,2,2,2,2 etc. I don't use excel very often so I need all the help I can get

Thanks again!!

 

[EBGreen]

Aah, I think I understand. So the number of fields in the text file will be variable?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[meldrape]

Yes, thanks Tom!!

 

[EBGreen]

I would simply create a temp array, dim it to the number of fields in the text file. Do a for loop from lbound of the array to ubound of the array setting each value to be what you want as you go. Then do the .TextFileColumnDataTypes assignment.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[meldrape]

Thanks Tom. I might need help with syntax but I"ll give it a shot. THanks again.