Saving Excel using Macro

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Dave Peterson]

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