Auto Update

gg

Well-known Member
Joined
Nov 18, 2003
Messages
560
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?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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.
Code:
'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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top