Solved

Rename Excel File From Access Button

Posted on 2013-06-13
14
210 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
  • 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now