Just to add to Rowan's reply...
You can get the location of the desktop folder with something like:
Option Explicit
Sub WithPath2()
Dim flName As String
Dim flFormat As Long
Dim WSHShell As Object
Dim DesktopPath As String
Set WSHShell = CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")
Set WSHShell = Nothing
'MsgBox DesktopPath
flFormat = ActiveWorkbook.FileFormat
flName = DesktopPath & "\Report_" & Worksheets("sheet1").Range("B14").Value
ThisWorkbook.SaveAs Filename:=flName, FileFormat:=flFormat
End Sub
Rowan wrote:
The code below (posted again to fix linewrapping) will allow you to navigate
to the desktop.
Sub SaveReport()
Dim flToSave As Variant
Dim flName As String
Dim flFormat As Long
flFormat = ActiveWorkbook.FileFormat
flName = "Report_" & Cells(4, 2).Value
flToSave = Application.GetSaveAsFilename _
(flName, filefilter:="Excel Files (*.xls), *.xls", _
Title:="Save FileAs...")
If flToSave = False Then
Exit Sub
Else
ThisWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat
End If
End Sub
Otherwise do something like this
Sub WithPath()
Dim flName As String
Dim flFormat As Long
flFormat = ActiveWorkbook.FileFormat
flName = _
"C:\Documents and Settings\myusername\Desktop\Report_" & Cells(4, 2).Value
ThisWorkbook.SaveAs Filename:=flName, FileFormat:=flFormat
End Sub
Change the flName string to the path of your desktop.
Regards
Rowan
"mrbalaje" wrote:
ok Rowan,
I want to save the excel in desktop.
In my excel in cell B14, i have my job number. I want to save the excel as
Report_O9863952.xls
Where O9863952 is the job number which is in cell b14. Job number will
change for every report. can you please give me the code.
"Rowan" wrote:
You didn't say what you wanted to do about chosing a folder to save your
report in but this macro will open the save as dialog specifying your report
name. You can then chose the directory and change the name - if required
before saving.
Sub SaveReport()
Dim flToSave As Variant
Dim flName As String
Dim flFormat As Long
flFormat = ActiveWorkbook.FileFormat
flName = "Report_" & Cells(4, 2).Value
flToSave = Application.GetSaveAsFilename _
(flName, filefilter:="Excel Files (*.xls), *.xls", Title:="Save File
As...")
If flToSave = False Then
Exit Sub
Else
ThisWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat
End If
End Sub
HTH
Rowan
"mrbalaje" wrote:
I will get my job number in the cell B4 of every job that I doing. I need to
save the excel like " Report_O0917656", where O0917656 is the job number that
was in cell B4.
Can I able to record a macro, in which the macro can save my excel in the
required format.
Job number will change for every reports.
--
Dave Peterson
|