I'm trying to find out an answer for the last month everyday.
I have a script who automatically open a new file that appear in a folder and I want for that file to run a macro.
I've made a macro and save it to personal.xls. The macro name is Macro1 (I've tried to name him Auto_Open but the result was the same)
When I open that file from My computer I can see the macros and I can run them, but if the file is opened by the script I can't see the macros and I can't run them. The error that I receive is: "Microsoft Office Excel:1004 The macro Macro1 cannot be found"
Please help me. I send you below all the script maybe there is someone who can tell me what is wrong.
Thank you very very much.
//The incoming folder
Let>InFolder=E:\Watch\Transfer2\
//Processed Log File
Let>ProcessedLog=E:\Watch\Transfer2\processed.log
//Open new File
VBSTART
Sub OpenAndRun(xlFileName, MacroName)
Set xlApp = CreateObject("Excel.Application")
'remove next line to make Excel invisible
xlApp.visible = true
Set xlBook = xlApp.WorkBooks.Open(xlFileName)
xlApp.Run(MacroName)
'Uncomment next line to quit Excel
'xlApp.quit
End Sub
VBEND
Label>mainloop
//Get a list of folders in InFolder
GetFileList>%InFolder%\*.*,file_list,;
Separate>file_list,;,files
//Loop through folder list
If>files_count>0
Let>k=0
Repeat>k
Let>k=k+1
Let>ThisFile=files_%k%
//Does this one exist in processed.log
Let>SendIt=TRUE
IfFileExists>ProcessedLog
ReadFile>ProcessedLog,ProcessedList
Position>ThisFile,ProcessedList,1,p
If>p>0
//This file already processed
Let>SendIt=FALSE
Endif
Endif
//Ok to process this file
If>SendIt=TRUE
//Write to processed log
WriteLn>ProcessedLog,r,ThisFile
//Open the file
VBRun>OpenAndRun,ThisFile,Macro1
Endif
Until>k=files_count
I have a script who automatically open a new file that appear in a folder and I want for that file to run a macro.
I've made a macro and save it to personal.xls. The macro name is Macro1 (I've tried to name him Auto_Open but the result was the same)
When I open that file from My computer I can see the macros and I can run them, but if the file is opened by the script I can't see the macros and I can't run them. The error that I receive is: "Microsoft Office Excel:1004 The macro Macro1 cannot be found"
Please help me. I send you below all the script maybe there is someone who can tell me what is wrong.
Thank you very very much.
//The incoming folder
Let>InFolder=E:\Watch\Transfer2\
//Processed Log File
Let>ProcessedLog=E:\Watch\Transfer2\processed.log
//Open new File
VBSTART
Sub OpenAndRun(xlFileName, MacroName)
Set xlApp = CreateObject("Excel.Application")
'remove next line to make Excel invisible
xlApp.visible = true
Set xlBook = xlApp.WorkBooks.Open(xlFileName)
xlApp.Run(MacroName)
'Uncomment next line to quit Excel
'xlApp.quit
End Sub
VBEND
Label>mainloop
//Get a list of folders in InFolder
GetFileList>%InFolder%\*.*,file_list,;
Separate>file_list,;,files
//Loop through folder list
If>files_count>0
Let>k=0
Repeat>k
Let>k=k+1
Let>ThisFile=files_%k%
//Does this one exist in processed.log
Let>SendIt=TRUE
IfFileExists>ProcessedLog
ReadFile>ProcessedLog,ProcessedList
Position>ThisFile,ProcessedList,1,p
If>p>0
//This file already processed
Let>SendIt=FALSE
Endif
Endif
//Ok to process this file
If>SendIt=TRUE
//Write to processed log
WriteLn>ProcessedLog,r,ThisFile
//Open the file
VBRun>OpenAndRun,ThisFile,Macro1
Endif
Until>k=files_count