VBA Code to save Files to a selected Folder

[Morshed_Dhaka]

Hello Everyone,

I am looking for a simple code.

What i want to do that i have 2 sheets in a excel file. Input sheet contains 100 people result data & Output sheet is Individual person result sheet. So the idea is when i run the macro, in the second sheet it put 1 people information details with details result, then convert it into PDF file & automatically save the PDF file to desktop.

The code working perfectly but what i want that, there will be Folder Selection dialog box at the begining for the process by which i will select the folder where all the PDF files will be stored after conversion. Below is my code :

Sub PrintStaffForm()
For r = 14 To 350
Sheets("OUTPUT").Range("AX2").Value = Cells(r, 1).Value
Sheets("OUTPUT").ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheets("OUTPUT").Range("AX5").Value & ".pdf"
On Error Resume Next
Next r
End Sub

Will be really helpful if somebody help me to build the code which i am looking for.

Thanks

 

[Morshed_Dhaka]

anyone help me pls....i need this simple code urgently...

 

[elynoy]

this will allow you to chose where to save the pdf file

Private Sub Save_As_PDF()
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler


Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Worksheets("SHEET1").Range("C17").Value _


'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"


'replace spaces and periods in sheet name
strName = Replace(wsA.Name, "", "")
strName = Replace(strName, "", "")


'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile


'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")


'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    'MsgBox "PDF file has been created: " _
      '& vbCrLf _
      '& myFile
End If


exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

Replace the SHEET for your needs

eLy

 

[Morshed_Dhaka]

Hi, i am not looking for this big code. My code is already working. I just needed additional code on top of that code. That additional code will allow me to choose a folder where the converted PDF file will be saved. The converted PODF file naming & others staff is alredy available into my code. I just need the extra one. Thanks

 

[elynoy]

yes, and it's in the middle of the one I gave. you just need to read it: after the " ' " it's written what the part of the code does.

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save"

and put this after the sub

Dim strPath As String
Dim myFile As Variant

eLy

 

[Morshed_Dhaka]

many many thanks. it works.

 

[elynoy]

No problem

eLy