Help importing, updating, and automating excel sheets

[ellipsis]

Hi everyone! I am having some trouble setting up a master excel sheet that can automatically pull data from a folder when it is opened.

I currently work with an inefficient data management system in which I save a file as a text (which I can't change), I then re-save it as an excel sheet, copy the cells, paste into another sheet that puts the data into proper formatting, and the I have to copy and paste it into the master excel sheet. From there it gets sorted, added to, and eventually removed, but that's not what I'm having difficulty with.

I tried to set up a query, but when I modify the data in the source file it doesn't update in the master file when I click refresh. I think it has to do with the security settings on my computer and since it's a work computer I can't really get the privileges I need. I ended up finding this code (see below) which allows me to import text files, but puts each file into separate worksheets which doesn't really help me.

The bottom line is that I need some code that will bring all the text or excel files from a folder and compile all the data in a single worksheet. Also I need it to only import the data once so that if I alter or remove something it isn't re-imported. Any advice that anyone can give me would be very helpful, thanks!

P.S. I'm using MS office 2003, and if needed I can use MS Access, but I am not too familiar with it so I prefer not to.

Sub CombineTextFiles()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String
 
On Error GoTo ErrHandler
Application.ScreenUpdating = False
 
sDelimiter = "|"
 
FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt), *.txt", _
MultiSelect:=True, Title:="Text Files to Open")
 
If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If
 
x = 1
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:="|"
x = x + 1
 
While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=sDelimiter
End With
x = x + 1
Wend
 
ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub
 
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub