Hi guys,
As I am just started with R I hope that you can support me in this case. I want to name an excel file with the previous month and year in compared with current system.
For example: the current month is Feb 2020
The file should be name as Jan_2020_List
Thanks a lot in advance!
Hi @Doris, does this help?
library(lubridate)
#>
#> Attaching package: 'lubridate'
#> The following object is masked from 'package:base':
#>
#> date
today <- Sys.Date()
today
#> [1] "2020-03-04"
a_month_ago <- today - months(1)
a_month_ago
#> [1] "2020-02-04"
last_month <- month(a_month_ago)
last_month
#> [1] 2
month.abb[last_month]
#> [1] "Feb"
Created on 2020-03-04 by the reprex package (v0.3.0)
Construct the name using paste0() and the lubridate helpers
library(lubridate)
library(stringr)
paste0(str_to_sentence(month(floor_date(Sys.Date(), unit = "month") - 1, label = TRUE)), "_", year(Sys.Date()), "_List.xlsx" )
#> [1] "Feb_2020_List.xlsx"
Created on 2020-03-05 by the reprex package (v0.3.0.9001)
Hi andresrcs,
Thank your for your support. Actually when i copy and run there is an error
Error: :15:185: unexpected symbol
14: #Set the name of the file
15: name<-pastepaste0(str_to_sentence(month(floor_date(Sys.Date(), unit = "month") - 1, label = TRUE)), "_", year(Sys.Date()), "paste0(str_to_sentence(month(floor_date(Sys.Date(), unit = "month
This is the full code of mine
#Input required libraries
library(XLConnect)
library(XLConnectJars)
library(lubridate)
library(stringr)
#Read the data from the flow
knime.out <- knime.in
#Open the library for formating
*options(java.parameters = "-Xmx28672m") *
require(XLConnect)
#Set the name of the file
name<-pastepaste0(str_to_sentence(month(floor_date(Sys.Date(), unit = "month") - 1, label = TRUE)), "", year(Sys.Date()), "paste0(str_to_sentence(month(floor_date(Sys.Date(), unit = "month") - 1, label = TRUE)), "", year(Sys.Date()), "_List.xlsx" )
#Prepare the template. Save it in the wanted location and close. Set the path to the template below:
template<-"C:\Users\uic61877\Desktop\EXECUTIVE LIST\Executives_Gradings_Database\KNIME workflow\intermediate_steps\Step 3\Step3_database.xlsx"
#Set the path to save the local copy of the file (on the computer)
new.folder<-"C:\Users\uic61877\Desktop\EXECUTIVE LIST\Executives_Gradings_Database\monthly lists"
#Copy the template to the new location
file.copy(template, new.folder)
#Set the working directory to the location
setwd(new.folder)
#Rename the template with the name of the current file. Check if the name of the template in "" is correct
file.rename("Step3_database.xlsx",name)
#Open the xls
wb <- loadWorkbook(name, create=FALSE)
setStyleAction(wb,XLC$"STYLE_ACTION.NONE")
#Write data from the flow
writeWorksheet(wb,knime.out, sheet = "Executives who left", startRow=4,startCol=1,header=FALSE)
#Save the data
saveWorkbook(wb)
There is one more thing i forget to mention in the question that in case the current month is Jan_2020 than the file should be named as Dec_20219_List but when i read your code it does the minus for month only.
Hi Dromano,
Thank you for your support!!
Actually i try to automatically named the file, not to pull out the month (as i don´t have any idea how to name it 
)
So is there anyway that
if the system is 01.02.2020 the excel file will be named as Jan_2020_List
if the system is 01.01.2020 the excel file will be named as Dec_2019_List
It would be super helpful for me as i have to named it manually each and every month now.
This function doesn't exist, it seems like you have pasted the code twice, check again.
Try to understand what the code is doing, getting the year to be set as you want is a trivial modification.
Andresrcs, I already checked it again than it work. Also I tried and sucessed with the year basing on your code.
Thank you so much again 
This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.