Import TXT and Speed up processing Time

I found a code that works. But I need to accomplish a bit more.

1) I need to be able to add the headers into this. I am dealing with end users with basic Excel skills so I need to anticipate problems. Headers being deleted has been an issue in other files used by this group. There are a total of 37 headers, if you will simply provide that script and the syntax so I can see how it is done, I can overwrite the first few, then add the additional ones. OR would the creation of headers from another worksheet be easier? I have the list there for another purpose. The named range is: codes_Headers and is located on the sheet named "codes" (sheet1) H2:H38. I don't know the best way to do this, so I want to offer all the information.

2) At almost the bottom of the code is an array for the data type in the columns. In this example, there are 8 numbers listed in the array. I am assuming that the data had 8 columns. The 1 is listed as 1. I am guessing as 1, is the for General? Now I have a few columns that are dates and a few that are text. I also have one that is currently showing as custom, as it is a date/time stamp (for example: 1/28/2019 15:27). Now because I have a total of 37 columns, do I need 37 numbers within my array, or is there an easier way to do that part?

3) My actual data file has over 37K records. This is the first file I have received, at the very least this is an average, I do not know how much more this file could potentially have. The run time of this macro was 40 seconds. Is there anything within the code that can be made more efficient so it is faster when it is run?

I am including a sample file. I have converted the confidential data to garbage. There are about 10 records or so in the sample, but the format is represented.

Please Login or Register  to view this content.

PaulSP8,

Okay, That solves item #1 on my list. Thanks. Now to figure out answers to the other 2 issues.

Please Login or Register  to view this content.

PaulSP8,

I completely missed that... this week has been a blur so far. I just did the recording of the import. I thought so on the speed... like most companies, we are working on cheap off the shelf "Playskool" lapstops with no power. They want us to muti-task, but these machines are barely capable of having Outlook and Excel open at the same time let alone actually doing something! LOL

Please Login or Register  to view this content.

Paul---

Question, can we do something about this part of the code? The path and file name cannot be hard coded into the file like this as this is something being created for an end user. And there cannot be any hard coding of paths and file names as they change. Can ya help?

Please Login or Register  to view this content.