Need some help with a save location in my code.

[michael2420]

Hello,


I am a first time poster to this forum and have found everyone's advice to be invaluable. The code below is working for my purposes for the most part. I am looking to have my team be able to use the sheet it is for and would prefer it save to our shared drive rather than my documents. Unfortunately I am still a bit green when it comes to VBA as far as where to insert the code and how it should read.


If anyone could please help me out I would really appreciate it.


Thanks in advance.

Sub SaveSheet()
'
' SaveSheet Macro


Dim part1 As String
Dim part2 As String
Dim part3 As String
Dim part4 As String


part1 = Range("B2").Value
part2 = Range("D2").Value
part3 = Range("B3").Value
part4 = Range("D3").Value




ActiveWorkbook.SaveAs FileName:= _
"" & part1 & " " & part2 & " " & part3 & " " & part4 & " " & part5 & " .xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    
End Sub

 

[mrmmickle1]

Michael2420,

Welcome to the board!

Try it like this:

Excel 2010
	B	C	D
2	ns-linyvfs02\HR DataRequests\		Assistant\
3	Business Queries\		2016 Q2\MyFile.xlsm
Sheet1

ActiveWorkbook.SaveAs part1 & part2 & part3 & part4, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

You have not specified a value for part5 in your code...so you may want to check that.

In addition you may want to take this into account:

Does it involve a shared drive? If so this can be tricky seeing as different users will have their drives mapped to different letters.

For example at my company I have a drive called HR Data Requests Mapped to Z:\ while my co -worker maps it to X:\ .... The only way to get around this is to use an absolute path in the link.

So rather then save a file like this:

Z:\Assistant\Business Queries\2016 Q2\MyFile.xlsx

you would have to save the file like this using an absolute path:

ns-linyvfs02\HR DataRequests\Assistant\Business Queries\2016 Q2\MyFile.xlsx

 

[michael2420]

Thank you for your help Matt, I had actually removed the Part 5 because I didn't end up needing it, I just forgot to remove it when I typed posted, my apologies.
It is a shared drive I am trying to save to but it all maps to the same location thankfully. I just am unsure as to how to formulate the code I need.
I want it to save to S:\Root\Folder1\SubFolder1\Subfolder1A\File.xlsm and can't figure out how to insert the location string.

 

[BQardi]

I want it to save to S:\Root\Folder1\SubFolder1\Subfolder1A\File.xlsm and can't figure out how to insert the location string.

Just put it before the filename (is part1, part2, part3 and part4 all part of the filename?):

Sub SaveSheet()
'
' SaveSheet Macro

Dim wbPath As String
Dim part1 As String
Dim part2 As String
Dim part3 As String
Dim part4 As String

[COLOR=#ff0000]wbPath = "S:\Root\Folder1\SubFolder1\Subfolder1A\" [/COLOR][COLOR=#008000]'Or put it in a range like your 'parts':
'wbPath = Range("B4").Value[/COLOR]
part1 = Range("B2").Value
part2 = Range("D2").Value
part3 = Range("B3").Value
part4 = Range("D3").Value


ActiveWorkbook.SaveAs FileName:= _
[COLOR=#ff0000]wbPath & [/COLOR]part1 & " " & part2 & " " & part3 & " " & part4 & " .xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    
End Sub

 

[mrmmickle1]

Can you share what the values in these cells are in reference to the full file string?

part1 = Range("B2").Value
part2 = Range("D2").Value
part3 = Range("B3").Value
part4 = Range("D3").Value

 

[michael2420]

Thank you BQardi, that worked perfectly for me!

 

[michael2420]

Hi Matt,
The First part is Name, Second was Class Number, Third was Start Date and Fourth was End Date.
BQardi was able to help me with what the save string should look like. Thank you for your help as well!