Importing Large CSV File into Excel Using VBA

[drmingle]

The below code works, but for whatever reason it doesn't seem to recognize the commas which need to be taken into account since the data is comma delimited:

Sub Import_Large_Textfiles_DAO()
'Note: A reference must be set to Microsoft DAO 3.5 or 3.51
'Note: A reference must be set to Microsoft Scripting Runtime
    Dim stPath As String, stFilename As String, stGetFile As String
    Dim Db As DAO.Database
    Dim Rst As DAO.Recordset
    Dim fsoObj As FileSystemObject
    
    stGetFile = Application.GetOpenFilename("CSVfiles (*.csv),*.CSV", , "Please select a CSVfile...")
    
        
    Application.ScreenUpdating = False
    Set fsoObj = CreateObject("Scripting.FileSystemObject")
    stPath = fsoObj.GetFile(stGetFile).ParentFolder.Path
    stFilename = fsoObj.GetFile(stGetFile).Name
    Set Db = OpenDatabase(stPath, False, True, "TEXT;")
    Set Rst = Db.OpenRecordset("SELECT * FROM " & stFilename)
    While Not Rst.EOF
        Worksheets.Add
        ActiveSheet.Range("A1").CopyFromRecordset Rst, 10
    Wend
    Rst.Close
    Db.Close
    Application.ScreenUpdating = True
End Sub

Any help would be appreciated. I am running on MS 2007.

 

[jimrward]

what is your list separator set to, control panel > regional settings > numbers