Opening a Text File (Tab Delimited)

[Andrew.Trevayne]

Hi again everyone!
Sorry for being a pain but I'd like your help once again.
Are you able to help me with a Macro that Opens a Text File into the same Sheet that I will run the Macro?
I would also need help deleting the 1st Line on the Text once it has been opened on Excel.
Starting line of the Data will be Cell A5.
I have enclosed my sample worksheet. Thank you in advance.

Andrew

[JosephP]

did you try recording the steps as you do them manually?

[Andrew.Trevayne]

Yes sir I have. But I was hoping that everytime I open a Text File, It would prompt me to select which Text File to Open.
When I recorded, I had to select the file already. Here's what I currently have:

Please Login or Register  to view this content.

---------- Post added at 01:52 AM ---------- Previous post was at 01:49 AM ----------

And it is also copying the file starting from Cell A1. I was hoping for it to start at A5. ^^

---------- Post added at 01:59 AM ---------- Previous post was at 01:52 AM ----------

---------- Post added at 02:00 AM ---------- Previous post was at 01:59 AM ----------

Okay I've managed the Macro to promt me to select the file to open.
Here is the code:

Please Login or Register  to view this content.

Next step is to get it to open and paste the result startin on Cell A5, any ideas?

[JosephP]

you aren't copying anything-you're opening the file as a workbook which is why the data starts in A1. you can either copy and paste from the new workbook to your target worksheet or import the data from the text file. copy and paste is probably easier since you don't want the first line anyway-just

Please Login or Register  to view this content.

for instance.

[MrShorty]

Must this be done by a Macro, or are you open to a non-macro approach? Unless there is something missing in your requirements, i would use the import external data command:

1) Data menu-> import external data -> import external data
2) navigate to the desired text file. You may have to tell it to show all files or text files, as those may not display by default.
3) Once the file is selected, Excel will bring up the text import wizard. As you work through the wizard, you can select options like what to use for the delimiter (tab), what row to start at (it sounds like you want to start on row 2), how to format each column or skip any columns.
4) After completing the text import wizard, the final dialog will have options for where to put the data (A3), and a properties button through which you can access an additional dialog where you can decide whether to have Excel remember the query definition or not, whether to prompt for the filename each time you want to refresh the data, manual or periodic refresh, and so on.