Automatic File Naming

mezr

Active Member
Joined
Feb 2, 2003
Messages
301
Hello all,
I'm trying to rewrite some old VBA that had been used for automatically renaming a file. It no longer works since switching over to Office 2013. The existing code is as follows ...

______

Private Sub CommandButton3_Click()

ThisWorkbook.SaveAs Filename:=Left(ThisWorkbook.FullName, Len(ThisWorkbook.FullName) - 12) _
& Sheet3.Range("A19").Text & "_" & Sheet3.Range("E4").Text

End Sub

______

In the end, the file name should look something like this - "fox-l2-frm1-111914-tml-timesheet_10-31-2014_John Smith" with the "10-31-2014" being the variable residing in A19, and "John Smith" being the variable residing in E4.

Thank you in advance for your suggestions.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
What is your original file name? I mean the full path inluding name of the file. Do you get any error?
 
Upvote 0
Hi, and thank you for your help.

The path and original file name is "\\FOX_OFFICE_TRAN\Fox_Office_Tran\Fox L2 Share\L2 Payroll\fox-l2-frm1-111914-tml-timesheet_template".

The error I get is "Compile Error. Can't find project or library" and it highlights the word "LEFT" in the code.

It's been several years since I've done much with VBA and I'm feeling a bit lost.
 
Upvote 0
In the VBA Editor, go to Tools --> References. Ensure the following are selected :)
- Visual Basic for Applications
- Microsoft Excel xx.x Object Library
- OLE Automation
- Microsoft Office xx.x Object Library

(not sure what version it is for XL13, so replace the x.xx for whatever you find)

Hope that helps

/AJ
 
Upvote 0
Hi Adam. Each of those were already selected, and a couple others too.
 
Upvote 0
Might want to look at using late binding instead of early binding.
 
Upvote 0
Ah ha! I scrolled further down and saw a calendar control that was labeled "MISSING". Unchecked that and it appears to be working.

Thank you so much for guiding me through this! :)
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top