Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mariadziewulska
Starting Member
3 Posts |
 Posted - 2005-01-10 : 11:42:08
|
 I am trying to automate a procedure. > Every time a new invoice comes into the invoice folder eg: C:\Invoices\NewInvoice.txt > 1.I want to run an excel programme eg: C:\Invoices\Book1.xls automatically > 2. And then Run a macro in Access eg:C:\Invoices\InvoiceDatabase\Macro7 > 3. And then Delete the invoice folder in C:\Invoices\NewInvoice.txt > > How do I do this in SQL?? Many Thanks in advance Maria |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-10 : 14:02:55
|
| Why?Create a job which scans the directory repeatedly waiting for a file to turn up. Process the file then move it to an archive directory (you can change it to delete if you wish).seehttp://www.mindsdoor.net/SQLTsql/s_ProcessAllFilesInDir.htmlYou just have to create @ProcSp to do the processing.Probably using xp_cmdshell or the sp_oa com procedures to do the rest of the processing.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mariadziewulska
Starting Member
3 Posts |
Posted - 2005-01-11 : 06:51:14
|
| Thanks for your reply!However I don't actually understand what the second half of the code is doing and thus am having difficulty interpreting it.What is this doing: select @cmd = 'dir/B' + @FilePath + @FileMaskHow do I run my excel and Access macro??Many ThanksMaria |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2005-01-11 : 07:18:51
|
| Mariadir /B is a dos command to suppress heading information@FilePath is a variable to the directory which in Nigels script has been set to C:@FileMask is a variable to a type of file *.txtSo Nigels script would process all txt files in C:You can run an Access macro automatically by creating a macro called AutoExec which will automatically run when the database is opened or via command line Example"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\PathToDB\MyDb.mdb" /x MyMacroNameThe first bit is the path to the application followed by the path to the database with a switch of /x and the macro required to run. The quotes are required if the paths have spaces in themHTHAndy |
|
|
|
mariadziewulska
Starting Member
3 Posts |
Posted - 2005-01-11 : 09:38:04
|
| Hi Andy,Thank you for your reply which makes has clarified a few things for me.Can I actually write this whole stored procedure in notepad and then call and run it in dos??Maria |
|
|
|
|
|
|
Batch Procedures/stored Procedure??