Using MSOfiledialogfolderpicker value to create filepath and file name???

[fantabulator]

I use the following code in my introductory worksheet to ask the user for a directory to save reports:

Sub filepath()
Dim intChoice As Integer
Dim strPath As String

Application.FileDialog(msoFileDialogFolderPicker).AllowMultiSelect = False
intChoice = Application.FileDialog(msoFileDialogFolderPicker).Show
If intChoice <> 0 Then
      strPath = Application.FileDialog( _
        msoFileDialogFolderPicker).SelectedItems(1)
        Cells(27, 7) = strPath
End If
End Sub

On another worksheet the user can save a PDF report that includes information from the worksheet to form the file and directory name. In the code below which is on my report sheet, currently f27 is where I have stored the path name from the above code(introductory sheet) so I can add it to the directory name. Is there a way to insert the folderpicker data gained from the intro sheet into the code below on my report sheet? My method seems clunky and isn't as reliable on a network as the drives change. I need the folderpicker value and then add j3, j28 and k28 to form the file name but cannot figure out the syntax to make it work.

 fName = .Range("f27").Value & .Range("j3").Value & .Range("j28").Value & .Range("k28").Value
   
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:="s:\" & fName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Thank you,

 

[NARAYANK991]

Hi ,

This is something you can yourself troubleshoot.

1. Manually write down the actual full path + filename , which will allow you to access the desired file on the network.

2. Write down the concatenated values of what might be available in the cells F27 , J3 , J28 and K28

3. See if the two are identical ; if not , change your statement so that the two will become identical.

4. You are giving a static assignment of S:\ for the network drive , but you also say that this can change ; what are the conditions under which it will change , and what are the possible variations ?

If you can post the values in the 4 cells , others can help.

Narayan

 

[fantabulator]

Thanks, I have been playing with it and here is still my problem:

I do identify the "s:" in the previous code but I would like to omit that and use the file folder information gathered in the first sheet plus (j3-dynamicstudentname) plus (j28 - Report) plus (K28 - dynamic count of reports printed)

I currently store the file path in f27 and have concatenated it to add a "\", so I can get something like "s:reports\" I have tried the following but keep getting an error:

    fName = .Range("f27").Value & .Range("j3").Value & .Range("j28").Value & .Range("k28").Value
   
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

I guess my question is how do I modify the above code so that the file saved is filefolderpathchosenbyuser\j3&j28&k28?

Thanks

 

[NARAYANK991]

Hi ,

Can you post exactly what you get before you execute the statement :

.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Place the cursor on this line and press the F9 key , to insert a breakpoint at this line.

Now run the macro ; when execution comes to this line , it will halt.

In the Immediate window , type in :

?fname

and press the ENTER key ; the value of this variable will be printed out ; copy it and post it here.

Narayan

 

[fantabulator]

I actually figured it out thanks to your Immediate window check idea. Didn't know you could do that. Found the problem and fixed it....thank you very much.