Specific area of worksheet to pdf for inclusion in email or FAX

[Kas2401]

Hello

I have a series of worksheets that represent monthly client statements. I ultimately want to be able to send the statements individually or in mass at month end to clients via MSExpress. Having never delved into Macros I am a complete novice and therefore need to be treated with kid gloves.

The worksheets are identical in layout and only differ with user data. I have created cells per worksheet that refer to a 'Master' worksheet to mange statement dating, period, year etc. Also I have a unique 'account number' per worksheet. I want to be able to save the individual worksheets as .pdf documents, creating the path and file name from the existing path and atributes within the file. This I have done as follows.

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)&K7&"_"&K4&J4&".pdf"

which results in a string as follows

C:\Documents and Settings\Kenneth Smart\My Documents\Business Ventures\ABC\ABC077_20124.pdf

All goes well when recording the macro below and it appears to work well, that is until I run it on any of the worksheets that it was not recorded in.

My problem is that the .pdf file name is not updating to the new worksheet data and retains the original file name. I have looked at some comments on the www, however, only being a geek wannabee I have yet to acquire the skill-set to understand whether my problem is unique.

I don't think it has anything to do with the .pdf creator, more like a refreshing or waiting period for excel to catch up with itself. As you can see from the macro I do go to the lengths of copying the result of the formula string as a 'value' into a secondary cell in an attempt to force the calculation to reveal the updated file name, but with no luck. I am confident that there is a very simple solution, perhaps this is why it evades me.

Any assistance will be welcome.

Sub Save_pdf()
'
' Save_pdf Macro
'

