Activation...When File Saved/Downloaded

[mtomas]

I run an extraction software that extracts data from websites and places all the data in a grid then saves the grid(spreadsheet) as an excel file on my desktop automatically with the same name everytime.. (i eventually rename this file once i process it myself in excel)

 

i am trying to run a macro and have this event (when this file.xls is saved onto my desktop) activate my macro. I have toyed with the directory modification but that doesnt seem to trigger the macro... Has anyone tried this activation trigger ?

 

 

basically once this file.xls appears saved on my desktop, that should trigger my macro to run. any help would be appreciated.

 

 

thanks

[terrypin]

I run an extraction software that extracts data from websites and places all the data in a grid then saves the grid(spreadsheet) as an excel file on my desktop automatically with the same name everytime.. (i eventually rename this file once i process it myself in excel)

 

i am trying to run a macro and have this event (when this file.xls is saved onto my desktop) activate my macro. I have toyed with the directory modification but that doesnt seem to trigger the macro... Has anyone tried this activation trigger ?

 

 

basically once this file.xls appears saved on my desktop, that should trigger my macro to run. any help would be appreciated.

 

 

thanks

 

The Help for the Directory Modification option could be a bit misleading. It says: "The Directory Modification activation looks for changes to files or folders. When a change is made to the file or folder specified, the macro will start." I've never used it before but there appears to be no way to specify a particular file. And the command itself reinforces this, as it is very clear, with the box labeled 'Path to watch'.

 

So in practice I think you need two stages:

1. Activate if the folder has been changed (as it will when you create your XLS file).

2. Test to see if the change has been caused by that specific file.

 

Here's an example that seems to work OK:

 

// Demo macro for mtomas (ME Pro version).

If Not File Exists: "C:\Documents and Settings\Terry\Desktop\mtomas.xls" // Skip the rest of the macro.

Macro Stop

Else

Text Box Display: // Proceed with the macro.

End If

 

And the folowing illustrates its use:

 

 

The demo macro file is: Demo_mtomas.mex

 

You can test it most conveniently, without opening Excel, by r-clicking in your folder and using the New item in the context menu to add a new XLS file. If you edit its name the macro will be activated and run to completion when the name matches your target.

 

--

Terry, East Grinstead, UK

[Yehnfikm8Gq]

I tried the Directory Modification last night. Only took a minute to write a macro. I found it worked fine when adding a file to a folder (using the Create, Rename, Delete). It does not work with the Desktop. Although ME can transfer files to and from the Desktop, it does not seem to include as a Folder as far as Directory Modification goes (Desktop is a special window rather than a folder). Perhaps some more expert comment is needed?

 

(edit) I know that many people use Desktop for transferring or downloading files. Nothing wrong with that. I use a folder called "Downloads" which would work with Directory Modification. Using a single place for transfers makes it easy to track new files and run virus scans etc.

Edited March 15, 2010 by JohnS

[terrypin]

I tried the Directory Modification last night. Only took a minute to write a macro. I found it worked fine when adding a file to a folder (using the Create, Rename, Delete). It does not work with the Desktop. Although ME can transfer files to and from the Desktop, it does not seem to include as a Folder as far as Directory Modification goes (Desktop is a special window rather than a folder). Perhaps some more expert comment is needed?

 

(edit) I know that many people use Desktop for transferring or downloading files. Nothing wrong with that. I use a folder called "Downloads" which would work with Directory Modification. Using a single place for transfers makes it easy to track new files and run virus scans etc.

 

The macro I showed above works fine here, with my desktop as its target. Did it not work for you?

 

--

Terry, East Grinstead, UK

[paul]

I tried the Directory Modification last night. Only took a minute to write a macro. I found it worked fine when adding a file to a folder (using the Create, Rename, Delete). It does not work with the Desktop. Although ME can transfer files to and from the Desktop, it does not seem to include as a Folder as far as Directory Modification goes (Desktop is a special window rather than a folder). Perhaps some more expert comment is needed?

Using Windows 7, where the Desktop folder is in C:\Users\username\Desktop, MEP's Directory activation feature works just fine.

Mind you, this is a folder which usually has quite a lot of activity, so I'm not convinced it's a good target location for files!

[terrypin]

Using Windows 7, where the Desktop folder is in C:\Users\username\Desktop, MEP's Directory activation feature works just fine.

