MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to save file in different location


Posted by Sam on May 07, 2001 11:09 AM

I know this is easy for a lot of you guys but I need a
Macro that will save the opened file to a different
location and replace the file or the cell contents
of one cell in the file in it's current location. I need
to be able to do this with out getting the pop up
windows that ask if I want to replace the file that
already exist.

Thanks, Sam


Posted by Barrie Davidson on May 07, 2001 1:56 PM

Hi Sam, could you be more specific? I don't quite understand what you mean by "or the cell contents of one cell in the file in it's current location".

Barrie

Posted by Sam on May 07, 2001 3:25 PM

Hi Barrie, What I'm trying to do is save the opened file to a different location than from where I opened it from. I have a macro that installs an assending number on the spreadsheet when the file opens. I also use another macro to save the file to a different location including the new number as part of the
file name. What I want to do is some how copy this new number to the file in the first location where the orginal file is stored so the next time the file is opened again the number will be one higher than the previous opening. Hope this makes sense.
Thanks, Sam

Posted by Barrie Davidson on May 07, 2001 5:11 PM

Hi again Sam. If I understand what you are currently doing.......
You save a file that you already have open (say Book 1) using a numbering system that attaches a number to the SaveAs file name. You want to be able to paste that number to a cell within a different file (say Book 2, sheet 1, cell A1). Is this correct? Is "Book 2" open when you are running your SaveAs macro? Does your SaveAs macro get its number from "Book 2"

Barrie

Posted by Sam on May 08, 2001 5:21 AM

Hi Barrie, Thanks for all your trouble so far. Actually book 1 is the book that I'm saving to a different location. I would also like to keep book 1 in it's orginal location but with the new number pasted in so that the next time it is opened it will be one number higher than the same book saved to the different location. I would also like to keep book 1 empty of all data in it's orginal location, except for the new number. Is there any way that I can paste a number into that file without getting a pop up window asking me if I want to replace the current file. Maybe I can explain this a little better. Let's say I have a file named work orders in C:\my files. I open this file. when I do I have a macro that increases a number in one cell of the workbook by one
when the workbook opens. When I'm finished entering data to this workbook, I have another macro attached to a button on the form that includes that number as the last part of the file name and saves the file to a different location, let's say C:\archives. What I want to accomplish is, the new number needs to be transfered back to the workbook in it's orginal location so that the next time the workbook is opened the number will be one higher than the number on the workbook that was saved to the different
location. This will prevent overwritting the workbook in the other location when the workbook is saved again.

Thanks, Sam

Posted by Barrie Davidson on May 08, 2001 8:38 AM

Okay Sam, I've written some code that I think will work for you. Some assumptions I made:
1. The location of the incremental number in your file (C:\my files\work orders.xls) is in cell A1 in Sheet1.
2. You have declared a variable, named File_Number, that captures the new incremental number.
Have a try at this and let me know if it works for you.
Sub Assign_Number()

Dim File_name As String

'This assumes that your incremental number is in cell A1, Sheet1
'and that the number you use is a variable called File_Number

File_name = ActiveWorkbook.FullName

' Your code that saves the file using the numbering system
' is inserted here

Application.DisplayAlerts = False
Workbooks.Open (File_name)
Sheets("Sheet1").Select
Range("A1").Value = File_Number
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub


Regards,
Barrie

Posted by Sam on May 08, 2001 2:51 PM

File_name = ActiveWorkbook.FullName

Thanks Barrie, I wasn't able to use all the code you wrote. I was able to use a few lines of it added to mine to get the end result I wanted.

Thanks again, Sam