Solved

Rename Excel File From Access Button

Posted on 2013-06-13
14
216 Views
Last Modified: 2013-06-13
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
Comment
Question by:shieldsco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 39244322
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
 

Author Comment

by:shieldsco
ID: 39244345
I need to run this from an access button so it will be necessary to point to a folder
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39244358
so replace all ActiveWorkbook.Path occurrences with the folder name.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 57
ID: 39244381
Use the name statement:

NAME <old path> As <New path>

 You can rename a disk file or directory this way.

Jim.
0
 

Author Comment

by:shieldsco
ID: 39244386
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39244390
whats the error?
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39244394
u need to change "new-file-name" to a valid file name
0
 

Author Comment

by:shieldsco
ID: 39244395
syntax error
MyFile = Dir$(\\cdc\project\OD_FMO_Share\AP Stat\Current Month CPB & "\*APStat*" & ".*")
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39244404
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
 

Author Comment

by:shieldsco
ID: 39244623
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
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
ID: 39244691
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
 

Author Comment

by:shieldsco
ID: 39245188
It renames the first file however it stays in the loop and locks the application
0
 

Author Closing Comment

by:shieldsco
ID: 39245330
The solution works only partially
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39245383
U should at least give me some time to check it before closing the question
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question