Mind you, this is a folder which usually has quite a lot of activity, so I'm not convinced it's a good target location for files!

 

Yes, I only used it in my demo because that's the folder the OP is apparently using.

 

--

Terry, East Grinstead, UK

[Yehnfikm8Gq]

Re Desktop, definitely does not work for me in XP using "Create, Delete, Rename". I did not try terrypin's macro as that specified a particular file. As I mentioned, ME seems to respond to particular files (Move file etc) but not for monitoring new files. That could be a bug. I would treat it as "one of those ME things" and move on! Let's call it a Desktop Null issue for XP!!

[terrypin]

Re Desktop, definitely does not work for me in XP using "Create, Delete, Rename". I did not try terrypin's macro as that specified a particular file.

 

John: Just r-click in your desktop folder as I mentioned and use New to create a new Excel file mtomas.xls. Or, if your context menu doesn't offer that option, create it from Excel. It doesn't have to have any content (mine had 'test' in the first cell).

 

BTW, I assume you are fully specifying the desktop's address? Probably something like C:\Documents and Settings\John\Desktop.

 

--

Terry, East Grinstead, UK

[mtomas]

thanks for the input... i have tested this several ways..

-----------------

activation: directory modification, points to desktop c:\users\mts\desktop

MACRO:

if not file :\users\mts\desktop\indeedjobs.xls

macro stop

else

run my macro

-----------------------

 

when i run my extraction SW and it saves this indeedjobs.xls file onto my desktop, nothing happens, ME does not run the macro

 

BUT...when i double click this file, the ME macro runs, the problem is that is continues to run, so if i try to close down indeedjobs.xls & dont save changes, it just runs the macro again and opens the file again. If i save it with another name, it runs the macro again as well, (the original file name still exists on the desktop) so i have no other option but to disable this macro...i have Win7 and Excel 2007.

 

is the issue the desktop "folder", there seems not to be an activation event, BUT the macros If NOT file causes the macro to loop... see attached JPG

[terrypin]

thanks for the input... i have tested this several ways..

-----------------

activation: directory modification, points to desktop c:\users\mts\desktop

MACRO:

if not file :\users\mts\desktop\indeedjobs.xls

macro stop

else

run my macro

-----------------------

 

when i run my extraction SW and it saves this indeedjobs.xls file onto my desktop, nothing happens, ME does not run the macro

 

BUT...when i double click this file, the ME macro runs, the problem is that is continues to run, so if i try to close down indeedjobs.xls & dont save changes, it just runs the macro again and opens the file again. If i save it with another name, it runs the macro again as well, (the original file name still exists on the desktop) so i have no other option but to disable this macro...i have Win7 and Excel 2007.

 

is the issue the desktop "folder", there seems not to be an activation event, BUT the macros If NOT file causes the macro to loop... see attached JPG

 

1. You don't need to post screenshots if it's just script. Just copy/paste using R-click > Copy Command Text.

 

2. Also, do as I did: extract (and test) a fragment of macro that's exhibiting the apparently incorrect behaviour. Then export it as an MEX and attach it to your post so that we can try it without having to work too hard,

 

3. Keep the testing simple. Did you run the basic macro I wrote (after changing the path/file)? Did it work correctly? IOW, before complicating the isssue with your particular macro, can you now successfully use the Directory Modification activation option? If not, can you do so on any folder?

 

BTW, although irrelevant to the main issue, I'm unsure about some of your script:

- Why the test at line 4 to see if Excel is running? Just use the Activate or Launch command.

- You might need a short delay after line 8.

 

--

Terry, East Grinstead, UK

[Yehnfikm8Gq]

Apart from the multiple launches experienced in W7(!), the issue with XP is that I dragged a file in from a folder just like a file would appear out of the blue from the Net. If I initiate a new/delete/rename file using a Windows Explorer >>instruction<<, the macro will run.

 

(edit)

I have saved files off the Net into Desktop and a named folder. Directory Modification does not work in this situation with Desktop; it works fine with a normal folder.

 

Paul, did you try dumping a file in or did you use a file instruction like New, Rename, Delete, Move? It could be W7 works exactly the same as XP regarding the desktop; it does not respond to dumps from the Net or file drags, just from Explorer instructions.

 

