Rename files with variable names
 

I would like to find code that would take a variable out of filenames in a
specific location.

For example, here are my file names:
"G:\A F S\Monthly Reports\HiPath\CS\MonthlyC_AgtSum_060206_142704.dl t"
"G:\A F S\Monthly Reports\HiPath\CS\MonthlyC_GrpCall_060206_142715.d lt"
"G:\A F S\Monthly Reports\HiPath\CS\MonthlyC_GrpSum_060206_142520.dl t"
"G:\A F S\Monthly Reports\HiPath\CS\MonthlyC_GrpTime_060206_142722.d lt"

I need to remove the uderscore and last 6 numbers from the file name to read:
"G:\A F S\Monthly Reports\HiPath\CS\MonthlyC_AgtSum_060206_142704.dl t"
becomes
"G:\A F S\Monthly Reports\HiPath\CS\MonthlyC_AgtSum_060206.dlt"

I need to do this because I can't loop through other code to open and modify
the files because the file names are variable.

I've tried the following code:
Do
If FOLDERLOOP = 1 Then FILENAMEFOLDER = "CS\MONTHLYC_AGTSUM_"
If FOLDERLOOP = 2 Then FILENAMEFOLDER = "CS\MONTHLYC_GRPCALL_"
If FOLDERLOOP = 3 Then FILENAMEFOLDER = "CS\MONTHLYC_GRPSUM_"
If FOLDERLOOP = 4 Then FILENAMEFOLDER = "CS\MONTHLYC_GRPTIME_"

If FOLDERLOOP = 5 Then Exit Do

OLDFILENAME = "G:\A F S\MONTHLY REPORTS\HIPATH\" & FILENAMEFOLDER &
RPTRUNDATE & "_*.dlt"
NEWFILENAME = "G:\A F S\MONTHLY REPORTS\HIPATH\" & FILENAMEFOLDER &
RPTRUNDATE & ".dlt"

SHELLCOMMAND = "Command.com /c ren " & OLDFILENAME & " " & NEWFILENAME

Application.Shell (SHELLCOMMAND)

FOLDERLOOP = FOLDERLOOP + 1

Loop Until FOLDERLOOP = 5

I've tried to also use:
Application.Shell (Command.com /c ren " & OLDFILENAME & " " & NEWFILENAME)

So I've come to the conclusion that I need to get rid of the variable in the
file name so I can use true file names.

How can I do this?

THX!

Rename files with variable names
 

Look in the VBA help for the Name function

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Robert_L_Ross" wrote in message ...
I would like to find code that would take a variable out of filenames in a
specific location.

For example, here are my file names:
"G:\A F S\Monthly Reports\HiPath\CS\MonthlyC_AgtSum_060206_142704.dl t"
"G:\A F S\Monthly Reports\HiPath\CS\MonthlyC_GrpCall_060206_142715.d lt"
"G:\A F S\Monthly Reports\HiPath\CS\MonthlyC_GrpSum_060206_142520.dl t"
"G:\A F S\Monthly Reports\HiPath\CS\MonthlyC_GrpTime_060206_142722.d lt"

I need to remove the uderscore and last 6 numbers from the file name to read:
"G:\A F S\Monthly Reports\HiPath\CS\MonthlyC_AgtSum_060206_142704.dl t"
becomes
"G:\A F S\Monthly Reports\HiPath\CS\MonthlyC_AgtSum_060206.dlt"

I need to do this because I can't loop through other code to open and modify
the files because the file names are variable.

I've tried the following code:
Do
If FOLDERLOOP = 1 Then FILENAMEFOLDER = "CS\MONTHLYC_AGTSUM_"
If FOLDERLOOP = 2 Then FILENAMEFOLDER = "CS\MONTHLYC_GRPCALL_"
If FOLDERLOOP = 3 Then FILENAMEFOLDER = "CS\MONTHLYC_GRPSUM_"
If FOLDERLOOP = 4 Then FILENAMEFOLDER = "CS\MONTHLYC_GRPTIME_"

If FOLDERLOOP = 5 Then Exit Do

OLDFILENAME = "G:\A F S\MONTHLY REPORTS\HIPATH\" & FILENAMEFOLDER &
RPTRUNDATE & "_*.dlt"
NEWFILENAME = "G:\A F S\MONTHLY REPORTS\HIPATH\" & FILENAMEFOLDER &
RPTRUNDATE & ".dlt"

SHELLCOMMAND = "Command.com /c ren " & OLDFILENAME & " " & NEWFILENAME

Application.Shell (SHELLCOMMAND)

FOLDERLOOP = FOLDERLOOP + 1

Loop Until FOLDERLOOP = 5

I've tried to also use:
Application.Shell (Command.com /c ren " & OLDFILENAME & " " & NEWFILENAME)

So I've come to the conclusion that I need to get rid of the variable in the
file name so I can use true file names.

How can I do this?

THX!