export to xls vba

[excel_2009]

Hi excel gurus,

I have the following code which works perfectly:

Sub export_lionbridge()


Dim New_file_name


New_file_name = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls")
Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs FileName:=New_file_name, FileFormat:=xlNormal
ActiveWindow.Close


 Application.Calculation = xlCalculationAutomatic
     Application.ScreenUpdating = True
 
End Sub

The code copies a worksheet and allows a user to save it as an XLS file, all I need help on is to disable the "Save As" user prompt, I want the file to be saved automatically to the desktop i.e C\Users\Desktop and the name of the file to be referenced from Sheet2 cell A1.

Is this possible, if so how?

Thank you!

 

[starl]

Sub export_lionbridge()  
Dim New_file_name
Dim Path As String
Path = CreateObject("Wscript.Shell").specialfolders("Desktop") & "\"
New_file_name = Path & Sheet2.Range("A1").value
Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs FileName:=New_file_name, FileFormat:=xlNormal
ActiveWindow.Close  
 Application.Calculation = xlCalculationAutomatic    
 Application.ScreenUpdating = True 
 End Sub

 

[excel_2009]

Perfect, thank you Starl!

I do have one other question though, I have modified the code slightly to include this:

New_file_name = Path & Sheets("Home").Range("F6").Value & "_export"

But sometimes the value F6 has spaces between the words, would it be possible to replace the space characters with underscores?

Thank you!

 

[Norie]

Try this.

Sub export_lionbridge()
Dim strNew_file_name As String
Dim strPath As String

    strPath = CreateObject("Wscript.Shell").specialfolders("Desktop") & "\"
    strNew_file_name = Replace(Sheets("Home").Range("F6").Value & "_export", " ", "_")
    
    Sheets("Sheet1").Copy
    
    With ActiveWorkbook
        .SaveAs Filename:=strPath & strNew_file_name, FileFormat:=xlNormal
        .Close
    End With
        
End Sub

 

[excel_2009]

Wow perfect! Thank you Norie!