PDA

View Full Version : Save As Location



Djblois
02-12-2008, 09:12 AM
I am creating a way for my users to send a 2007 file format to people and it will send it as a 2003 format. I am doing this because some people here are not that good with computers so I am making it as easy as possible. Here is the code I am using:

Set wbToEmail = ActiveWorkbook
If frmEmailWork.cbSendInOldFormat Then
wbToEmail.SaveAs wbToEmail.Name & ".xls", FileFormat:=xlNormal
Else
wbToEmail.SaveAs wbToEmail.Name
End If

The only trouble I am having is I can't figure out how to set the saveAs location and how do I remove the .xlsx from the file name?

Bob Phillips
02-12-2008, 11:50 AM
Set wbToEmail = ActiveWorkbook
If frmEmailWork.cbSendInOldFormat Then
wbToEmail.SaveAs "C:\path\subpath\" & wbToEmail.Name & ".xls", FileFormat:=xlExcel8
Else
wbToEmail.Save
End If

Djblois
02-12-2008, 12:07 PM
Thanks Xld,

That works perfectly but is there a way to remove .xlsx or .xlsm before it saves right now I am getting a file name like this "SalesReport.xlsx.xls"

Daniel

Bob Phillips
02-12-2008, 12:16 PM
Really? So it doesn't work perfectly :whistle:

How about this



Set wbToEmail = ActiveWorkbook
If frmEmailWork.cbSendInOldFormat Then
Filename = wbToEmail.Name
Pos = InStrRev(Filename, ".")
If Pos > 0 Then
Filename = Left$(Filename, Pos - 1)
End If
wbToEmail.SaveAs "C:\path\subpath\" & Filename & ".xls", FileFormat:=xlExcel8
Else
wbToEmail.Save
End If