Requesting assistance in cleaning up some text import VBA

Edit: Thread solved ( Solution code in post #9 )



A portion of my VBA came from macro recording and I was wondering how much I can trim it up.

Essentially it's just taking a pipe-delimited text file and importing it into a table at this stage in the VBA but I feel like I don't need a lot of these conditions.

All it needs to do is take the text file ( ImportFile ) and take the pipe delimitation and use that to post into a table.
Right now this assumes I am using the same text file ( 44 delimitations ) but I would like it to be variable so that I could load a 4 pipes per row delimited file or a 100 pipes per row delimited file

The text file is just values, there is nothing unique that has to be done with any values received except that they can sometimes be numeric values beyond 14 characters long ( Which excel sometimes tries to turn into a scientific formula ) which I believe some part of the conditions is handling because it doesn't do that with this particular code

Please Login or Register  to view this content.

Try commenting this out and see what happens:

Please Login or Register  to view this content.

Ok, have a look at this link and try it.

https://superuser.com/questions/3074...-files-as-text

Checked out the link and it did have good information about .csv files but not for my current setup ( For .txt files )

It did give me ideas though about making this VBA handle both .txt and .csv files ( Comma delimited true, everything else false on .csv and input option for .txt delimitation identifier down the road )


I'm just wondering if there is a way to not have excessive 2's in this part:

.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, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)

I use almost 60 of them to account for the files I'm currently loading and I could manually add 140 or so more but it just doesn't look clean to me. I was hoping there would be a loop statement or something to replace all those 2's

Hi #DIV/0!
I think probably simplifying any code got from a macro recording usually means to a large extent, as Trevor suggested, taking things out and seeing what happens.

_...........................................................

I was actually, by coincidence, just now, trying out a lot of different ways of importing Pipe delimited files into Excel for myself.
Your way is probably the only way I have not tried yet. If you ever get it simplified and understand the code , then I would be interested to add it to my collection.

_...........................
Here below is a code showing just one of the ways I am currently trying. I have adapted it to your code. The main “adaptation” was the Application.GetOpenFilename( __, which I don’t do usually .

I just tested this on a text file of mine of about 255 columns by 8790 rows, Pipe delimited. ( You never have to know how many columns or rows you have ).

It seems to work. My text data is simple text, ( including commas in it ) and numbers from about 0.0000001 to 1000. I do not have any very large numbers so I do not know what sort of changing Excel might decide to do from “normal” to scientific notation. I don’t understand all that and usually get really pissed off when Excel changes things like that.


If you want to try out the code below and have any problems getting it to work, then give me a text file and I will try it out for you.
If you want to know then how it works I will be happy to bore you to death with the explanation. I would not personally recommend using the code if you have no idea how it works. ( My last explanation of this sort of code I did today here https://www.excelforum.com/excel-pro...ml#post4655965 a few minutes ago ). If you want me to explain the code, then please read that first, then I will try to explain again and apply the explanation specifically to your code version.


Alan

Please Login or Register  to view this content.

Edit: Thursday 18th May 2017 : Commented out the ' Let oRsT.CursorLocation = adUseClient
- see Post 11

Originally Posted by #DIV/0!

Got stuck on the ": Let oRsT.CursorLocation = adUseClient" part

What got stuck? - The code or you in understanding it.

Found a way to simplify it via a link ( http://stackoverflow.com/questions/1...ilecolumndatat )

Revised and shortened code below:

Please Login or Register  to view this content.

Thanks for the rep.


And thanks for sharing your solution.

Hi #DIV/0!
Thanks for the Feedback, sharing your solution, and the Rep

Actually I am only replying here as I noticed a quirk sometimes in the ": Let oRsT.CursorLocation = adUseClient" part:

What I sometimes noticed was:
_If I use Option Explicit I get a compile error of:
variable not defined
_Without Option Explicit I get a runtime error:
Run time error ‘3001’
The argument is of false type, is outside of the validity range, or not compatible with each other

I found out why this was, and also found out why I had not noticed it before:
I had not noticed this before, as I usually had a reference to Microsoft Active X Data Objects 2.5 Library.
Adding that reference cures that error. ( I find that a bit strange as the code uses Late Binding. )

_........................

So to cure that problem you can do one of two things
Either
_A. add a reference to one of the Microsoft Active X Data Objects Libraries
To do that, Like in this screenshot: MicrosoftActiveXDataObjects2_5Library.JPG :- http://imgur.com/jbN2KG0 :-
From VB Editor Window:-
_ 1. Extras
_ 2. References
_ 3. scroll down to Microsoft Active X Data Objects 2.5 Library, ( or similar )
_ 4. Add a check to that in the little box to the left
_ 5. OK

Or
_B. the code will work if you just ‘Comment out that: ' Let oRsT.CursorLocation = adUseClient

_........................

These Cursor option things are a bit quirky and are the only things I could not get a good clear explanation of from anyone. ( I found by experimenting that including _ oRsT.CursorLocation = adUseClient _ speeded my codes up a little bit )


_......................
I found in my forays into “ADO” stuff a lot of quirks, so I am not too keen on it. But it might be worth a quick try again if you check that ": Let oRsT.CursorLocation = adUseClient" again.
_......................................

In the code I gave in post #6 I just edited the code. ( And I checked it on a text file of mine of about 1760 columns x 7990 rows, pipe _ | _ delimited: it seems to work OK )

Thanks again for the Rep
Alan


P.s. If you get a chance, check out the Forum Rules please, https://www.excelforum.com/forum-rul...rum-rules.html about using code tags, for example.
Thanks.

Please Login or Register  to view this content.

You do code tags like this in the editor before you post:
[CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]