Using to VB to Save As Cell Name. And then Save As PDF

[davion]

Im a little stuck

I have a VB set up that "Save As" the SHEET, with name based on the contents of a particular cell. - which works perfectly.

I then have another VB setup to save new file as a PDF.

The issue I'm coming across is that it is saving the PDF as the original excel
workbook's name, not the new "Save As" name

example

Sample.xls has contents in Cell A1, when Macro activated it saves the active sheet as a new file with the contents of A1 as its name. Lets say Bob.xls

Sample.XLS also has another Macro, which when activated, saves as a PDF.
This macro is carried over to the newly created bob.xls

Issue is, it is saving bob.xls's PDF, as sample.pdf.

Anyway I can get it to save as bob.pdf without having to create a new macro every time?

 

[pbornemeier]

I understand that you want a macro that will save the active sheet as a .pdf file with the name of the file to be based on the value in range A1 of that sheet.
You must ensure that A1 hold a valid filename (see http://support.microsoft.com/kb/177506)

Sub SaveWorksheetAsPDF()
    'Save the active sheet as a .PDF with the filename in cell A1
    'A1 must hold a valid filename (see http://support.microsoft.com/kb/177506)
    'File will be saved in the same directory as this file
    
    Dim sFileName As String
    
    With ActiveSheet
        sFileName = .range("A1").Value 'assumes a valid filename exists in A1
        .Copy
        .ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=ThisWorkbook.path & "\" & sFileName & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=False, _
            IgnorePrintAreas:=False, OpenAfterPublish:=False
        ActiveWindow.Close False
    End With

End Sub

 

[davion]

I think you might have misunderstood question.
I have the VB set up to save it as a PDF based on cell contents already, and have it set up to save to another folder. This all works well

I have a VB setup to save the current sheet, in a new excel workbook, with the data in cell as its name.

What I am hoping to achieve, is this-

File A (lets call it bob.xls) has three sheets, with each sheet containing a cell that will be used as new file name for that sheet.
So cell a1 in each sheet, will become the name of that particular sheet, in a new workbook. Lets call this sally.xls

Second VB is set up in same fashion (within bob.xls as well), i.e saving each sheet as a new PDf, with cell A1's content as its name. lets call it sally.pdf

Now these work all well and good, IF you have the two macros running from the same sheet (bob.xls)

BUT what I am hoping to achieve, is that macro run from bob.xls creates sally.xls, and then sally.xls creates sally.pdf, WITHOUT having to program VB code into sally.xls as well.

Does this make sense?

I understand that you want a macro that will save the active sheet as a .pdf file with the name of the file to be based on the value in range A1 of that sheet.
You must ensure that A1 hold a valid filename (see http://support.microsoft.com/kb/177506)

Sub SaveWorksheetAsPDF()
    'Save the active sheet as a .PDF with the filename in cell A1
    'A1 must hold a valid filename (see http://support.microsoft.com/kb/177506)
    'File will be saved in the same directory as this file
    
    Dim sFileName As String
    
    With ActiveSheet
        sFileName = .range("A1").Value 'assumes a valid filename exists in A1
        .Copy
        .ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=ThisWorkbook.path & "\" & sFileName & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=False, _
            IgnorePrintAreas:=False, OpenAfterPublish:=False
        ActiveWindow.Close False
    End With

End Sub

 

[pbornemeier]

Do you need the intermediate .xls files? or do you want bob.xls to create a pdf file for each of its worksheets directly?

For sally.xls to create sally.pdf. there must be code in sally.xls.

This code will save each worksheet in the active workbook as a .pdf file

Option Explicit

Sub SaveAllWorksheetsAsPDF()
    'Save each worksheet in active workbook as a .PDF with the filename in that sheets cell A1
    'A1 must hold a valid filename (see http://support.microsoft.com/kb/177506)
    'File will be saved in the sPDFFilePath directory
    
    Dim sFileName As String
    Dim sht As Worksheet
    Dim sPDFFilePath As String
    
    If Application.Version > 11 Then
        sPDFFilePath = ThisWorkbook.Path & "\PDF\"
        
        If Right(sPDFFilePath, 1) <> "\" Then sPDFFilePath = sPDFFilePath & "\"
        
        For Each sht In ActiveWorkbook.Worksheets
            With sht
                sFileName = .Range("A1").Value 'assumes a valid filename exists in A1
                .Copy
                .ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=sPDFFilePath & "\" & sFileName & ".pdf", _
                    Quality:=xlQualityStandard, IncludeDocProperties:=False, _
                    IgnorePrintAreas:=False, OpenAfterPublish:=False
                ActiveWindow.Close False
            End With
        Next
    Else
        MsgBox "Will only work in Excel 2007 or later."
    End If

End Sub

If that doesn't work as you wish, please post the two excel procedures your are using.

 

[davion]

Yes I wanted the resulting .xls file to create the PDF, I was hoping there was a procedure I could use to automatically program a macro into the resulting .xls file (sally.xls) that could then generate the PDF.
I have the two separate codes (to create sally.xls and.pdf) setup in the main .xls (bob.xls) which work perfectly.

I was hoping to have the aforementioned setup, only because some people using the system find having to create both from the same file "complicated".

But thanks for trying to help though.