Import txt, csv, xls files into excel using vba

I've been trying to open text files into excel using vba. I want to access any text, csv, xls file and import it fast by clicking one button (located on the top of "Dust Raw Data" and "VOC Raw Data" worksheets - see attached. I generated a vba code, but the issue is that when I run the code (press the import button - on the Dust Raw and VOC Raw sheets), the values containing numbers do not register and they are assumed as text instead of numbers. Additionally, I want to extract the time only from the VOC Raw Data sheet (using the PID txt file- see attached) to the "VOC table" sheet which must be in the format of hh:mm AM/PM, but instead; excel gives me as m/d/yyyy h:mm. Excel cannot understand what I`m trying to achieve so it copies the whole thing as Ex: 05/01/23 06:05:55 instead of 6:05 AM. Please let me know if I need to add more to my code and if so, what lines. TIA!!

(Note: I`m aware of power query, but I want to use vba in this particular case)

The VBA Code:

Please Login or Register  to view this content.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. As you are new here, I have done it for you this time.)