How to I get a macro in Excel 2007 to import multiple text file data (diffent names each time)?

He'll save the files (each file will always have a differtent name)
He wants to build a marco and run it that imports all the files from his f:\ location. The first one imported will start at A1 and this file could be lord knows how long before hte newt starts to improt. (Maybe finished up at A50 or A500...not real sure) The next file that imports would pick up on the next row after the previous file copied.  

Is there a "clean" way to do this?

Here's a copy of the macro.
Sub ImportDataFiles()
'
' ImportDataFiles Macro
'
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\CT_120105_CF.txt", _
        Destination:=Range("$A$10"))
        .Name = "CT_120105_CF_5"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
CPChem1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael FowlerSolutions ConsultantCommented:
Attached is a sub from
http://www.zerrtech.com/content/excel-vba-open-all-files-a-directory

that gives a good example of how to loop through all files in a directory. The example opens xls files but would ve easy to edit for your specifications.

After each file is inserted you can use

firstRow = Range("A" & rows.count).end(xlup)

to get the first blank row after the data.

Questions
What format will the data be in the text files?
How is this data to be inserted in to the spreadsheet?
Do the text files need to be deleted or moved after insertion?

Michael
Sub fileloop()
    Dim MyDir As String
    Dim strPath As String
    Dim vaFileName As Variant
    Dim i As Integer
    
    MyDir = ActiveWorkbook.Path
    strPath = MyDir & "\files"

    With Application.FileSearch
        .NewSearch
        .LookIn = strPath
        .SearchSubFolders = False
        .Filename = ".xls"

        If .Execute > 0 Then

            For Each vaFileName In .FoundFiles
                ' open the workbook
                Workbooks.Open vaFileName
        
                ' put "Hello" in A1 in each file
                With ActiveWorkbook
                    .Worksheets("Sheet1").Cells(1, 1).Value = "Hello"
                    .Save
                    .Close
                End With
            Next
        End If
    End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CPChem1Author Commented:
thank you so much! Let me find out more info from him and get back with you...soon
0
CPChem1Author Commented:
Answers to questions:
What format will the data be in the text files?

Answer: Comma delimited.

How is this data to be inserted in to the spreadsheet?

Answer: Rows

Do the text files need to be deleted or moved after insertion?

Answer:  The text files don’t need to be deleted or moved from their location after they are improted to Excel.  
Once the files are in the spreadsheet, he will sort and manipulate the data.  After the first .txt file is imported into the spreadsheet, he wants the next file to be placed in the next available empty row just below the previously inserted file.  This would then continue until he runs out of .txt files or the spreadsheet is full.  He anticipates that he will have 1,000 plus files so he may fill many spreadsheets with 65,000+ rows of data.  

Does this help? :-)
0
CPChem1Author Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.