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