VB Run-time error 1004 when opening file from previously chosen folder

[damkru]

Hello Everyone,

This is my first post on the forum and my first programming attempt. Thus, please be understanding if I will ask basic questions OK, cutting to the chase...

I'm trying to write a macro which would import data from multiple .csv files to one worksheet. I have few hundreds files, each file has only one worksheet named the same as the file and all files are in the same directory. In each worksheet are 3 columns and more than 2k rows of data. I need to put all of that (except last 9 rows from each file) to one worksheet one under another (one table). Using some codes from forums and trail and error method I end up with following code:

Please Login or Register  to view this content.

Unfortunately, that worked only for the testing folder where I copied 3 files. When I try to run it for other folder or even for the test folder but with different files I get "Run-time error '1004'". Full error msg:

'xxx.csv' could not be found. Check the spelling of the file name, and verify that the file location is correct.

If you are trying to open the file from list of mostrecently used files, make sure that the file has not been renamed, moved or deleted.

Where 'xxx.csv' is first .csv file in the folder alphabetically. Also, when it works it will ask me if I want to keep content of Clipboard every time it closes next file. Is there any way to confirm that automatically?

I would appreciate any help.

[Kyle123]

Does this have to be done from Excel? I'd do this from the cmd prompt.

Where

Please Login or Register  to view this content.

For your code, I suspect that you need to explicitly put the filepath in the open command, just the name of the csv you're opening isn't sufficient

i.e

Please Login or Register  to view this content.

[damkru]

For your code, I suspect that you need to explicitly put the filepath in the open command, just the name of the csv you're opening isn't sufficient

Kyle, that worked like a charm. BIG thank you.
Would you be able also advise something to avoid that question about the Clipboard?

[haripopuri]

If you still want to do it in excel...

Please Login or Register  to view this content.

[haripopuri]

Would you be able also advise something to avoid that question about the Clipboard?

Have to use below line to avoid the large data prompt.

Please Login or Register  to view this content.

[damkru]

I still want to use Excel because I need to cut first and last 9 rows from origin files. Combining all suggestions I got macro that does exactly what I wanted. Thank you very much for your help.

This is final version of the code:

Please Login or Register  to view this content.

[haripopuri]

If query is resolved, please update the status of thread to solved

[haripopuri]

Sorry didnt notice that its already updated. Please ignore