Save As file name-Macro

[dlove]

Ok,

I have a macro recorded that saves the workbook to my desktop and then saves an additional copy to a shared drive. Right now it wants to save my file as the same name every time and ask to replace the existing copy.

What I would like to tell it to do is make the save as name reference cell D10. Unfortunatley, I don't know how to tell it to do that in visual basic.

Any assistance?

 

[VoG]

Can you post the code that you have.

 

[dlove]

Here it is.

Thanks!



Sub SaveQuote()
'
' SaveQuote Macro
'
'
ChDir "C:\Documents and Settings\drew\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\drew\Desktop\Customer Quote Testing.xls", _
FileFormat:=xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ChDir "Z:\Quoting Inventory Base Data"
ActiveWorkbook.SaveAs Filename:= _
"Z:\Quoting Inventory Base Data\Customer Quote Testing.xls", FileFormat:= _
xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Range("Q36").Select
End Sub

 

[VoG]

Try this: change the sheet name to suit

Sub SaveQuote()
'
' SaveQuote Macro
'
'
ChDir "C:\Documents and Settings\drew\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\drew\Desktop\" & Sheets("Sheet1").Range("D10").Value & ".xls", _
FileFormat:=xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ChDir "Z:\Quoting Inventory Base Data"
ActiveWorkbook.SaveAs Filename:= _
"Z:\Quoting Inventory Base Data\" & Sheets("Sheet1").Range("D10").Value & ".xls", FileFormat:= _
xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Range("Q36").Select
End Sub

 

[dlove]

I appreciate the help with the code. If I send this code to somone else, will the macro recognize to try and save the file to their desktop and not mine? If not, then how would we write the code to save the copy to whatever desktop the macro is being run at?

Thanks,

 

[VoG]

You could try this

Sub SaveQuote()
'
' SaveQuote Macro
'
'
ChDir "C:\Documents and Settings\drew\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\" & Environ("username") & "\Desktop\" & Sheets("Sheet1").Range("D10").Value & ".xls", _
FileFormat:=xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ChDir "Z:\Quoting Inventory Base Data"
ActiveWorkbook.SaveAs Filename:= _
"Z:\Quoting Inventory Base Data\" & Sheets("Sheet1").Range("D10").Value & ".xls", FileFormat:= _
xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Range("Q36").Select
End Sub

 

[dlove]

It seems to be closer. The first line of the code is still giving the recipient problems.

ChDir "C:\Documents and Settings\drew\Desktop"

The above line of code gets an error. I'm guessing it is because my username is specified on that line and it gets confused when it runs the code from a different desktop. I appreciate your assistance on this issue! I will continue to try different things. Further help is appreciated.

Thanks,

 

[VoG]

Oops! I should have spotted that. Try deleting that line of code - I don't think it is necessary.

 

[Wayne01]

Hi Guys

I have used the macro to export a chart to an image file but have noticed that my larger charts seem to loose some of the chart image to the right hand side of the image. Does anyone know how to solve this issue? can we specify an image height and width?

Regards
Wayne

 

[Wayne01]

Oops, wrong thread.