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:

Sub OpenTextFile()

    Dim CSVFilename As String
    Dim Pathname As String
    Dim CSVFile As String
    
'Set the variables
    
    CSVFilename = "datafile.csv"
    Pathname = "C:\Documents and Settings\My Documents\"
    CSVFile = "Text;" & Pathname & CSVFilename
    
    Application.DisplayAlerts = False    
    ActiveSheet.Range("A1:Z5000").ClearContents
'Run the import process

    ImportCSV ' See below for this code
        
End Sub

This code resides within a module

Sub ImportCSV()

With ActiveSheet.QueryTables.Add(Connection:= _
        CSVfile, Destination:=Range("A1"))
        .Name = "Journal Data Import"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(4, 4, 1, 1, 1, 1, 4, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

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:

Sub OpenTextFile()

    '... Code as normal

    ImportCSV (CSVFile)
        
End Sub

Sub ImportCSV(ByVal CSVFile as string)

   '... Code as normal

End Sub

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.