Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Button fails to call macro when open an Excel via Intranet | Excel Discussion (Misc queries) | |||
How do I find a Macro in Excel when I don't know its name? | Excel Discussion (Misc queries) | |||
How to Create an Excel Macro to send a meeting request. | Excel Discussion (Misc queries) | |||
Macro in Excel 2002 to save a workbook to a FTP location | Excel Discussion (Misc queries) | |||
How can I get a macro in Excel to set the Zoom to "Selection"? | Excel Discussion (Misc queries) |