Import text file via Query

Richard,

The overall objective is to import three different text files that are generated out of our accounting software and create an excel workbook that shows week to date, month to date, and year to date sales info. Right now we are manually doing this. All the formulas and so on in the excel sheet are working we just need to get the data in the corresponding sheets. I would like to automate it so anyone that has access to the workbook and can create the tab delimited files can just open the workbook and the numbers all show up correctly. In the end I will control where the tab delimited files are located and when they are created. So the Sales Manager can just open this workbook every Friday and he will have all the info he needs to fill out his reports to the owners.

The only reason that I am grabbing one line at a time is because that is the only way I know how to do it. I just need the entire file in separate cells in the same order they are in the text file starting at cell A2. If there is a better way I am open to that. The way I have it coded is the only way I could come up with that made any kind of sense to me.

Thanks for the quick reply.

hi, extrapulp, you can try to use either macro recording and do txt file import and edit the code you get or use something sort of that:

Please Login or Register  to view this content.

Try to run this code on a test workbook

watersev,

I modified the code you provided so I can pass in a file and the sheet I want the data imported to. I also took out the screen updating I have that handled outside this sub.

It works great but it opens a new workbook and puts the data in there starting at cell A1 not A2 as called out. I would like to specify which sheet the data goes into. I thought with selecting the sheet that would do it. The following modified code imported 5295 rows of data in a second or two.

Here is the modified code.

Please Login or Register  to view this content.

That is much much simpler and way faster. Can you give me any insight on how to get it to go into the specified sheet instead of putting it in a new workbook?

Thank you for the reply!

shg,

Running the code you posted unaltered gives me an error out of resources (see attached jpg of error window) on the line

Please Login or Register  to view this content.

It is also opening another workbook. In both cases it opens a workbook named the same as the file name I am importing.

I am still messing with the code you and watersev posted. Maybe I can stumble on some alteration of that to make it work. Either way I am seeing different perspectives on how to get the same results!

As far as recording a macro of importing a file. I know how to record a macro but how would I import a file via Excels interface?

Thanks for all the great replies!

Alright I figured out how to record the macro of importing a file via the Excel interface. I have never done this before. Just always copy and paste and then text to columns... Learn something every day.

This is what I ended up with and it works just great. Takes about 3 seconds for almost 6000 rows.

Please Login or Register  to view this content.

Thanks goes out to everyone that helped!!!!!