Solved

Save a document with same filename but different extension in VBA, but the filename will change

Posted on 2014-09-19
9
732 Views
Last Modified: 2014-09-23
Title says it all,

I am writing VBA code, I want to save as in the same directory with different extension, but with the same file name,  which could be different every time.
0
Comment
Question by:tike55
  • 6
  • 3
9 Comments
 
LVL 39

Expert Comment

by:als315
ID: 40334080
You can get new file name with this function:
Public Function s_as(E As String) As String ' E - New extension without comma
s_as = ThisWorkbook.FullName
s_as = Left(s_as, InStrRev(s_as, ".")) & E
End Function

Open in new window

Usage:
ThisWorkbook.SaveAs Filename:=s_as("txt")
0
 

Author Comment

by:tike55
ID: 40336855
Could you further elaborate this example by using "test" as the file name, and xlsx as the extension, so I know I get it right.

Also,  if I wanted to add "_Monday" after "test" could you provide me an example.

thanks!
0
 

Author Comment

by:tike55
ID: 40337146
I am also confused as how to call this function in the macro
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 40337406
Your question was ambiguous: "I want to save as in the same directory with different extension, but with the same file name,  which could be different every time"
If you like to save your file always with extension "xlsx" and change file name every time, you should clarify rule for naming. If you like to add day name, basing on current date, you can use this sub (macro):
Public Sub s_as()
Dim s_as As String
s_as = ThisWorkbook.FullName
s_as = Left(s_as, InStrRev(s_as, ".") - 1) & "_" & Format(Date, "dddd") & ".xlsx"
ThisWorkbook.SaveAs Filename:=s_as
End Sub

Open in new window

You can add options to format function if you like to add month or year
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:tike55
ID: 40337569
I omited date portion for now.  just used:

Public Sub s_as()
Dim s_as As String
s_as = ThisWorkbook.FullName
s_as = Left(s_as, InStrRev(s_as, ".") - 1) & ".xlsx"
ThisWorkbook.SaveAs FileName:=s_as
End Sub


Now I am getting the error" Run-time error '1004'
THis extension can not be used with the selected file type.  Change the file extension in the File name text box or select a different file type by changing the Save as type.  

- when I hit debug, the following line is highlighted.
ThisWorkbook.SaveAs FileName:=s_as

Any ideas?
0
 

Author Comment

by:tike55
ID: 40337688
Also,
the original file is a csv, I don't know if that will effect anything.

THanks for your help btw
0
 

Author Comment

by:tike55
ID: 40338184
Hi,

Just wondering whether any headway has been made, or is the question over.
0
 

Author Closing Comment

by:tike55
ID: 40339425
Would have been nice to get a responce to my last comments.  Otherwise, I would have given 500 points.
0
 
LVL 39

Expert Comment

by:als315
ID: 40339946
Sorry, we can't answer immediately. We can do it when we have free time. You can see in debugger, what is in s_as variable. I have no problems with csv files and this code.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

26 Experts available now in Live!

Get 1:1 Help Now