If an Excel file already exists in the folder...

[TheLazyPig]

Hi!

I'm trying to COPY TO aglib_aug24 TYPE XL5 file in a folder but the file name is already exists. Is there a way I can rename the file I'm about to save without changing the exitsing one.

lcFile --> W:\Upload2\FINANCE\AGLIB\24AUG\AGLIB.DBF

W:\Upload2\FINANCE\AGLIB\ --> path
24AUG --> folder name
AGLIB.DBF --> file name

SET DELETED ON
SET SAFETY OFF
SET UDFPARMS TO REFERENCE

CLOSE ALL
CLEAR

LOCAL lcTest,lcFile,lcRes,lcDir
*----------------------------------
lcTest   = "C:\BACKUP_FILES\"
lcDir    = "O:\Programs\OR\"
lcFile   = thisform.txtFileName.Value
caDir    = "W:\Upload2\FINANCE\AGLIB\"
lcRes    = "C:\BACKUP_FILES\AGLIB_EXTRACT\"
lcYY     = LEFT(LEFT(GETWORDNUM(JUSTPATH(lcFile),6,"\"),5),2)
lcMon    = RIGHT(LEFT(GETWORDNUM(JUSTPATH(lcFile),6,"\"),5),3)
*----------------------------------

IF  ATC("AGLIB.DBF",lcFile) = 0
    MESSAGEBOX("Invalid File Name!",48)
    thisform.txtFileName.Value = ""
    thisform.btnExtract.enabled = .F.
ELSE
   
    USE (lcFile) IN 0 SHARED NOUPDATE
   
    WAIT "Extracting Aglib...." WINDOW NOWAIT
               
    SELECT * FROM AGLIB WHERE rec_status <> 'D' INTO CURSOR aglibext
       
    IF MESSAGEBOX("Extract Agent Library?",36,"Confirmation") = 6
       SELECT aglibtext
       COPY TO caDir+"\aglib_"+lcMon+lcYY TYPE XL5
       MESSAGEBOX("File [aglib_extract.xls] Created!",64)
    ELSE
       MESSAGEBOX('Extraction Cancelled')
       RETURN 0
    ENDIF  
   
    thisform.txtFileName.Value = ""
    thisform.btnExtract.enabled = .F.

ENDIF

Thank you for the replies!

 

[Chris Miller]

First observation:

IF  ATC("AGLIB.DBF",lcFile) = 0

better be done as

IF  NOT UPPER(JUSTFNAME(lcFile)) == "AGLIB.DBF"

On your problem, though:
First check, whether the file caDir+"\aglib_"+lcMon+lcYY+".xls" already exists, if so, choose another name or rename the existing file.
A simple way to produce file names that do not exist is using SYS(2015), so you could simlply use that as a suffix. Best have all of it in a lcXLSFile variable, first:

lcXLSFile = caDir+"\aglib_"+lcMon+lcYY+SYS(2015)
COPY TO (lcXLSFile) TYPE XL5

Then pass on lcXLSFile to further code working on the XLS file, like excel automation code.

 

[igorsandler]

We convert DATETIME() to a character and add that to the file name so that has some kind of meaning.