Auto Update

[gg]

I have an excel file that contains daily data sent via e-mail.
Currently I copy and paste the data.

I would rather detach the data into a specific folder and have Excel update itself based on the all the new files in a folder?

I know this can be done, but I am not sure how to do it. Especially with different file names each day.

Any suggestions?

 

[gg]

Bump

 

[Gates Is Antichrist]

I can only imagine doing this with VBA. There are a lot of details to address, but I'll try to hit a couple.

You could save the latest already-processed as-of date/time in a control cell - perhaps protected. Dates are easy to compare, and you can process the file or files that exceed the saved date, without knowing the file name.

Start by perusing the help for the Dir function, especially the example, and check out the "see alsos" for more ideas.

'This example uses the Dir function to check if certain files and directories exist.

Dim MyFile, MyPath, MyName   ' Returns "WIN.INI" if it exists.
MyFile = Dir("C:\WINDOWS\WIN.INI")	

' Returns filename with specified extension. If more than one *.ini
' file exists, the first file found is returned.
MyFile = Dir("C:\WINDOWS\*.INI")

' Call Dir again without arguments to return the next *.INI file in the 
' same directory.
MyFile = Dir

' Return first *.TXT file with a set hidden attribute.
MyFile = Dir("*.TXT", vbHidden)

' Display the names in C:\ that represent directories.
MyPath = "c:\"	' Set the path.
MyName = Dir(MyPath, vbDirectory)	' Retrieve the first entry.
Do While MyName <> ""	' Start the loop.
    ' Ignore the current directory and the encompassing directory.
    If MyName <> "." And MyName <> ".." Then
        ' Use bitwise comparison to make sure MyName is a directory.

        If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
            Debug.Print MyName	' Display entry only if it
        End If	' it represents a directory.
    End If
    MyName = Dir	' Get next entry.
Loop

Well, nuts, I thought GetAttr would get the file date. You can try hunting that one down. Anyway this forms a framework for you to FIND the file. Then reading the data might look like

Dim InputData
Open "MYFILE" For Input As #1	' Open file for input.
Do While Not EOF(1)	' Check for end of file.
    Line Input #1, InputData	' Read line of data.
    Debug.Print InputData	' Print to Debug window.
Loop
Close #1	' Close file.

Of course, if this is for distribution, you should consider lots of error checking not shown above.
HTH