Saving new file in working directory

[dl7631]

Hi!

I created a macro. At the end of this macro I want to save my active workbook as a new file. Here is what I wrote:

ActiveWorkbook.SaveAs Filename:= _
"TV&Radio_" & Sheets("Template").Range("B7") & ".xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False

Problem: It saves the file under "MyDocuments". However, I want it to be saved in the same directory that I am working in.

Advice?

Thanks a lot!

 

[TiaXL]

Hi!

I created a macro. At the end of this macro I want to save my active workbook as a new file. Here is what I wrote:

ActiveWorkbook.SaveAs Filename:= _
"TV&Radio_" & Sheets("Template").Range("B7") & ".xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False

Problem: It saves the file under "MyDocuments". However, I want it to be saved in the same directory that I am working in.

Advice?

Thanks a lot!

activeworkbook.saveas ("S:\Your Folder\TV&Radio_" & sheets("Template").range("B7") & ".XLSX")

 

[dl7631]

activeworkbook.saveas ("S:\Your Folder\TV&Radio_" & sheets("Template").range("B7") & ".XLSX")

Sorry, I am afraid it won't work for me. I was not clear: am not creating this macro for myself. It will be for a bunch of other people. This macro should be saving the file in the working directory of the person using it - whatever this folder might be.
Makes sense?
Thank you again!
Dimitri

 

[TiaXL]

Sorry, I am afraid it won't work for me. I was not clear: am not creating this macro for myself. It will be for a bunch of other people. This macro should be saving the file in the working directory of the person using it - whatever this folder might be.
Makes sense?
Thank you again!
Dimitri

are the folders being worked out of denoted anyway? like username for example.

 

[dl7631]

No. The file will be sent to different users. No information is available as to where their folders will be - some people might have it on their desktops, others - in their "MyDocuments", others - god knows where.

 

[TiaXL]

No. The file will be sent to different users. No information is available as to where their folders will be - some people might have it on their desktops, others - in their "MyDocuments", others - god knows where.

if its being sent to them then wont it be cached in a temp file unless they manually save it somewhere themselves.

 

[dl7631]

They open the file, save it in the folder where they want to work (like anyone would do it) and then work with it. They open it, do something, hit the macro button and then the macro creates a new file that it has to save. The code I sent saves it under "My Documents". I would like it to be saved in the directory the person has the file with the macro open.

 

[TiaXL]

try at the start of your code

dim sPath as string

sPath = activeworkbook.path

and then when you save your file

activeworkbook.saveas(sPath & "TV&RADIOblabla.xls")

 

[dl7631]

It's not really working.
I added to lines at the top of my code:

Dim sPath As String
sPath = ActiveWorkbook.Path

And my "save" code looked like this:

ActiveWorkbook.SaveAs (sPath & "TV&Radio_" & Sheets("Template").Range("B7") & ".xlsx")

2 problems with it:
1. It includes the name of the folder into the name of the file, which I'd like to avoid.
2. It saves it one level above the working directory (not sure why).

I would hope there is a straightfoward way of saying: save this file right here.
No?

 

[TiaXL]

It's not really working.
I added to lines at the top of my code:

Dim sPath As String
sPath = ActiveWorkbook.Path

And my "save" code looked like this:

ActiveWorkbook.SaveAs (sPath & "TV&Radio_" & Sheets("Template").Range("B7") & ".xlsx")

2 problems with it:
1. It includes the name of the folder into the name of the file, which I'd like to avoid.
2. It saves it one level above the working directory (not sure why).

I would hope there is a straightfoward way of saying: save this file right here.
No?

no its not straight forward you want excel to somehow know where the person is saving a file which can only be done with the filepath property

you need a \ before your filename

if you print the path to a cell you can see that it doesnt put he last \ on

activework.saveas(spath & "\filename.xls")