VBA to save a file in a location as determined by cell A2

[fishep6]

Hi

I have looked all over and found various threads with similar issues but I cant quite get it to work.

So far my VBA will save a file with a file name as determined by cell "B2" and it saves to the file path "Z:\Documents\Folder 1\Sub folder 2"

However what I want is the file path to not be fixed but to be determined by a Vlookup value that results in Cell "A1".

So in cell "A1" (based on a Vlookup formula that is in it) would return one of the 4 file paths, and which ever filepath it returns I want it to save into the right folder. The file paths will be as below.

"Z:\Documents\Folder 1\Sub folder 1"
"Z:\Documents\Folder 1\Sub folder 2"
"Z:\Documents\Folder 1\Sub folder 3"
"Z:\Documents\Folder 1\Sub folder 4"

and therefore the VBA I want would read the file path in cell A1 and save accordingly. My code so far is

Private Sub CommandButton1_Click()
Dim path As String
Dim filename1 As String
 
path = "Z:\Documents\Folder 1\Sub folder 2\"
filename1 = Range("B2").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
 
End Sub

One further issue is that the file path is on a network drive, so presumably some people would have it mapped differently. Could someone explain how I write the file path as a network address (I remember reading a post that gets the file path as a series of numbers but I cant seem to find that post any more.

Many thanks

 

[fishep6]

Ive managed to do this now, however still need help with making it work regardless of how the network drive is mapped. How do I get the core network drive filepath so it works regardless of how the network drive is mapped?

I just need to work out how to get the file path address to put into my Vlookup table and not into the VBA now, the Vlookup result will post in cell A1

Private Sub CommandButton1_Click()
    Dim SaveName As String
       SaveName = ActiveSheet.Range("b2").Text
       ActiveWorkbook.SaveAs Filename:=ActiveSheet.Range("A1") & _
           SaveName & ".xls"
           
           
           
End Sub

 

[Fluff]

Have a look here https://www.mrexcel.com/forum/excel-questions/502617-getting-workbooks-path-network-vba.html

 

[fishep6]

managed to fin the bit I was looking for by searching the mapping of the network drive "do'h" thank you anyway

 

[dandy]

I have a similar problem but am trying to take it a little further I think.

I have a folder on my desktop with a bunch of subfolders by peoples names (i.e. Subfolder1 = DOE, JOHN; Subfolder2 = SMITH, JOHN, etc.). When I change cell "B2" in the spreadsheet and run the Macro, I would like it to save that sheet as a .pdf to the applicable person's folder that is listed in "B2". Then, when I change names and run it again, it will save it to the next persons folder. But I do not want to save the excel file itself as anything different.

Can anyone help with this?

 

[dandy]

This is what I currently have.

Right now, the PDF is only saving to the path: K:\10 Quality Assurance\04 Inspection Stamp Log and Authority\03 Weld & Braze Certification Logs

But I need it to save to the folder that is defined in cell B2 aka ActiveSheet.Range("B2").Value

Option Explicit

Sub GeneratePDF()
Dim Path As String
Dim Filename As String

Call SetPrintArea
Path  = "K:\10 Quality Assurance\04 Inspection Stamp Log and Authority\03  Weld & Braze Certification Logs" & "\" &  ActiveSheet.Range("B2").Value
'Call MakeDirectory(Path)
Filename = ActiveSheet.Range("B3").Value & "-" & ActiveSheet.Range("D3").Value
Application.DisplayAlerts = False
ActiveSheet.ExportAsFixedFormat  Type:=xlTypePDF, Filename:=Path & " " &  ActiveSheet.Range("B3").Value & "-" &  ActiveSheet.Range("D3").Value & "-" &  ActiveSheet.Range("F3").Value & ".pdf"
Application.DisplayAlerts = True

End Sub

Sub SetPrintArea()
    Range("A2:G14").Select
End Sub

Function FolderExists(FolderPath As String) As Boolean
On Error Resume Next

ChDir FolderPath
If Err Then FolderExists = False Else FolderExists = True

End Function