• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1770
  • Last Modified:

Excel VBA - Combining text files, adding file name to a column

Hi,


Does anyone have any code that could do this?


Thanks
Kieran
0
kieranjcollins
Asked:
kieranjcollins
1 Solution
 
gowflowCommented:
yes sure can you be more specific and post a workbook ?
to add the current file name of an open workbook to Cell A1 you do this:

Range("A1") = Activeworkbook.FullName

gowflow
0
 
mvidasCommented:
Hi Kieran,

Is this what you're looking for? It will prompt you to select the file(s) you want to import, ask you if there is a delimiter, then import them with the file path/name in column A and each line starting in column B.
Sub KieranImportFiles()
 Dim vFF As Long, vFiles As Variant, vFile As Variant, FileCont(), filecnt As Long, i As Long
 Dim vDelim As String, vRow As Long
 
 vFiles = Application.GetOpenFilename("Text Files,*.txt;*.csv,All Files,*.*", MultiSelect:=True)
 If LCase(vFiles(1)) = "false" Then Exit Sub 'hit cancel
 vDelim = InputBox("What is the file delimiter? Enter blank for fixed width.", "Enter delimiter", ",")
 
 filecnt = 0
 ReDim FileCont(1, filecnt)
 For Each vFile In vFiles
  vFF = FreeFile
  Open vFile For Input As #vFF
  Do Until EOF(vFF)
   Line Input #vFF, TempStr
   ReDim Preserve FileCont(1, filecnt)
   FileCont(0, filecnt) = vFile
   FileCont(1, filecnt) = Split(TempStr, vDelim)
   filecnt = filecnt + 1
  Loop
  Close #vFF
 Next
 filecnt = filecnt - 1
 
 Application.ScreenUpdating = False
 Workbooks.Add 1
 vRow = 1
 For i = 0 To filecnt
  Cells(vRow, 1) = FileCont(0, i)
  Range(Cells(vRow, 2), Cells(vRow, UBound(FileCont(1, i)) + 2)) = FileCont(1, i)
  vRow = vRow + 1
 Next 'i
 Application.ScreenUpdating = True
End Sub

Open in new window

If not, please give an example of what you're looking to do, or describe it a little better.

Matt
0
 
kieranjcollinsAuthor Commented:
That code worked a treat, thank you
0
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now