Saving Excel using Macro

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.

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.

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.

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.

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