SaveAs using VB
 

Hi guy's

Can anyone help me put the following into the correct syntax so it will
work using 2003 please..

Function NewName() As String
Dim strFName As String
strFName = ActiveWorkbook.FullName
GetFullFile = Replace(strFName, ".xls", "a")
End Function

The reason I am wanting to use the above is to strip away the extension
because when the below code runs it saves it like this:

TMS_2012_10_19.xlsa.xml

When in fact I need it to be:

TMS_2012_10_19a.xml

My issue is I don't know where to insert it, or at least call the
function in order to set mySwb as the newly renamed active ( NewName )
workbook.

Sub Create_XML()

Dim myExtStr As String
Dim mySwb As Workbook
Dim myFileDir As String
Dim myFilePath As String
Dim myFileName As String

Set mySwb = ActiveWorkbook

myFileName = mySwb.Name & "a"
myFilePath = "T:\myFolder\"
myExtStr = ".xml": FileFormatNum = 46


With mySwb
.SaveAs myFilePath & myFileName & myExtStr
End With

End Sub

TIA
Mick.

Hi Mick,

You first need to change the Function with this code.

I guess you can replace the following line in the Create_XML procedure.

myFileName = mySwb.Name & "a"
with
myFileName = Replace(ActiveWorkbook.FullName, ".xls", "a")

You also don't need the function as the above code should work for you.

I haven't tested this code but am hoping it works for you.

Let me know if you get any issues.

Prashant

SaveAs using VB
 

Living the Dream wrote:

Can anyone help me put the following into the correct syntax so it will
work using 2003 please..

Function NewName() As String
Dim strFName As String
strFName = ActiveWorkbook.FullName
GetFullFile = Replace(strFName, ".xls", "a")
End Function

The reason I am wanting to use the above is to strip away the extension
because when the below code runs it saves it like this:

TMS_2012_10_19.xlsa.xml

When in fact I need it to be:

TMS_2012_10_19a.xml

My issue is I don't know where to insert it, or at least call the
function in order to set mySwb as the newly renamed active ( NewName )
workbook.

Add a dot at the end:
GetFullFile = Replace(strFName, ".xls", "a.")

--
Whenever I think about the past, it just brings back so many memories.
-- Steven Wright

SaveAs using VB
 

Hi Auric

Apologies for not replying sooner.

I managed to get a work around so it's all good

Thanks again.

Mick.