• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

Rename Excel File From Access Button

I need to rename a Excel file from a button in Access 2010. The Excel file name will always contain APStat and will always be in the same folder. Any thoughts -- Thanks
0
shieldsco
Asked:
shieldsco
  • 7
  • 6
1 Solution
 
Meir RivkinFull stack Software EngineerCommented:
change "new-file-name" to the new file name.
ActiveWorkbook.Path returns the path of current excel file.

MyFile = Dir$(ActiveWorkbook.Path & "\*APStat*" & ".*")
Do While MyFile <> ""
	currentFile = Name ActiveWorkbook.Path & "\" & MyFile 
	newFile = Name ActiveWorkbook.Path & "\" & "new-file-name"
	Name currentFile As newFile
Loop

Open in new window

0
 
shieldscoAuthor Commented:
I need to run this from an access button so it will be necessary to point to a folder
0
 
Meir RivkinFull stack Software EngineerCommented:
so replace all ActiveWorkbook.Path occurrences with the folder name.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Use the name statement:

NAME <old path> As <New path>

 You can rename a disk file or directory this way.

Jim.
0
 
shieldscoAuthor Commented:
I get a complie error

MyFile = Dir$(\\cdc\project\OD_FMO_Share\AP Stat\Current Month CPB & "\*APStat*" & ".*")
Do While MyFile <> ""
    currentFile = Name \\cdc\project\OD_FMO_Share\AP Stat\Current Month CPB & "\" & MyFile
    newFile = Name \\cdc\project\OD_FMO_Share\AP Stat\Current Month CPB & "\" & "new-file-name"
    Name currentFile As newFile
Loop
0
 
Meir RivkinFull stack Software EngineerCommented:
whats the error?
0
 
Meir RivkinFull stack Software EngineerCommented:
u need to change "new-file-name" to a valid file name
0
 
shieldscoAuthor Commented:
syntax error
MyFile = Dir$(\\cdc\project\OD_FMO_Share\AP Stat\Current Month CPB & "\*APStat*" & ".*")
0
 
Meir RivkinFull stack Software EngineerCommented:
change it to:
folder = "\\cdc\project\OD_FMO_Share\AP Stat\Current Month CPB\"
MyFile = Dir$(folder & "*APStat*" & ".*")
Do While MyFile <> ""
    currentFile = folder & MyFile
    newFile = folder & "new-file-name"
    Name currentFile As newFile
Loop

Open in new window

0
 
shieldscoAuthor Commented:
It's not renaming the file

Private Sub Command0_Click()
'Rename CPB Excel Spreadsheet
folder = "(\\cdc\project\OD_FMO_Share\AP Stat\Current Month CPB\"
MyFile = Dir$(folder & "*APStat*" & ".*")
Do While MyFile <> ""
    currentFile = folder & MyFile
    newFile = folder & "APStat_CPB_Final.xls"
    Name currentFile As newFile
Loop




'Rename TIIPB Excel Spreadsheet
folder = "(\\cdc\project\OD_FMO_Share\AP Stat\Current Month CPB\"
MyFile = Dir$(folder & "*APStat*" & ".*")
Do While MyFile <> ""
    currentFile = folder & MyFile
    newFile = folder & "APStat_TIIPB_Final.xl"
    Name currentFile As newFile
Loop


End Sub
0
 
Meir RivkinFull stack Software EngineerCommented:
try this:

Private Sub Command0_Click()
'Rename CPB Excel Spreadsheet
folder = "\\cdc\project\OD_FMO_Share\AP Stat\Current Month CPB\"
MyFile = Dir$(folder & "*APStat*" & ".*")
Do While MyFile <> ""
    currentFile = folder & MyFile
    newFile = folder & "APStat_CPB_Final.xls"
    Name currentFile As newFile
Loop



'Rename TIIPB Excel Spreadsheet
folder = "\\cdc\project\OD_FMO_Share\AP Stat\Current Month CPB\"
MyFile = Dir$(folder & "*APStat*" & ".*")
Do While MyFile <> ""
    currentFile = folder & MyFile
    newFile = folder & "APStat_TIIPB_Final.xl"
    Name currentFile As newFile
Loop


End Sub

Open in new window

0
 
shieldscoAuthor Commented:
It renames the first file however it stays in the loop and locks the application
0
 
shieldscoAuthor Commented:
The solution works only partially
0
 
Meir RivkinFull stack Software EngineerCommented:
U should at least give me some time to check it before closing the question
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now