Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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