using cell values as folder path

carlandtina02 · Apr 2nd 2016

Hi Guys,

I'm trying to alter the below code so that I can save the activesheet in specific folders depending on what is in b12 and b13, but with no avail.
The save as pdf part works but i cant save it to specific folders that relate to the cell data.

if someone could help me, that would be great :smile:

Thanks in advance

Code

Sub SaveActiveSheet()


    Const strPath As String = "C:\Documents and Settings\MyName\My Documents\Reports\& CellName1\& CellName2"
    Dim strFile As String
    Dim CellName1
    Dim CellName2
    
    CellName1 = Range("b12").Value
    CellName2 = Range("b13").Value




    With Sheet2
    
        'check for Account Details'
        If .Range("B16").Value = "" Or .Range("H10").Value = "" Then
            MsgBox "Please enter Account Details. ", _
                    vbExclamation, "Missing Account"
        Else
            strFile = .Range("B16").Value & " " & .Range("H10").Value
            .Copy    'Copy sheet2 to a new workbook




            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
                    strPath & strFile & ".pdf", Quality:= _
                    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                    OpenAfterPublish:=False




        End If
    End With
End Sub

Display More

**pike** · Apr 2nd 2016

Re: using cell values as folder path

Hi carlandtina02
Welcome to the forum
The const and file name and path is unclear
try

Code

Option Explicit
Sub SaveActiveSheet()
     
    Const strPath As String = "C:\Documents and Settings\MyName\My Documents\Reports\"
    Dim strFile As String
     
    strFile = Range("b12").Value & Range("b13").Value
    
     
    With Sheet2
         
         'check for Account Details'
        If .Range("B16").Value = "" Or .Range("H10").Value = "" Then
            MsgBox "Please enter Account Details. ", _
            vbExclamation, "Missing Account"
        Else
            strFile = .Range("B16").Value & " " & .Range("H10").Value
            .Copy 'Copy sheet2 to a new workbook
             
             
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            strPath & strFile & ".pdf", Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
             
             
        End If
    End With
End Sub

Display More

carlandtina02 · Apr 2nd 2016

Re: using cell values as folder path

Hi Pike,

Thanks for the welcome and the code!

I tried the altered code you done, but its not saving in the extended folder path, it saves in "Reports" but not in the folders within it?

Regards
Tina :smile:

Code

Option ExplicitSub NewSaveActiveSheet()
     
    Const strPath As String = "C:\Documents and Settings\Carl.Walker\My Documents\Reports\"
    Dim strFile As String
     
    strFile = Range("b12").Value & Range("d12").Value
     
     
    With Sheet2
         
         'check for Account Details'
        If .Range("B16").Value = "" Or .Range("H10").Value = "" Then
            MsgBox "Please enter Account Details. ", _
            vbExclamation, "Missing Account"
        Else
            strFile = .Range("B16").Value & " " & .Range("H10").Value
            .Copy 'Copy sheet2 to a new workbook
             
             
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            strPath & strFile & ".pdf", Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
             
             
        End If
    End With
End Sub

Display More

**pike** · Apr 2nd 2016

Re: using cell values as folder path

since you have provided no information to help a guess

Code

strFile = Range("b12").Value &"\"& Range("d12").Value

**pike** · Apr 2nd 2016

Re: using cell values as folder path

or

Code

strFile ="\" & Range("b12").Value &"\"& Range("d12").Value

**pike** · Apr 2nd 2016

Re: using cell values as folder path

no It will be

Code

strFile = Range("b12").Value &"\"& Range("d12").Value

re-read you first post
and code compliments

Code

Const strPath As String = "C:\Documents and Settings\MyName\My Documents\Reports\"

carlandtina02 · Apr 2nd 2016

Re: using cell values as folder path

Hi Pike,

Sorry,I've attached a sample of what I'm using at present.
It still saves in the "Reports" folder only and not in its sub folders unfortunately?

Thanks
Tina

**pike** · Apr 2nd 2016

Re: using cell values as folder path

Hi
Is there folder already created as per B12 cell?
what is the name of the file that's saved to the reports folder?

**pike** · Apr 2nd 2016

Re: using cell values as folder path

is Carl.Walker in the folder path?

Code

Option Explicit
Sub NewSaveActiveSheet()
 
Const strPath As String = "C:\Documents and Settings\Carl Walker\My Documents\Reports\"
Dim strFile As String, strFolder As String


strFolder = "\" & Range("b12").Value
strFile = "\" & Range("d12").Value


  
With Sheet2
     
     'check for Account Details'
    If .Range("B12").Value = "" Or .Range("d12").Value = "" Then
        MsgBox "Please enter Account Details. ", _
        vbExclamation, "Missing Account"
     Else
     '   strFile = .Range("B16").Value & " " & .Range("H10").Value
     '   .Copy 'Copy sheet2 to a new workbook
          If Dir(strPath & strFolder, vbDirectory) = "" Then
        MkDir strPath & strFolder
    End If
 
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strPath & strFolder & strFile & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
         
         
    End If
End With
End Sub

Display More