If this was me, I would choose a folder instead of Desktop where the macro would run with a dumped file. I would immediately transfer the file to another folder which is not Directory Modification activated and import the file to Excel there. The transfer with the macro should prevent it running again since you cannot have two instances of one macro.

 

If it's imperative Desktop is used, I suggest someone contact ISS - that was the expert comment I was thinking of in Post #3.

[paul]

Paul, did you try dumping a file in or did you use a file instruction like New, Rename, Delete, Move? It could be W7 works exactly the same as XP regarding the desktop; it does not respond to dumps from the Net or file drags, just from Explorer instructions.

Sorry to disappoint you - but I dragged a file from a DOpus window to the desktop, and the macro was triggered.

[mtomas]

Win7 and Excel 2007 (file ext is .xlsx not .xls)

 

i moved the activation from desktop to documents folder and it seems to trigger the macro fine. I took your original macro and just created the launch program c:\users\mts\documents\indeedjobs.xlsx

 

If Not File Exists: "C:\Users\MTS\Documents\IndeedJobs.xlsx"

Macro Stop

Else

Program Launch: "indeedjobs.xlsx" (Maximized)

Parameters:

End If

 

the problem is that is continues to loop the macro when i close the excel file, when i close the indeedjobs.xlxs file it reopens again, which is correct: if file exists, program launch... any suggestions on how to correct this action?

[paul]

i moved the activation from desktop to documents folder and it seems to trigger the macro fine. I took your original macro and just created the launch program c:\users\mts\documents\indeedjobs.xlsx

 

If Not File Exists: "C:\Users\MTS\Documents\IndeedJobs.xlsx"

Macro Stop

Else

Program Launch: "indeedjobs.xlsx" (Maximized)

Parameters:

End If

 

the problem is that is continues to loop the macro when i close the excel file, when i close the indeedjobs.xlxs file it reopens again, which is correct: if file exists, program launch... any suggestions on how to correct this action?

Your macro will run whenever your Documents folder changes - which is probably very often. Since your .xlsx file (presumably?) still exists, Excel will be reopened again and again and again....

You need to find a way of informing the macro that you don't want it to run again on the current .xlsx file. You could use a different folder (one that changes infrequently, preferably under your direct control), OR you could change the .xlsx file's name once you've opened it once in Excel, OR you could store a value in the registry - but then you need to create a mechanism to erase the registry value when you do want the .xlsx file to be opened in Excel.

I think you should change the file's name (or move it to a different folder) once you have opened it in Excel.

[paul]

Re Desktop, definitely does not work for me in XP using "Create, Delete, Rename". I did not try terrypin's macro as that specified a particular file. As I mentioned, ME seems to respond to particular files (Move file etc) but not for monitoring new files. That could be a bug. I would treat it as "one of those ME things" and move on! Let's call it a Desktop Null issue for XP!!

I tried this in XP (a VMWare sesssion running under W7), and it worked perfectly! So let's call it a Desktop Null issue for JohnS' XP!!

[terrypin]

I tried this in XP (a VMWare sesssion running under W7), and it worked perfectly! So let's call it a Desktop Null issue for JohnS' XP!!

 

John: Still don't know if you remain unable to run my (adapted) test macro, after my reply in post #8?

 

--

Terry, East Grinstead, UK

[Yehnfikm8Gq]

Hi terrypin, I've been investigating other stuff which may explain some of what I found. I changed to Avast! AV yesterday. I noticed today that the behaviour I was getting on this thread became erratic. At times I could get nothing to activate my macro. While checking Avast I noticed a high number of detections. It had thrown all the macscr*.vbs files into its Virus Chest. I've set up exceptions but can't tell for the moment if that is going to be effective or was the cause of the behaviour. Mind you dumping to folders always seemed to activate the macro.

 

On the general subject of Directory Modification I would not choose the Desktop for that Activation as I think Paul intimated. I would choose a folder solely for the purpose. All those mouse click, process, window title, system, clipboard text activations need very careful thought to avoid surprises. As I've commented before, anything like that I make the macro give 10 secs of audibles prior to any action so I can safely pause/save what I'm doing. Not necessary if you have a dedicated PC for automated processes.

[terrypin]

On the general subject of Directory Modification I would not choose the Desktop for that Activation as I think Paul intimated.

 

From post #6: "Yes, I only used it in my demo because that's the folder the OP is apparently using."

 

--

Terry, East Grinstead, UK