External Data Problem

Hi, I've written some code that will open csv files in a given directory and copy in relevant data. This works ok but I want a slicker way of doing this therefore am looking to use the get external data function. I've recorded a macro and this runs fine, however, when I try to substitute the filename with a variable, I get an error. My code is as follows:

Please Login or Register  to view this content.

This code resides within a module

Please Login or Register  to view this content.

I suppose my query is, how can I get this code to work so that I can vary the csv filename in a loop??

Thanks in anticipation

Mike

Q. Why error on substituting a filename?
A. CSVFile is defined and constructed in Sub OpenTextFile() but this value is not passed to Sub ImportCSV(). CSVFile in Sub ImportCSV() is empty and therefore the error occurs trying to open an unspecified filename. To resolve this, pass CSVFile to ImportCSV procedure. General idea is as below:

Please Login or Register  to view this content.

This was tested on Excel 2010 and observed to be working.

Q. How would you like to vary the CSV filename in a loop?
By user input per loop or is there already a list of filenames that requires to be loaded?

Hope my suggestion helps with your case.

Hi, many thanks for your help. I'll amend and try this today. The CSV filename will vary because there are many files to open in the directory. I've done the code to list the files in the spreadsheet so this routine will loop through the list.