carlandtina02 · Apr 2nd 2016

Re: using cell values as folder path

Hi Pike,

Yes, the full path with the sample given would be "C:\Documents and Settings\carl.walker\My Documents\Reports\a\b"
The file name saves as "c def".

Hope this helps

**pike** · Apr 2nd 2016

Re: using cell values as folder path

try this with a check if the folder exists

Code

Option Explicit
Sub NewSaveActiveSheet()
    Const strPath As String = "C:\Documents and Settings\Carl.Walker\My Documents\Reports\"
    Dim strFile As String, strFolder As String


    strFolder = Range("b12").Value
    strFile = "\" & Range("d12").Value


    With Sheet2
        'check for Account Details'
        If .Range("b12").Value = "" Or .Range("d12").Value = "" Then
            MsgBox "Please enter Account Details. ", _
                   vbExclamation, "Missing Account"
        Else
            If Dir(strPath & strFolder, vbDirectory) = "" Then
                MkDir strPath & strFolder
            End If
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                            strPath & strFolder & strFile & ".pdf", Quality:= _
                                            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                                            OpenAfterPublish:=False
        End If
    End With
End Sub

Display More

carlandtina02 · Apr 2nd 2016

Re: using cell values as folder path

Hi Pike,

Its getting there :smile: but now it:

Saves in folder "a" (instead of "b").
Saves as filename "b"?

**pike** · Apr 2nd 2016

Re: using cell values as folder path

I just swapped the values in the ranges around as it was only a guess

try..

Code

Option Explicit 
Sub NewSaveActiveSheet() 
    Const strPath As String = "C:\Documents and Settings\Carl.Walker\My Documents\Reports\" 
    Dim strFile As String, strFolder As String 
     
    strFolder = Range("d12").Value 
    strFile = "\" & Range("b12").Value 
     
    With Sheet2 
         'check for Account Details'
        If .Range("b12").Value = "" Or .Range("d12").Value = "" Then 
            MsgBox "Please enter Account Details. ", _ 
            vbExclamation, "Missing Account" 
        Else 
            If Dir(strPath & strFolder, vbDirectory) = "" Then 
                MkDir strPath & strFolder 
                doevents  
           End If 
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ 
            strPath & strFolder & strFile & ".pdf", Quality:= _ 
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ 
            OpenAfterPublish:=False 
        End If 
    End With 
End Sub

Display More

carlandtina02 · Apr 2nd 2016

Re: using cell values as folder path

Hi Pike,

Sorry, I feel i'm having your life here

The code is actually creating a folder instead of using the folders that are currently there, and is not naming the file as b16 and h10 value?

Code

Option Explicit
Sub NewSaveActiveSheet()
    Const strPath As String = "C:\Documents and Settings\Carl.Walker\My Documents\Reports\"
    Dim strFile As String, strFolder As String
     
    strFolder = Range("d12").Value
    strFile = "\" & Range("b12").Value
     
    With Sheet2
         'check for Account Details'
        If .Range("b16").Value = "" Or .Range("h10").Value = "" Then
            MsgBox "Please enter Account Details. ", _
            vbExclamation, "Missing Account"
        Else
            If Dir(strPath & strFolder, vbDirectory) = "" Then
                MkDir strPath & strFolder
                DoEvents
            End If
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            strPath & strFolder & strFile & ".pdf", Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        End If
    End With
End Sub

Display More

**pike** · Apr 2nd 2016

Re: using cell values as folder path

if the folder exist it would create a new one
the folder is the range value
"C:\Documents and Settings\Carl.Walker\My Documents\Reports" & Range("d12").Value
the name of the pdf is Range("b12").Value
you will have to explain what cell does what
maybe

Code

Option Explicit
Sub NewSaveActiveSheet()
    Const strPath As String = "C:\Documents and Settings\Carl.Walker\My Documents\Reports\"
    Dim strFile As String, strFolder As String


    strFolder = Range("b12").Value & "\" & Range("d12").Value
    strFile = "\" & Range("b16").Value & Range("h10").Value


    With Sheet2
        'check for Account Details'
        If .Range("b12").Value = "" Or .Range("d12").Value = "" Then
            MsgBox "Please enter Account Details. ", _
                   vbExclamation, "Missing Account"
        Else


            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                            strPath & strFolder & strFile & ".pdf", Quality:= _
                                            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                                            OpenAfterPublish:=False
        End If
    End With
End Sub

Display More

carlandtina02 · Apr 2nd 2016

Re: using cell values as folder path

Hi Pike,

You've cracked it, this works perfectly now, thanks very much for persevering and all your help! :thanx:

Just a bit of clarity for anyone else wanting to use this:

Code

[COLOR=#333333]strFolder = Range("b12").Value & "\" & Range("d12").Value 'This chooses the folder path from the cell value
[/COLOR][COLOR=#333333]    strFile = "\" & Range("b16").Value & Range("h10").Value 'This names the file as the cell value [/COLOR]

**pike** · Apr 2nd 2016

Re: using cell values as folder path

your welcome thanks for posting

using cell values as folder path

Participate now!

Share