Saving file in current folder

[rossross]

Hi, I've got a total of 3 files I'm working and I want to save one of them in the current folder.

For starters, I am brand new to writing VBA so I'm sure this is all wrong.

My process starts with copying a master file called "template" into a customer's folder, the customer's excel file will already be in it, and then I'll open these two, do my thing then I need to save it a new workbook in this folder. I've got a command to open a new workbook, copy a tab into it and then SaveAs the name in this current folder I'm using.

Sub NewPage()

Dim newwb As Workbook
Dim newpage As String
Dim relativepath As String
Set newwb = Workbooks.Add
newpage = "Example Name"
relativepath = ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:=newpage

Workbooks("Template").Sheets("Example").Copy _
    Before:=newwb.Sheets(1)
'this creates the new workbook and says where to place it

Application.DisplayAlerts = False
Worksheets("Sheet1").Delete
Application.DisplayAlerts = True

End Sub

I had done some Googling trying to find this answer and saw someone mention the activeworkbook.path method above but it just saves into "My Documents" on my desktop.

I will be on my shared server for our department, attempting to save into a specific customer's folder.

To clarify, I will command to open new workbook, copy data into this new workbook then SaveAs whatever name in the current folder I'm using where these other two files I'm using are. And it's different every time so I can't use the same path. Customer 1 can start with an A and have their own folder and Customer 2 might start with F and have their own folder. With sub folders as well.


Any help on this?

 

[Micron]

I will be on my shared server for our department, attempting to save into a specific customer's folder.

Not really sure of what you're after, but based on that statement perhaps you should look into the msoFileDialogFolderPicker. It will return the path of the folder that the user selects, then you could use that in code. It will also allow you to start the search in a particular folder location if need be.

 

[rossross]

Not really sure of what you're after, but based on that statement perhaps you should look into the msoFileDialogFolderPicker. It will return the path of the folder that the user selects, then you could use that in code. It will also allow you to start the search in a particular folder location if need be.

is that a program? never heard of that.


Basically I just want to save the workbook in the folder that i'm in. Open new workbook, saveas, saved in this folder. Whatever folder I happen to be in.

 

[Micron]

If you Google excel vba save workbook in current folder
(or use whatever browser you're accustomed to) you'll find there's at least a hundred links where people asked for exactly that or similar. Most, if not all, have code solutions, a few which are in this forum. Sorry but I don't have any code for this. This forum also has a "Similar Threads" listing at the bottom of every thread. You could also look there to see if anything looks promising.

 

[rossross]

If you Google excel vba save workbook in current folder
(or use whatever browser you're accustomed to) you'll find there's at least a hundred links where people asked for exactly that or similar. Most, if not all, have code solutions, a few which are in this forum. Sorry but I don't have any code for this. This forum also has a "Similar Threads" listing at the bottom of every thread. You could also look there to see if anything looks promising.

i did exactly that and got nowhere

 

[Micron]

It's hard to tell from your posts which parts you have manually done before the code will run and which parts you want the code to do. It seems that you will have already manually opened a template workbook plus a workbook with data, then will manually copy and paste data into the template. Then you want to SaveAs the template. If that is correct, why not just use the SaveAs dialog that you get by using File > SaveAs in the template workbook?

 

[rossross]

It's hard to tell from your posts which parts you have manually done before the code will run and which parts you want the code to do. It seems that you will have already manually opened a template workbook plus a workbook with data, then will manually copy and paste data into the template. Then you want to SaveAs the template. If that is correct, why not just use the SaveAs dialog that you get by using File > SaveAs in the template workbook?

Template is manually opened and this commands a new workbook to open, i'm copying from the template workbook into this new one. then it does the SaveAs the new name. right now it goes to "my documents." I just need this new workbook to save in the folder where my Template workbook is being opened from. the folder i will be working in while using these.

 

[Micron]

So I think your problem would be that you're using the relative path of the activeworkbook which is likely the new wb. In testing, that value is "\book1". My suggestion would be to add
Debug.Print ActiveWorkbook.Name
Debug.Print relativepath
Debug.Print ThisWorkbook.Path
before this next line
Workbooks("Template").Sheets("Example").Copy _
Before:=newwb.Sheets(1)

then step through the code or put a breakpoint on the workbooks line and just run it. Then look at the output in the immediate window to see if it shows you in the starting path that you want. You'll also see what the other 2 values are.

That assumes the code is running from your template wb. My guess is that by using relative path of the new wb it's going to default to your user location instead of where you're working in.