Solved

Rename Excel File From Access Button

Posted on 2013-06-13
14
212 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

910 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

22 Experts available now in Live!

Get 1:1 Help Now