Excel Macro Save As Link

Where do you want that link copied to (i.e., what is the other workbook's name, where in that workbook do you want the link)?

Barrie

The template name is Sales Package and each time the user saves the file as a Save As the filename would change (Each Time a New Customer is added to the Sales Package template) the filename that the link would be attached to the Snow Contracts. When the user enters the save as name the same name would be set up in the snow contract file and if the user clicks on that name it would open the saved as file.

Thanks for responding.

Hi, sorry for not responding earlier but Monday was a holiday here and today has been really busy. Have a try with this and see if it works for you.

Sub Temporary_Name()
'Written by Barrie Davidson
Dim FileSaveName As String
Dim Counter As Integer
Dim OpenWorkBooks_Count As Integer
Dim ActiveFile As String

'Identify the Active file's window
ActiveFile = ActiveWorkbook.Name
'Set the counter
Counter = 1
'Count the number of open workbooks
OpenWorkBooks_Count = Application.Windows.Count
'Check to see if "Snow Contracts" is already open
'Loop through all open workbooks
Do Until Counter > OpenWorkBooks_Count
'If the workbook is hidden do nothing, otherwise check the name
If Windows(Counter).Visible = True Then
Windows(Counter).Activate
If ActiveWorkbook.Name = "Snow Contracts.xls" Then
'If "Snow Contracts" is open bypass the command to open it
GoTo ByPass
End If
Else
End If
Counter = Counter + 1
Loop
'If "Snow Contracts" is not open, open it
Workbooks.Open FileName:="C:\Snow Contracts.xls"
ByPass:
'Go to your original file
Workbooks(ActiveFile).Activate
'Get the new file name
FileSaveName = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls),*.xls")
'Save the file
ActiveWorkbook.SaveAs (FileSaveName)
'Close the file
ActiveWorkbook.Close
'Activate the "Snow Contracts" file
Workbooks("Snow Contracts.xls").Activate
'Go to the first empty cell in column A
Range("A1").End(xlDown).Offset(1, 0).Select
'Input the file name in the selected cell
Selection.Value = FileSaveName
'Add the hyperlink
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=FileSaveName
'Close the "Snow Contracts" file
ActiveWorkbook.Close

End Sub

You'll have to change the line:

Workbooks.Open FileName:="C:\Snow Contracts.xls"

to ensure you have the correct path.

Regards,
Barrie

The filename is being saved but it does not direct it to Snow Contracts. When I go to The Snow Contracts workbook the file savedas name is not there anywhere in the cells.

Help!

Barry:
I tried using the macro provided to me. But, the saved filename is not showing up in the Snow Contracts workbook. and although it indicates A1 for the cell to start saving to, the cursor actually ends up in G34 (have no idea why). But no names are writing to this file.

Two questions for you:
1. Is the "Snow Contracts" file actually named Snow Contracts.xls?
2. Did you change the line in the macro to the proper path and file name?

When I tested this it worked fine for me (a mystery to solve, I love it!).

Barrie

Barrie:
yes, the file is actually called Snow Contracts.xls. I did change the line to the proper path and file name.
I even took it one step farther. I copied Snow Contracts to another filename snowcontracts2.xls on the c:/drive. But it still doesn't work. It goes to Snow contracts after you enter the saved file name. the cell G34 is selected and not the A1 cell. I checked the macro and it definately says A1 Cell. This is definately weird! But we are almost there (I think!)

Again, I just tested the macro and it worked fine. Here's how I tested it:

I made a file called "Snow Contracts.xls" and another file called "Test.xls". I put the macro in "Test.xls". I then opened a new file, put some sample data in this file, and then ran the macro in "Test.xls". As I indicated, this works fine for me. So the next question I ask myself - where are you putting the macro ("Test.xls" in my test case) and what file are you running it on (the new file in my test case)?

Am I making sense??

Barrie

Barrie:
I tried follwoing your steps. Somehow something is not quite correct. Okay, how are you running your macro in test.xls??
I have a file that has a new button under sales package.xls. When the user selects the button it generates the save as dialog box and the user saves the new file name. I assign my macro to that button. When the file is saved it should generate a hyperlink filename with the same filename that was saved. The hyperlink name would show up in the Snow Contracts File. When the user opens up Snow Contracts and selects a filename it will open the Sales Package filename.
The macro is in Sales Package the hyperlink name should go to Snow Contracts.
I am also getting compile errors:Syntax Errors on the following:
1. If Windows (Counter).Visible=True Then
2. If ActiveWorkbook.Name="Snow Contracts.xls" Then
3. Else
Thanks for your continued help :

Okay, now it's making sense. Try replacing your button macro with this (do it on a copy of your template first!) macro and it should work fine for you:

Sub Saving_Template()
'Written by Barrie Davidson
Dim FileSaveName As String
Dim Counter As Integer
Dim OpenWorkBooks_Count As Integer
Dim ActiveFile As String
Dim TemplateFile As String

'Identify the Template file's window
TemplateFile = ActiveWorkbook.Name
'Create a copy of the active sheet (so macros aren't saved)
ActiveSheet.Copy
'Identify the Active file's window
ActiveFile = ActiveWorkbook.Name
'Set the counter
Counter = 1
'Count the number of open workbooks
OpenWorkBooks_Count = Application.Windows.Count
'Check to see if "Snow Contracts" is already open
'Loop through all open workbooks
Do Until Counter > OpenWorkBooks_Count
'If the workbook is hidden do nothing, otherwise check the name
If Windows(Counter).Visible = True Then
Windows(Counter).Activate
If ActiveWorkbook.Name = "Snow Contracts.xls" Then
'If "Snow Contracts" is open bypass the command to open it
GoTo ByPass
End If
Else
End If
Counter = Counter + 1
Loop
'If "Snow Contracts" is not open, open it
Workbooks.Open FileName:="C:\Snow Contracts.xls"
ByPass:
'Go to your original file
Workbooks(ActiveFile).Activate
'Get the new file name
Saving_File:
FileSaveName = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls),*.xls")
'Make sure you aren't over-writing your template
If FileSaveName = "C:\Template.xls" Then
MsgBox ("You can't over-write the template")
GoTo Saving_File
End If
'Save the file
ActiveWorkbook.SaveAs (FileSaveName)
'Close the file
ActiveWorkbook.Close
'Activate the "Snow Contracts" file
Workbooks("Snow Contracts.xls").Activate
'Go to the first empty cell in column A
Range("A1").End(xlDown).Offset(1, 0).Select
'Input the file name in the selected cell
Selection.Value = FileSaveName
'Add the hyperlink
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=FileSaveName
'Close the "Snow Contracts" file
ActiveWorkbook.Close
'Go to the template file
Workbooks(TemplateFile).Activate
'Close the template file
ActiveWorkbook.Close

End Sub

And, of course, change the following lines to the correct path/filename:

Workbooks.Open FileName:="C:\Snow Contracts.xls"

If FileSaveName = "C:\Template.xls" Then

Regards,
Barrie