search

mcgovernc7 / R-Studio-saveworkbook-function-causes-excel-file-to-become-corrupted-and-unusable

R Studio saveworkbook function causes excel file to become corrupted and unusable
0 stars 0 forks source link

R studio saveworkbook function causes excel file to become corrupted #1

Open mcgovernc7 opened 10 months ago

mcgovernc7 commented 10 months ago

I am using the openxlsx package in R studio to load an excel file into R and then overwrite a particular tab with new data. R then saves the updated file so it can be used later. This is supposed to automate a workflow.

I successfully ran the code in a dummy excel file ( a blank file with the correct tab names for R to delete, create new, and then overwrite).

When I went to use the code on the actual excel it was intended for, it ran successfully but when I went to open the file, it said it was corrupted. Excel tried to repair the file but it couldn't and now the file no longer opens.

I have tried tips I found online such as removing special characters from any of the tab names in the actual excel file, removing conditional formatting from all sheets, removing grouping from rows and columns.....but nothing has worked.

Can anyone help me with this issue?

The code is 99% complete but this last 1% is preventing me from automating a workflow.

I even tried relocating the file to a new file path that doesn't use special characters but that didn't work either.

Here is code:

FD_CVM<-FD127%>% filter(Group=="CVM") WB <- loadWorkbook("C:/Users/OneDrive - Company Name/Shared Documents - Company Name/Finance (Confidential)/FP&A/Decision Support/Shared Folder/Revenue/202311/ZBA model 2023.11 CVM.xlsx")

 removeWorksheet(WB, "CVM")
 CVM= addWorksheet(WB, "CVM")
 writeData(WB, CVM, FD_CVM)
saveWorkbook(WB,"C:/Users/OneDrive - Company Name/Shared Documents - Company Name/Finance (Confidential)/FP&A/Decision Support/Shared Folder/Revenue/202311/ZBA model 2023.11 CVM.xlsx", overwrite = TRUE)
mcgovernc7 commented 10 months ago

I am not sure why but when I submitted the issue, it split the R code I typed into two pieces. The first piece is in the response section, and the second piece is in the separate section of the submittal reserved for quick copy/pasting