Saving in wrong location

Hi

I have the following code which saves my worksheet with a name extracted from particular cells:

Public Sub SaveAsMaximoWO()
ThisFile = Range("AC5").Value
ThisFile2 = Range("E3").Value
saveName = ThisFile & " - " & ThisFile2
ActiveWorkbook.SaveAs Filename:=saveName

End Sub

This (for reasons I don't understand) saves the file to My Documents by default.

What I would like it to do is save to another folder within my documents as default. How can I do this?

Any help greatly appreciated

Many Thanks

Chris

The reason being is that you have not specificied the folder location thus it is storing it in your default location i.e My documents.

What you need to do is specify the full address like "c:/My Documents/Testing folder/" & Thisfile etc.

Try this

Sub SaveAsMaximoWO()
dim ThisFile as string
dim thisfile2 as string
Dim WorkPath as string

ThisFile = Range("AC5").Value '
ThisFile2 = Range("E3").Value ' not sure if it already has the .xls extension so will add it into the string
WorkPath = thisworkbook.path & "/Save Folder"

saveName = WorkPath & '/' & ThisFile " - " & ThisFile2 & ".xls"
ActiveWorkbook.SaveAs Filename:=saveName

End Sub

so in this case what should happen is that say the activeworkbook is in My documents then it would save it in My documents under the subfolder called "Save Folder". Just make sure that the subfolder exists else it will crash.

Thanks ever so much for your help.

I have implemented the code you suggest and it says there is a syntax error on the following line:

saveName = WorkPath & '/' & ThisFile & " - " & ThisFile2 & ".xls"

I am not sure what this means? I have checked that the workPath folder exists in my documents so not sure why it isn't working.

As an aside, is it always necessary to declare varialbes in macros as you have done or is it just good practice?

Chris

thanks for for replying on my behalf simon.

No problem im no expert so had to try the code see why it didnt work and process of elimination.........just beat you to the reply thats all.........i surprised myself that i got it!

Regards,
Simon

Hi again!

Really sorry to ask again, as I am sure I am missing something really stupid here (again). Here is the code:

Sub SaveAsMaximoWO()
Dim ThisFile As String
Dim Thisfile2 As String
Dim WorkPath As String
Dim saveName As String

ThisFile = Range("AC5").Value '
Thisfile2 = Range("E3").Value ' not sure if it already has the .xls extension so will add it into the string
WorkPath = ThisWorkbook.Path & "/Gas Sheets"

saveName = WorkPath & "/'" & ThisFile & " - " & Thisfile2 & ".xls"
ActiveWorkbook.SaveAs Filename:=saveName

End Sub

Yet when it runs it is coming up with and error and highlighting the last line when I go into debugging.

My uselessness in this is quite embarassing....

do you mind me asking what value is the thisfile and thisfile2? i know you cant use certain symbols when saving a workbook.

all i can assume is that you dont have a folder called Gas sheets in the same path as the excel workbook you opened because the code worked for me if i created the folder gas sheets

Regards,
Simon

Sorry FunkyMonk didn't mean to tread on your toes!

Simon

run this and see what it is returning sometimes you maybe returning a double / by mistake.

Sub SaveAsMaximoWO()
Dim ThisFile As String
Dim Thisfile2 As String
Dim WorkPath As String
Dim saveName As String

ThisFile = Range("AC5").Value '
Thisfile2 = Range("E3").Value ' not sure if it already has the .xls extension so will add it into the string
WorkPath = ThisWorkbook.Path & "/Gas Sheets"
msgbox workpath
saveName = WorkPath & "/'" & ThisFile & " - " & Thisfile2 & ".xls"
msgbox savename
ActiveWorkbook.SaveAs Filename:=saveName

End Sub

Originally Posted by Simon Lloyd

Sorry FunkyMonk didn't mean to tread on your toes!

Simon

its alright I like a bit of competion

Wahoo, right, it works. Thanks.

I think the technical term for me in this instance is "turdface".

Basically, Simon got my rather slow brain ticking. The folder I wanted to save to was not on the same path as the file I was saving from, so it was trying to save to some very odd place indeed!

Therefore I got rid of the thisActiveWorkbook.path and just put in the path (minus the drive name as other computers may have different drive names) and it works!

Again, I really appreciate the help. I'm a philosopy graduate, I should be nowhere near this! But becuase I don't sweat with fear when I switch my computer on (and my boss won't buy software) then he thinks it would be a good idea to make me into a programmer. Unfortuntely, it's you blokes who get the fall-out. Thanks again, I'll be back!

well I have no higher education, my boss/IT department wont upgrade our systems (windows NT/Office 97 with some machine including mine which have been upgraded to excel 2000 not even the complete office package).

VBA programmers can get paid loads to customerise Excel so maybe get your boss to send you on some courses and then leave him but I work in the public sector but looking for a better paid job. As the skills I have dont match my salary.

SO glad we could be of some assistance

Originally Posted by Simon Lloyd

Sorry FunkyMonk didn't mean to tread on your toes!

Simon

Seems like you not only tread on my toes you F*&king jumped on them on this occasion.

Me neither and i dont have the skills to match my job! (hehe) maybe if we slammed our heads together hard enough we could amount to something!

Simon

Errr........... i have a pair of steel toe capped boots if you like, size 9, they may afford you some protection from my clumsiness!

regards,
Simon

Originally Posted by Simon Lloyd

Me neither and i dont have the skills to match my job! (hehe) maybe if we slammed our heads together hard enough we could amount to something!

Simon

hehehehe - banging heads together sounds a bit naff.

I think me banging my head may have something to do with the fact that programming is way beyond me! So I will have to decline....

I came across this thread through a search. It has been helpful so far.

What if I don't want to specify the filename and leave it up to the user. I want to point it to a specific folder, but the user can name the file as they see fit.

thanks.

-joel

nevermind, I found the answer, but it wasn't clear to me until I started playing around with it.

I used:

Application.GetSaveAsFilename "enter/the/path/here"

Hope this helps someone.

-joel