+ Reply to Thread
Results 1 to 8 of 8

Export as PDF with file name stored in a cell

  1. #1
    Registered User
    Join Date
    01-14-2016
    Location
    Trinidad & Tobago
    MS-Off Ver
    Office 365
    Posts
    17

    Export as PDF with file name stored in a cell

    Hi,

    I'm having a problem here which I searched in forums and I tried to fix it based on what I learned yet no success. I need solution urgent so I might seek an answer in other forums as well such as ExcelGuru and MrExcel ( Cross post policy)

    I wanted to create a macro selects some part of my worksheet and saves it in to the defined directory with the file name in A1.
    A1 had the formula as the file name display. I thought it was the problem so I moved the formula to A2, added Copy-Paste Values action to the macro (copy to A1 again) But still the same problem. It gives me an error saying File Not saved. When I click DEBUG, I see whole code block is colored yellow in VBA editor.

    Here below is the code

    I would appreciate if anyone could point out the mistake....


    Sub PP()
    '
    ' PP Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+P
    '
    Range("A2").Select
    Selection.Copy
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1:U86").Select
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\Murat Erguden\Dropbox\PLANET\5STAR CHA\PAYROLL\2017\ & Range("A1").Value ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
    Application.WindowState = xlNormal
    End Sub

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    4,972

    Re: Export as PDF with file name stored in a cell

    You need to create a valid string for the file name - depends on what you have in A1, of course, and that the folders through

    C:\Users\Murat Erguden\Dropbox\PLANET\5STAR CHA\PAYROLL\2017

    actually exist.



    Please Login or Register  to view this content.
    The code that you posted should not have compiled, giving the error "Expected end of statement" and highlighting the A1. I added a double quote after the 2017\
    and an & between .Value ".pdf"
    Last edited by Bernie Deitrick; 11-08-2016 at 05:47 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    01-14-2016
    Location
    Trinidad & Tobago
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Export as PDF with file name stored in a cell

    A2 has the formula =TEXT(G2,"mmm/dd/yy")&" TO "&TEXT(P2,"mmm/dd/yy")
    A1 has Oct/03/16 TO Oct/16/16.
    The macro copies A2 and PASTE VALUES to A1. I thought the VBA didn't like the formula so I tried this.
    How would I add the string necessary to the code?

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    4,972

    Re: Export as PDF with file name stored in a cell

    No the macro does not like your date format - slashes of any kind are not allowed in file names.

    So, you could try to replace the / with -, like so

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor unit285's Avatar
    Join Date
    10-29-2015
    Location
    TN
    MS-Off Ver
    Office 365
    Posts
    242

    Re: Export as PDF with file name stored in a cell

    Well, when i try to paste this code it blocks me for trying to inject a sql ??? Weird
    http://prntscr.com/d4u831

    http://prntscr.com/d4u97r
    Last edited by unit285; 11-08-2016 at 06:34 PM.

  6. #6
    Registered User
    Join Date
    01-14-2016
    Location
    Trinidad & Tobago
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Export as PDF with file name stored in a cell

    Yes. It was the problem. Thank you very much....

  7. #7
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    4,972

    Re: Export as PDF with file name stored in a cell

    Sometimes <> gets mistaken as HTML code.

  8. #8
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,028

    Re: Export as PDF with file name stored in a cell

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    http://www.excelguru.ca/forums/showt...name-in-a-cell
    http://www.mrexcel.com/forum/excel-q...ml#post4677965

    This is not the first time you disregard forum rules, be it on this forum or others...
    Last edited by Pepe Le Mokko; 11-09-2016 at 04:34 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Export cell value to txt file
    By francois1975 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-21-2015, 08:19 AM
  2. export cell content onto notepad file
    By birdboyee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-24-2014, 09:59 PM
  3. Export each row as text file using first cell in row as title
    By aaronmb in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-11-2013, 06:40 AM
  4. Export cell to txt file
    By monkeychops in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-12-2008, 01:31 PM
  5. [SOLVED] Cell export to Text File?
    By Steve in forum Excel General
    Replies: 3
    Last Post: 12-07-2005, 11:15 AM
  6. Removing file names that are stored in open file box?
    By Gary Kent in forum Excel General
    Replies: 1
    Last Post: 08-06-2005, 04:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1