Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

TheLazyPig

Programmer
Sep 26, 2019
95
PH
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

Code:
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! 😊
 
First observation:

Code:
IF  ATC("AGLIB.DBF",lcFile) = 0
better be done as
Code:
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:

Code:
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.
 

Part and Inventory Search

Sponsor

Back
Top