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 Mike Lewis 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... 1

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.
 
We convert DATETIME() to a character and add that to the file name so that has some kind of meaning.
 

Part and Inventory Search

Sponsor

Back
Top