VBA -is a File watcher possible?

D

dantheman9

Board Regular
Joined
Feb 5, 2011
Messages
175
Hi Having only recently done a bit of amatuer VBA coding...my knowledge is still in the beginner stage of things....Im aware that VB supports a File watcher of some sort, but my coding hasn't got the that level as such yet....
I guess the question is, does VBA support such a feature?
what im looking for is that for each new text file created excel will import it into excel. (is that possible with VBA?)

thanks

Dan
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Perhaps...

If you can specify a location. You could loop through such said location and check the names of the files, if those files do not appear in your list, then you could import them into Excel.

But as for something akin to an event listener in excel, I'm not so sure about that...

jc
 
Upvote 0
If you could rely on the file's date/time-stamp being accurate, you could store the date/time of the latest file you've processed and periodically check the directory for anything newer. If you find anything newer, you process those files and store the new latest date/time in readiness for the next time you check.
 
Upvote 0
thanks both for ideas,

In theory, I could use the date stamp when the file is created (isn't that what file watcher in vb does anyhow?).

Thinking more about it, I guess i could set something up to import a full set of data every 5-10 mins from a set folder location, would that cause issues? or would it be better to just import new files one at a time?

I don't need all data in real time as such, so a 5 min delay is not an issue (but Im not sure if having a time delay would cause issues if it tried to update at the same time as a new txt file is been created?

If did that I guess I would have issues with editing data too.... would there be a way to pause code untill the the excel sheet im using is not in use (ie, if I was editing some data on it, vba would just reset to 5 min count again or something?)

Maybe it is just to hard to do?
 
Upvote 0
dantheman9 said:
I don't need all data in real time as such, so a 5 min delay is not an issue (but Im not sure if having a time delay would cause issues if it tried to update at the same time as a new txt file is been created?
Click to expand...

You could set it so that it only processed a file which was more than a certain age, e.g. only if the file date/time was more than five minutes (or whatever) ago.

I'm just playing with some coding now. Let me know if you'd like a copy.
 
Upvote 0
Hi Ruddles,

yes please!
that would be most useful (that would be the second time you have helped me out!!!)
:biggrin:
 
Upvote 0
dantheman9 said:
yes please!
that would be most useful (that would be the second time you have helped me out!!!)
Click to expand...

I'm actually doing a three-for-the-price-of-two this month! :)

You could do something like this: create a new workbook and create a worksheet called "Hidden". (You can call it what you like eventually - just change the bits in blue - but for the moment let's use that name.) Don't hide it whilst you're testing but you can hide it when you're running live if you want to.

Change the bits in red to point to the folder where your files are expected to appear and to select the file type. The value in pink says: "even if it's a new file, I'm going to ignore it until it's thirty seconds old". Set this to whatever value it needs to be to avoid trying to open it whilst it's still being created.

When/how to run it? You can run it from a command button on your worksheet; from one of the event handlers; or at regular intervals using .OnTime.

But for the moment just get it running. Run it once against a folder containing some .txt files so that it can store the latest date/time stamp, then edit an existing file or create a new file and run it again. Remember it won't detect any files modified less than thirty seconds ago.

If you want to delete the file after processing it, I've left a Kill command in there: just uncomment it. You might want to do this to prevent files being modified and being detected again - you need to think about this bit of the process.

Code: 
[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Public Sub DetectNewFiles()
  
  Const sFolder As String = "[COLOR=red][B]C:\Temp\[/B][/COLOR]"  [COLOR=green]   ' folder to watch
[/COLOR]  Const sFileSpec As String = "[COLOR=red][B]*.txt[/B][/COLOR]"      [COLOR=green]' type of file to watch
[/COLOR]  Const sAgeSelect As String = "[B][COLOR=magenta]00:00:30[/COLOR][/B]"  [COLOR=green]' ignore files newer than this
[/COLOR]  
  Dim sFileName As String
  Dim dFileStamp As Date
  Dim iFiles As Integer
  Dim iNewFiles As Integer
  
  Dim dLastFileProcessed As Date
  Dim dLatestFileDetected As Date
  
  dLastFileProcessed = ThisWorkbook.Sheets("[COLOR=blue][B]Hidden[/B][/COLOR]").Range("A1")
  
  sFileName = Dir(sFolder & sFileSpec)
  Do While sFileName <> ""
    dFileStamp = FileDateTime(sFolder & sFileName)
    If dFileStamp > dLastFileProcessed And dFileStamp < Now() - TimeValue(sAgeSelect) Then
      iNewFiles = iNewFiles + 1
[/SIZE][/FONT][FONT=Courier New][SIZE=1][COLOR=green]      '
      ' this is the point at which a new file has been detected: insert
      ' some coding here to process it as required
      '
      ' during testing we'll just display the file name on screen[/COLOR][/SIZE][/FONT]
[FONT=Courier New][SIZE=1][COLOR=green]      '
[/COLOR]      MsgBox "New file: " & sFileName & " (" & Format(dFileStamp, "dd/mm/yyyy hh:nn:ss") _
         & ")" & Space(10), vbOKOnly + vbInformation[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   [COLOR=green]   '[/COLOR][/SIZE][/FONT]
[FONT=Courier New][SIZE=1][COLOR=green]      ' maybe you want to delete the file after processing it?[/COLOR][/SIZE][/FONT]
[COLOR=green][FONT=Courier New][SIZE=1]      ' Kill sFolder & sFileSpec
[/SIZE][/FONT][FONT=Courier New][SIZE=1]      '[/SIZE][/FONT][/COLOR]
[FONT=Courier New][SIZE=1][COLOR=green]      ' end of  coding to process new file[/COLOR][/SIZE][/FONT]
[FONT=Courier New][SIZE=1][COLOR=green]      '
[/COLOR]      If dFileStamp > dLatestFileDetected Then dLatestFileDetected = dFileStamp
    End If
    iFiles = iFiles + 1
    sFileName = Dir()
  Loop
  
[/SIZE][/FONT][FONT=Courier New][SIZE=1][COLOR=green]  ' if we found any new files, store the latest date/time stamp back in the hidden worksheet
[/COLOR]  If iNewFiles > 0 Then ThisWorkbook.Sheets("[B][COLOR=blue]Hidden[/COLOR][/B]").Range("A1") = dLatestFileDetected
    
  MsgBox "Done: " & CStr(iNewFiles) & " new file" & IIf(iNewFiles = 1, "", "s") & " found" _
     & Space(10) & vbCrLf & vbCrLf, vbOKOnly + vbInformation
      
End Sub[/SIZE][/FONT]

Let me know how it goes?
 
Upvote 0
Oops!


That should be Kill sFolder & sFileName, not Kill sFolder & sFileSpec! :oops:

 
Upvote 0
Hi Ruddles,

that works well!

If I use an On Time with it....will that stop me from editing the sheet too?
say for eg. if i wanted to copy the data to a shared workbook, or use the data in place

I would need to leave the files in place (as the folder would be the only store for them...so won't use the Kill command)

I guess On.Time could be used with the below, I would the set this with a button to stat mointoring, and another to stop...


Code: 
Sub RepeatMacro()
Application.OnTime Now + TimeValue("00:10:00"), "RepeatMacro"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=TimeValue("00:10:00"), Procedure:="RepeatMacro", Schedule:=False
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:10:00"), "RepeatMacro"
End Sub
 
Upvote 0
If you're going to run the macro using .OnTime then you'll have to make sure nothing you're doing will upset anything the macro needs to do, and vice versa. Also, since nothing else can happen whilst VBA is running, you'll find that Excel may stop responding for a moment whilst the macro is actually running.

I would recommend that you set up a development environment where you can resolve these issues before you commit to using this approach operationally.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Split Workbook VBA w/Default Value for Sensitivity Labels
Replies
1
Views
466
Jeffrey Mahoney
Jeffrey Mahoney
J
  • Question
Forced to discard digital signature after opening and immediately saving an Macro-Enabled Excel file
Replies
0
Views
164
JDCreate
J
M
  • Question
Insert merged row into Excel, with duplication of the row(s) formatting above, using Command Button
Replies
1
Views
444
sxhall
S
E
  • Question
Macro to Pull Summary from Multiple Files
Replies
2
Views
301
dreid1011
dreid1011
E
  • Question
Trying to open misbehaving XLSM file: eliminating macros as a cause?
Replies
4
Views
757
EdNerd
E

Forum statistics

Threads
1,216,106
Messages
6,128,863
Members
449,473
Latest member
soumyahalder4

Share this page

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
Back
Top