csv files into excel worksheets

[TjS]

I'm building a macro to pull information from multiple worksheets. The source of the data for each sheet is a csv file. I will be building a form where the user can browse and import the csv files. How can I write the VBA to pull File1.csv file into Worksheet1, File2.csv file into Worksheet2, File3.csv into Worksheet3, etc. in a single VBA event? (The names were just examples).

Please include code.

 

[TjS]

found it

Sub ImportCSV()

    Dim strPath As String
    Dim strFile As String
     '
    strPath = "C:\temp\"
    strFile = Dir(strPath & "*.csv")
    Do While strFile <> ""
        With ActiveWorkbook.Worksheets.Add
            With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _
                Destination:=.Range("A1"))
                
'               .Parent.Name = Replace(strFile, ".csv", "")  
                .TextFileParseType = xlDelimited
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = xlWindows
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = True
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileOtherDelimiter = "|"
                .Refresh BackgroundQuery:=False
            End With
        End With
        strFile = Dir
     Loop
     
End Sub

 

[TjS]

As i said it works...But i have a slight problem, probably with some long file names, which doesn't fit in in the worksheet names, and the code stops at the points where the filename is too long, a couple of names are equal to the filename, but some names which are (too) long(?) are skipped and the code is stopped.

Can anyone please advise how to cope with this (adding a number to the worksheet name automaticly for example?, but again, how to?)

thanks in advance

 

[Happy YN]

opposite

Just wondered how to do the opposite. I want to write a module that goes thru every sheet of an excel file and create a csv for each one. sheet1 to csv1, sheet2 to csv2 etc.
Any ideas
Thanks

 

[shades]

Howdy, Check out the knowledge articles at www.vbaexpress.com Some of this is already written with directions on how to setup and use.
________
360 GTC