search

gdemin / excel.link

Convenient Data Exchange between R and Microsoft Excel
56 stars 16 forks source link

How to see the logs #20

Closed rmlopes closed 5 years ago

rmlopes commented 5 years ago

Hi,

How can I see the logs of the R script that is run?

rmlopes commented 5 years ago

I am now able to provide a bit more info. The code I am trying is very simple:

lista =   xl.current.region('Sheet1!k2:k53', col.names = FALSE, row.names = FALSE)
res =   ks.test(as.numeric(lista),'pnorm',mean=mean(lista),sd=sd(lista))$p.value
xl['Sheet1!N2'] = NA
xl['Sheet1!N2'] = res

The problem is if I try to get the data from a different sheet it does not work. Moreover, If I rename my sheet (changing line 1 accordingly) is does not work either. I have to go back to the original name.

gdemin commented 5 years ago

Hi, Sorry, there are no logs at this moment. As far as I can see lista is data.frame and ks.test doesn't work with data.frames. Try the following:

lista =   xl.current.region('Sheet1!k2:k53', col.names = FALSE, row.names = FALSE)[[1]] # take only first column
res =   ks.test(as.numeric(lista),'pnorm',mean=mean(lista),sd=sd(lista))$p.value
xl['Sheet1!N2'] = NA
xl['Sheet1!N2'] = res

Additionally check what will be selected when you press Ctrl+Shift+* on sheet1!k2. All selected values need to be numeric.

rmlopes commented 5 years ago

Maybe I did not express myself correctly. The R code is fine, I can run it in R, and it runs in excel if I make a new sheet and use the original name "Sheet1" like the sample I provided. If I simply change the name of that same sheet to "Sheet2" (updating also the first line of the R code) than there is some error and the R code stops executing in the very first line.

This is a bit of an ackward error, and it is behavior that does not happen on the example file. Maybe there is some workbook setting/option that I am missing?

Just to be clear, If I change the name of the sheet back to "Sheet1" the code executes and gives the result.

gdemin commented 5 years ago

Yes, it is really strange. Did you try to run your code in the R itself (not via Excel) with lista = xl.current.region('Sheet2!k2:k53', col.names = FALSE, row.names = FALSE)? It should work the same way or rise an error with descriptive message.

rmlopes commented 5 years ago

In R it works fine, just tried it.

gdemin commented 5 years ago

Hmm... I will try tomorrow - by now I don't have Windows system near me. Did you save workbook after the renaming workbook? Perhaps, it helps.

rmlopes commented 5 years ago

Yep, also tried that after renaming the sheet, and close and reopen the workbook as well. Thanks for the help.

gdemin commented 5 years ago

One more check. Try in Excel:

tryCatch({
  lista =   xl.current.region('Sheet1!k2:k53', col.names = FALSE, row.names = FALSE)
  res =   ks.test(as.numeric(lista),'pnorm',mean=mean(lista),sd=sd(lista))$p.value
  xl['Sheet1!N2'] = NA
  xl['Sheet1!N2'] = res},
  error = function(e) e$message
  )

It should return error message.

rmlopes commented 5 years ago

Nothing happens when I run it.

gdemin commented 5 years ago

Sorry:

xl['Sheet1!N2'] = tryCatch({
  lista =   xl.current.region('Sheet1!k2:k53', col.names = FALSE, row.names = FALSE)
  res =   ks.test(as.numeric(lista),'pnorm',mean=mean(lista),sd=sd(lista))$p.value
  xl['Sheet1!N2'] = NA
  xl['Sheet1!N2'] = res},
  error = function(e) e$message
  )
rmlopes commented 5 years ago

It prints "Exception ocurred". In the meanwhile I was able to debug it through R, the data has different formats when picking up from the different sheets for some reason (the type is numeric in both, but the result is different for some reason). All working like a breeze now. Many thanks @gdemin.