Opening a non-Excel file in Excel with delimiters

I am Exporting a report from a system called FPARS (Freight Payment System) and saving it in the folder where it needs to be. Next I need to open it in Excel, using commas as a delimiter. The report saves with the extension "_XLS" and the file type says "File". Is it possible to make my macro open the file using the delimiter or does that part have to be done manually. The File Name won't always be the exact same thing, so I'm thinking that, if it could bring up the Open File box, maybe have the user select the file to open and then have the macro do the rest.

I tried just opening it with no delimiters and then doing a Text-To-Columns, but certain rows open up with data in columns other than A. I have no idea why.

Sort of cross-posted, but not explained as well, and haven't had any responses yet: http://www.mrexcel.com/forum/excel-q...g-problem.html

Thanks!

Jenny

Okay, sorry. I was trying to figure out how to remove sensitive data in the original file while leaving it in the original format. Finally got it, but it won't show up as being available to upload here! So, I put the information into the first column of an XLSX spread sheet (capVisExporttest_XLS - Copy); I hope that helps. capVisExporttest_XLS2 - Copy is how I want it to look right after opening. Once it's open in Excel, I've got my macro doing what I need to finish it. That coding is below. Feel free to tweak that if you see anything.

Please Login or Register  to view this content.

Thanks!

Jenny

Originally Posted by TMS

Something like:

Please Login or Register  to view this content.

Regards, TMS

Sorry to be so long; I had to go and do my ACTUAL work for a while, LOL!

I tried that code, replacing the path to the file I want, but it tells me I have the name wrong. I checked several times and I've typed it in correctly.

Meanwhile, I've Googled the problem and found some (very long) coding that brings up an "Open File" dialog box and lets me select the correct file to open. But I can't get it to correctly delimit the file so that it opens the way it should. Here's that coding:

Please Login or Register  to view this content.

It fails on the line:

Please Login or Register  to view this content.

and the error is Run-time error '1004': Method 'Cells' of object'_Global' failed.

Any ideas?

Thanks!

Jenny

I got it figured out! I pieced together code from here and from Googling stuff & got it doing what I want! It opens an Open File box, lets you select the text file you want and opens it with delimiter - parsing across the spreadsheet into (in this case) columns A-P.
In case anyone could use this information, here's the coding I put together:

Please Login or Register  to view this content.

Thanks for the input!

Jenny