Save As Dialog Box

[vicky666]

I want to create a Save As dialog box that appears when i click on my label, i have this code below so far but i don't know what to dim my dialog box as, can anyone help as I am new to this?

Dim wkbDataSave As Workbook
Dim strResponse As String

Set wkbDataSave = Application.Workbooks("TeachingPlan.xls&quot

CommonDialog1.Filter = "All Files|*.*"
CommonDialog1.Show.Open
strResponse = CommonDialog1.Filename

wkbDataSave.SaveAs strResponse

 

[RobBroekhuis]

For save as, try using the Application.GetSaveAsFilename method. You don't need to dim anything. Try:

strResponse=application.GetSaveAsFilename("TeachingPlan.xls", "Microsoft Excel Files (*.xls), *.xls",1, "Save", "Save&quot
wbk.saveas strResponse



Rob

 

[vicky666]

It brings up the save as box but then when you enter the file name and select the location you want and click ok, there is an error in the line wkbSaveAs strResponce and suggestions?

 

[RobBroekhuis]

Try again, hit "debug" when you get the error, and get the value of strResponse in the immediate window. Your post above refers to strResponce with a "c" - is your actual code consistent in terms of the variable name?
The code itself should work fine (I use it all the time) - what error do you get?
Rob

 

[vicky666]

The code is consistent and the value of strResponse is the filepath that i specified but the error still appears in that line

wbk.SaveAs strResponse

also it doesnt save the file to the location specified

 

[deshorta]

Hiya Vicky,

You trying to save it in excel? It's possible to get a user to input a saveas name and save it as that, if you are wanting to save the file in the same directory, that is.

 

[RobBroekhuis]

I assume you changed the
wbk.SaveAs to wkbDataSave.SaveAs
to be consistent with your variable name. If so, this really should work. What is the error you are getting?
Rob

 

[vicky666]

My code is below and on the line wbkSaveData.SaveAs strResponse is where the error occurs and it says "object required"

Sub SheetSave()

Dim wbkDataSave As Workbook
Set wbkDataSave = Application.Workbooks("TeachingPlan.xls&quot

strResponse = Application.GetSaveAsFilename("TeachingPlan.xls", "Microsoft Excel Files (*.xls), *.xls", 1, "Save", "Save&quot
wbkSaveData.SaveAs strResponse

 

[RobBroekhuis]

You dim and set the variable wbkDataSave, but you try to save the variable wbkSaveData. Since the latter is undefined, Excel does not recognize it as an object, hence the error.
Rob

 

[vicky666]

ooops! Thanks for all the help, as you can probably tell ive been working on this for too long and keep making silly mistakes lol, im going for a break