Reference Text File To Import Using Generic Letter or Number

**R_S_6** · 01-23-2009, 12:35 PM

Hi all,

I have recorded some script in excel to import a *.dat file, delete the first 19 rows of data, then export the data as a *.csv file.

My problem is I have circa 30 *.dat files to import, all of which have large file names and I am fed up with copying and pasting them in.

So my question is, in the below code, can I somehow use a reference at the start such as:

abc1.dat = "1"
abc2.dat = "2"

Then where the files were used, reference the "1".dat and its exported version "1".csv for the abc1.dat case.

Please Login or Register  to view this content.

Appologies as I am somewhat *poor* at VBA code - hence my use of the Recorder.

Cheers

**Leith Ross** · 01-23-2009, 08:13 PM

Hello R_S_6,

This version of your macro allows you to insert the file names into an array. Here the names are hard coded into the macro. The file names could be read from a sheet in the macro is running in. I would need to know what the sheet name is and the available column to have the macro read the file names in automatically.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**R_S_6** · 01-26-2009, 04:40 AM

Hi Leith, thanks for your reply.

If I were to call the sheet in which the file names are stored 'File_Names' and the file names were listed in Column A starting at Row 1...

Can you show me the required alterations to your piece of code?

Thanks Again!

Cheers

**R_S_6** · 01-26-2009, 06:37 AM

Just had a go with the code you provided. (Although I prefer your idea of the file names in a sheet!)

I had to modify this line:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

As it came up with an error here (Compile Error).

After that it hit the following error at line:

Please Login or Register  to view this content.

Saying 'Compile Error: Sub or Function not defined' Not a clue on this one!

Thought it would be good to highlight the above before hopefully

you manage to alter the code to read in the file names from 'Column A' of Sheet 'File_Names'.

Thanks Again!

**R_S_6** · 01-26-2009, 02:16 PM

Bump - Any Ideas

**Leith Ross** · 01-26-2009, 03:02 PM

Hello R_S_6,

I have made the changes to the macro. Thanks for catching the typo. My typing skills are not very good. The file names are now read from the worksheet named "File_Names" column "A" starting with row 1 down to the last entry in the column.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**R_S_6** · 01-27-2009, 07:06 AM

I've hit an issue on line:

Please Login or Register  to view this content.

It says:

Run-time error '1004':

Excel cannot find the text file to refresh this external data range

Check to make sure the text file has not been moved or renamed, then try the refresh again

I have double checked the folder extension to the test *.dat files, and also that the folder is present and correct to receive the converted *.dat files to *.csv files.

Any ideas?

Cheer Leith!

Mark

**Leith Ross** · 01-27-2009, 08:14 PM

Hello Mark,

Do you know how many files have been opened when the error occurs?

**R_S_6** · 01-28-2009, 06:34 AM

Morning Leith,

Just had another quick go at running the macro - fell over at the same point. Don't believe it has opened any files as yet, as none of the '*.dat' files have been written out in '*.csv' format in the destination folder 'D:\TEMP\X123\'.

If you want to check the macro on your machine I have attached the 3 sample files, source folder and destination folder I am using as a test of the system/macro. Feel free to alter the destination folders etc as per a desired location on your machine.

Also added test worksheet with labelled 'File_Names' sheet and listed file names to look up.

Cheers

Mark