Error in .COM(x, name,...) or Microsoft Excel cannot access excel error with Windows task scheduler.

[zlfang00]

Don't know anybody ever see this before.

I have a R script myRscript.r to run following codes:

  library(RDCOMClient)
  ex <- COMCreate("Excel.Application")
  wb <- ex$Workbooks()$Open("myexcel.xlsx")  

If I run the R script in windows command prompt directly, or put the script Rscript myRscript.r > myRscript.r .Rout in a Windows bat file and execute the bat file, I get no issues. the excel file, myexcel.xlsx, is opened as expected.

However, if I call the bat file in Windows Scheduler task manager, with Program/script as "C:\myscriptfolder\run.bat", then I always get issue to open the excel file.

Here is the error.

Error in .COM(x, name, ...): Microsoft Excel cannot access the file 'C:/myscriptfolder/myexcel.xlsx'. There are several possible reasons:

? The file name or path does not exist. ? The file is being used by another program. ? The workbook you are trying to save has the same name as a currently open workbook. (Microsoft Excel)

I tested it many times, and the issue can be reproduced. My guess is Windows Scheduler task manager run the bat file differently from windows command prompt, but any one knows how to solve this?

Thanks

[duncantl]

Hi. As you do, I strongly suspect that the task manager runs the .bat and R session with a different working directory. One approach is your .bat file can provide an absolute path the .xlsx file

You can determine the working directory of the .bat file by either finding it in the Window's help files or by creating a .bat file that reports the working directory. You could create the latter with an R script if necessary that does something like

cat(getwd(), "\n", file = "C:/some/file/in/your/home/directory")

There should be some way to provide arguments in the task scheduler or in the .bat file to pass to the R script. But it is probably easier to just condition the R script.

[zlfang00]

Good point, I did what you suggested, and was able to cat current working directory.

What I observed is, the absolute path of the excel file is "C:/Users/My/directory/myexcel.xlsx" changes when running with Scheduler task, the error message shows it becomes as "C://Users/My/directory/myexcel.xlsx". The "//" added to the file path causes those errors, don't know why it is changed in .COM(x, name,...) in Windows task Scheduler environment.

Then I tried setting working directory first as setwd("C:/Users/My/directory"), then open the excel file name directly: ex$Workbooks()$Open("myexcel.xlsx") in R script. The error of .COM becomes:

-2147352567 Error in .COM(x, name, ...): Sorry, we couldn't find myexcel.xlsx. Is it possible it was moved, renamed or deleted? (Microsoft Excel) On Fri, Jul 21, 2023 at 11:14 PM Duncan Temple Lang < ***@***.***> wrote: > Hi. > As you do, I strongly suspect that the task manager runs the .bat and R > session with a different working directory. > One approach is your .bat file can provide an absolute path the .xlsx file > > You can determine the working directory of the .bat file by either finding > it in the Window's help files or by creating a .bat file that reports the > working directory. You could create the latter with an R script if > necessary that does something like > > cat(getwd(), "\n", file = "C:/some/file/in/your/home/directory") > > There should be some way to provide arguments in the task scheduler or in > the .bat file to pass to the R script. But it is probably easier to just > condition the R script. > > — > Reply to this email directly, view it on GitHub > , > or unsubscribe > > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> >

[zlfang00]

the error message with the absolute excel path update="C:/Users/Mydirectory/myexcel.xlsx" is,

cat(getwd(), "\n", update) C:/Users/Mydirectory C:/Users/Mydirectory/myexcel.xlsx wb <- ex$Workbooks()$Open(update)

-2147352567 Error in .COM(x, name, ...): Microsoft Excel cannot access the file 'C://Users/Mydirectory/myexcel.xlsx'. There are several possible reasons:

• The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook. (Microsoft Excel)

On Mon, Jul 24, 2023 at 12:09 PM Zaili Fang @.***> wrote:

Good point, I did what you suggested, and was able to cat current working directory.

What I observed is, the absolute path of the excel file is "C:/Users/My/directory/myexcel.xlsx" changes when running with Scheduler task, the error message shows it becomes as "C://Users/My/directory/myexcel.xlsx". The "//" added to the file path causes those errors, don't know why it is changed in .COM(x, name,...) in Windows task Scheduler environment.

Then I tried setting working directory first as setwd("C:/Users/My/directory"), then open the excel file name directly: ex$Workbooks()$Open("myexcel.xlsx") in R script. The error of .COM becomes:

-2147352567 Error in .COM(x, name, ...): Sorry, we couldn't find myexcel.xlsx. Is it possible it was moved, renamed or deleted? (Microsoft Excel) On Fri, Jul 21, 2023 at 11:14 PM Duncan Temple Lang < ***@***.***> wrote: > Hi. > As you do, I strongly suspect that the task manager runs the .bat and R > session with a different working directory. > One approach is your .bat file can provide an absolute path the .xlsx file > > You can determine the working directory of the .bat file by either > finding it in the Window's help files or by creating a .bat file that > reports the working directory. You could create the latter with an R script > if necessary that does something like > > cat(getwd(), "\n", file = "C:/some/file/in/your/home/directory") > > There should be some way to provide arguments in the task scheduler or in > the .bat file to pass to the R script. But it is probably easier to just > condition the R script. > > — > Reply to this email directly, view it on GitHub > , > or unsubscribe > > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> >

[zlfang00]

other R functions should be able to open "C://User/..." Don't know why .COM(x, name,..) can not

On Mon, Jul 24, 2023 at 12:15 PM Zaili Fang @.***> wrote:

the error message with the absolute excel path update="C:/Users/Mydirectory/myexcel.xlsx" is,

cat(getwd(), "\n", update) C:/Users/Mydirectory C:/Users/Mydirectory/myexcel.xlsx wb <- ex$Workbooks()$Open(update)

-2147352567 Error in .COM(x, name, ...): Microsoft Excel cannot access the file 'C://Users/Mydirectory/myexcel.xlsx'. There are several possible reasons:

• The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook. (Microsoft Excel)

On Mon, Jul 24, 2023 at 12:09 PM Zaili Fang @.***> wrote:

Good point, I did what you suggested, and was able to cat current working directory.

What I observed is, the absolute path of the excel file is "C:/Users/My/directory/myexcel.xlsx" changes when running with Scheduler task, the error message shows it becomes as "C://Users/My/directory/myexcel.xlsx". The "//" added to the file path causes those errors, don't know why it is changed in .COM(x, name,...) in Windows task Scheduler environment.

Then I tried setting working directory first as setwd("C:/Users/My/directory"), then open the excel file name directly: ex$Workbooks()$Open("myexcel.xlsx") in R script. The error of .COM becomes:

-2147352567 Error in .COM(x, name, ...): Sorry, we couldn't find myexcel.xlsx. Is it possible it was moved, renamed or deleted? (Microsoft Excel) On Fri, Jul 21, 2023 at 11:14 PM Duncan Temple Lang < ***@***.***> wrote: > Hi. > As you do, I strongly suspect that the task manager runs the .bat and R > session with a different working directory. > One approach is your .bat file can provide an absolute path the .xlsx > file > > You can determine the working directory of the .bat file by either > finding it in the Window's help files or by creating a .bat file that > reports the working directory. You could create the latter with an R script > if necessary that does something like > > cat(getwd(), "\n", file = "C:/some/file/in/your/home/directory") > > There should be some way to provide arguments in the task scheduler or > in the .bat file to pass to the R script. But it is probably easier to just > condition the R script. > > — > Reply to this email directly, view it on GitHub > , > or unsubscribe > > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> >