'
Range("Q1").Select
Selection.Copy
Range("R1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("R1").Select
Application.CutCopyMode = False
Range("A23").Select
Application.CutCopyMode = False
Range("R2").Select
ActiveCell.FormulaR1C1 = _
"C:\Documents and Settings\Kenneth Smart\My Documents\Business Ventures\ABC\ABC077_20124.pdf"
Range("A23").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\Kenneth Smart\My Documents\Business Ventures\ABC\ABC077_20124.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub

[Ken Puls]

Hey there,

I've cleaned up your macro a bit to get rid of the extraneous stuff:

Sub Save_pdf()
    With ActiveSheet
        .Range("Q1").Copy
        .Range("R1").PasteSpecial Paste:=xlPasteValues
        .Range("R2").FormulaR1C1 = _
            "C:\Documents and Settings\Kenneth Smart\My Documents\Business Ventures\ABC\ABC077_20124.pdf"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            .Range("R2").Value, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With
End Sub

A couple of points here though...

• This is using Microsoft's built in PDF writer, not PDFCreator. Don't know if you care, but just thought I'd let you know.
• The code above, no matter what sheet it is run on, will put the file path listed in the code into cell R2. I am don't think that's what you're after, so which sheet houses the path that you want to use?

It may be handy if you could upload a sample workbook stripped of sensitive data for us to look at. We can probably give you some pointers on making the whole code approach a bit better.

[Kas2401]

Hello Ken

Thank you for the cleanup. The placing of the path into cell R2 was just an attempt to ensure that dynamic path calculation via a formalea was working. As I said the recording works perfectly but when the macro is called for the other worksheets the path fails to change.

I have attached my workbook(trimmed for size - nothing sensitive)

My objective is to learn VB and immediate needs are to

1. Distribute statements at month end in mass or individually during the month on request(buttons)
2. The distribution could be via email (express or outlook - embedded or attached as .pdf, excel - choice driven), fax from PC
3. The distribution of supporting invoices previously scanned in as 'numbered.pdf' documents. My throughts here are that as the statement is a list of invoices and the invoices are named using the invoice numbers there is a link that just might facilitate the automatic attachment of.pdf (invoices)files into an email.
4. Sending emails requesting payments for arrears transgressions based on manual scans of the age analysis worksheet
5. Re-sending adhoc documents(individual invoices or other docs) on request.

Of course there are other matters, but the above ranks at the top.

I have just achieved the renaming of the individual worksheets to a name created in cell A1. Simple but very chuffed with myself as two days ago I have never coded anything.

I have also been messing with other code borrowed from various places.(ref attached workbook)

Last night I sat down and started to trawl the web for info regarding knowledge acquisition for VB or even C++(not sure which route to go). I know that this stuff exists but have little to no idea where to start. I think I need a very structured workbook to build a foundation.

Thank you for all your assistance and any guidance you may have.

God Bless

Kenneth

[Ken Puls]

Hi Kenneth,

Okay, so you're a brand new coder then? That's very cool. You're embarking on a great journey that you are going to find will pay MASSIVE dividends if you stick with it.

The very first thing I want you to do is go into the Visual Basic Editor (VBE) --> Tools --> Options. Check the box beside "Require Variable Declaration". This will automatically place the words "Option Explicit" at the top of every new module for you. Never write any code above that line.

The reason we do this is to force you to declare all your variables. This will help you avoid weird errors and also help you learn about data typing. In fact, I can show you what it does right now...

Go into Module4 and put Option Explicit at the top. Now go to Debug-->Compile VBA Project.

See that error message? That's telling you that Excel doesn't know what K7 is. If you did not have Option Explicit on, it would interpret it as an empty Variant and throw an error at runtime. This isn't a big deal as it throws an error, but there are situations where it may not and results are not what you expect. The range should be referred to as Range("K7")

At any rate, back to the matter at hand.

I'd like to break your project down and tackle things one by one. As we go through you'll start learning and be able to take further steps on your own too.

The original question you had on the SavePDF routine... I believe you should only need this version:

Sub Save_pdf()
    With ActiveSheet
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            .Range("Q1").Value, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With
End Sub

If you can test that and let me know, that would be great. The change I'm making here is to force it to read directly from Q1 on the Active worksheet. I think that should take care of your issues.

Once we've got this one sorted, start a new thread and we'll tackle the next issue. You'll have a better chance of getting help from others too if you keep the threads to single bite sized issues. And yes, feel free to post all isues in individual threads at the same time. We can work on them concurrently, just keep the focus is all.

Before you do though, I would suggest you get rid of all the extraneous code that isn't working. It will help clarify the areas we need to be looking at.

Hope this helps,

[Kas2401]

Hi Ken

Thank you for the encouragement. I will try the code tomorrow. My question at present refers to your closing comments. How do I know what is extraneous as my limited knowledge base hardly qualifies me.

Best Regards

Kenneth

[tommyt61]

Kenneth,


Very nice work on your Excel file. I do a very similar thing that you are doing except i am sending invoices instead of Statements to customers. Here is how i go about it, maybe you can work off my method to fit your situation.

I took your file , made some changes. maybe this will accomplish what you are trying to do.

to use the file i uploaded...... create a folder Named C:\Statements (Code will not work if you do not create this folder)
Save the Statements.xlsm file i uploaded into C:\Statements

Note: i don't use any of the existing macros that were in the file .

Using the file:

Top right of MASTER sheet right under you Logo you will see 2 buttons

Save Statement and Reset

click on MASTER Sheet
fill in your data
click the Save Statement Button.

you will now have a ABCNNN.pdf and a ABCNNN.xls file saved in C:\Statements\2012\05

(The reset Button clears the MASTER sheet and fills in the Default values so you are ready to do another Statement.)



1. I don't save each Statement as a sheet in the workbook. I save a completed master as a .PDf and as a .XLS
this way you only have the MASTER sheet in the workbook.

2. The macro in this file automatically creates a folder based on the month and year. This helps automate it so you don't have to always remember to create new folder structure when the year rolls over. It will automatically create these for you.

3. so all statements that are generated for example in April 2012 will be located in C:\Statements\2012\04\

I only create a file name based on the Acount Number EX: ABC099. There is no need to put the year and month name in the file name.
Of course you may have specific needs for this and changes could be made.

Keep in mind this is just one way to do this, but it will give you a very good head start on looking at how this code works so as to make changes that you might want to make.

Note: This all works off of PDFCreator you will have to make changes if you want to use built in Saveas .pdf. I personally prefer PDFCreator myself.

[tommyt61]

See if this is what you are trying to accomplish using your Excel file. Replace your Save_pdf macro code with this code. Run macro on each sheet individually . Might be good idea to assign the macro a keyboard shortcut like CTRL_Q

Sub Save_pdf()
'
' Save_pdf Macro
'
'
    
    Range("Q1").Select
    Selection.Copy
    Range("R1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A20").Select
    
    
    
    Range("Q1").Select
    Selection.Copy
    Range("R1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("R1").Select
    Application.CutCopyMode = False
    Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        Range("R1").Value
        
    Range("A23").Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Range("R1").Value
 _
         'Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
End Sub

[Kas2401]

Hello Tom

Thank you for the feedback. I most certainly will start to look at your solution and code. My needs do differ from yours in that my invoices are pre-printed and made out on site at the time of the job being undertaken. Each invoice MUST be accompanied by the customer order if payment is to be effected, however, the order is in most instances an administrative nightmare and is only produced after the job is complete (60min or so). Sometimes we have left the premises and the order is still not available so we are constantly chasing missing orders(hard copy). Our invoices are captured to the statements during the course of the month to slowly build a picture of forthcoming cash flow as payments are collected made on a 30 or 60 day basis. Every month ,without doubt, each of the customers have problems in that they lose our invoices. I call them problems as this appears to be a normal tactic in delaying payments here in SA. They refuse to make any payment unless it appears on a statement and the invoice will not be considered if it does not cross reference an order number supplied by the customer.

My objective is to send the statement and accompanying invoices(reflecting the order numbers) electronically in mass and ad-hoc as the need arises. This will result in reducing the 3 part invoice to a 2 part and save some costs as well as create a simple process flow for our customers. At present not all customers use email and some rely heavily on FAX, therefore I need to use an indicator in the statement to drive the route my code needs to take(email or fax).

I will post my progress soon.

Best Regards

Kenneth

[Ken Puls]

Thank you for the encouragement. I will try the code tomorrow. My question at present refers to your closing comments. How do I know what is extraneous as my limited knowledge base hardly qualifies me.

Actually, this might be easier than you think.

Of all the code you have in your file that you were trying to cobble together... if you don't or won't run any of those pieces, then they are what I would classify as extraneous. If in doubt:

• Highlight the block of code from Sub to End Sub
• On your Edit Toolbar, click the "Comment Block" icon (black line, 2 green lines and 2 black lines). This will insert a ' at the beginning of each line.
• Run each routine you think you need to run

If you don't get any errors, then you didn't need that block of code and it can be safely deleted. If you get an error, then uncomment it as it's needed.

I wouldn't modify/comment out any of Tommy's code though.

(Sorry for the late reply. Been offline since Friday, and it's really busy here right now.)