+ Reply to Thread
Results 1 to 93 of 93

Converting copied text into table

  1. #1
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Converting copied text into table

    Hi everyone, I'm new to a job at a small nonprofit.that requires me to create invoices. My boss sends me emails with people's information, which I then reformat to put in invoices in word, save as PDF, record in excel, and send out with an email from Outlook. I would love to find an easier way. I'm wondering about putting the info in an excel table so that I can then use a mail merge to create an invoice with the address--unless it turns out to be more work than to format each one (I'm newish to Excel).

    The info I get is listed in the following way:

    First Name: Mia
    Last Name: Kant
    Email Address: mika@gmail.com
    Country: Afghanistan

    Business/Institution Information
    Company: QuI
    Job Title: Gender Manager
    City: Kabul
    Country: Afghanistan
    Website:
    Phone: 009370500026
    Skype ID:
    Work EMail Address: me0ai@gmail.com

    I need to use some but not all of the info, but if I could get it to format into a table, I assume I could then pick and choose.

    Any help would be greatly appreciated!!

    Thank you!

  2. #2
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Welcome to the forum

    Is the info as depicted above listed in Word or is it already in Excel. Ideally we need to get it into Excel to split it up into data to create a table of it...
    Regards,
    Rudi

  3. #3
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Thanks, Rudi!

    The info is just in an email (in outlook).

    Thanks again!

  4. #4
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    I'm trying to determine if any automation is needed. Do you receive only a few names at a time that you can easily copy/rewrite the data into a tabular format in Excel (and in so doing, create a source for mail merge), or do you recieve hundreds of these names that you require automation to split the content and set it up into a table for you?

    I'm not sure if there is a specific task you are needing assistance with or if you were just after general advice to smooth out the process you listed above...
    Please clarify
    TX

  5. #5
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    I receive about 10 names a day. I could copy or rewrite the data, but then I'm not sure how much time I'd be saving.

    General advice on smoothing the process out would be ideal. I just figure there's a way to use Excel to make it all happen more automatically (such as creating the invoice numbers and the date and then recording the information back on the sheet where we track everything), and I'd love some advice.

    Thanks!

  6. #6
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    OK. If you get about 10 a day, that is not to much effort to capture into Excel.
    You have the right idea about a more optimized process. I would advise to do the following:

    1. Set up a table in Excel and capture these names and addresses into the table
    2. The table will act as the source for a mail merge to the invoices (which would be a form letter in Word, with the fields in the form letter connected to the Excel table.
    3. The invoices can be merged to email, or to form letter if for printing
    4. PDF documents can be saved from the form letters very easily in Word.

    I'm not sure if there is more in your processes with this data....but working in this way will be most optimal.

  7. #7
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Thanks so much! Is the only way to make that table through manually entering everything?

  8. #8
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    If the data you receive is in a consistent format, then macro code can be used to automate the transfer of the data to the table. If you can provide a sample of dummy data that resembles the format you get it in, as well as an idea of the table you want to put it in, then I can see about automating the process...

  9. #9
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    The data (for the customers) looks exactly like how I copied it into the first message. How would a macro work?
    Thank you again! You're amazingly helpful!

  10. #10
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    And I suppose the table would just have First Name, Last Name, Title, Organization, City, Country, Phone, and Email, as I need this information to format their contact info in the invoice, to make the file name I then save as a PDF, and to record in a separate Excel sheet their last name and the date the invoice was sent.

    If it would help to see a sample of any of these documents, please let me know.

    Thanks again!!

  11. #11
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    It's a pleasure to help.

    Attached is a template that contains a macro to transfer the data into a table on the second sheet.
    Just copy the raw data from Outlook (or whatever source) and paste it into cell A1 (to replace old data) of the RawData sheet
    The click the Transfer button and the data is processed to the second sheet with a table.
    The more data you add to RawData and process, it will simple append it to the bottom of the table

    This table can act as a source for any mail merge you do in Word. IOW, if you have an Invoice in Word, you can use Word's mail merge tools to connect to sheet 2 in the attached template and generate automatic invoices based on the records in the table. The merged invoices can easily be saved as PDF files as needed. As for the recording of the date for each sent email, its just a matter of using the Table. Filter for the appropriate records that were invoiced and autofill the date down the Date Sent column that I added in the table too. So the table is not only a source for the merging, but also a record of when the invoices were sent.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hello,

    I am in awe that you would be willing to do this for me! When I open the file and click the button, it says that the macro cannot be found. Do you know if there's a trick to getting it to open?

    Thank you!!!

  13. #13
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    *Perhaps it's version I'm using. I'll try on my work computer and will then let you know!

  14. #14
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi, It works!!! Thank you SO MUCH.

  15. #15
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    I'm actually going to need a couple more macros (to have the course number generate the other course info, found in a table). I don't want to keep bothering you, so do you have any suggestions for resources so I could learn how to use macros?

    Thanks a lot,
    Sophie

  16. #16
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    Glad you came right. The button on the sheet is just a convenience but you can run the macro in other ways too; usually from the View ribbon, Macros and Macros. Another way to access the macros in a file is to press ALT+F8. The dialog that appears will list the macros and you can choose one and press Run.

    I am happy to assist further... if I have time available, I usually frequent these forums and see if there is something to assist with.

    I do not understand your question regarding the generating of course numbers. If you need more help in this regards, just send steps or sample information that clarifies this.

  17. #17
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    I appreciate it so much!

    So, I'm still trying to figure out the simplest way to make the invoices. I wonder about doing them in Excel rather than in Word because if I did a mail merge, I assume it would make them for the whole list rather than just for the newest entries (correct me if I'm wrong).

    What I was trying to say about the course number is that my organization uses course numbers to keep track of what courses people sign up for, but then we put details in the invoice (course names, start and end dates). I think I can probably do a vlookup to
    include all of this data in the invoice if it's in Excel, but since I'm teaching myself everything from scratch, I don't have a
    clue if any of the things I'm doing make sense.

    Is it possible to have macros that populate an invoice within an excel file that I could then save as a PDF? I know there are templates, but I don't know how it works to have each line populate an invoice that I could then save and then reuse the template (with a new invoice number).

    Thanks!

  18. #18
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi Sophie,

    It is possible to have this invoicing process in Excel, but I would not recommend it as it will cost much more effort to maintain, including the fact that you loose the ability to use the mail merge process if the invoice exists in Excel.

    In the attached, you will notice that I have extended the table to include Course name and Start/End Dates. When the person is entered into the table via the macro, or manually, you can simple assign the course and start/end dates for that person. All these fields (columns) become available for the mail merge in Word.

    You asked: "...if I did a mail merge, I assume it would make them for the whole list rather than just for the newest entries (correct me if I'm wrong)."
    You are wrong...
    You can merge the whole set, or you can merge sub sets based on selecting with check boxes or using filters to determine WHO gets a mail merge.
    See the three screenshots I made that illustrate a mail merge that is connected to your table.
    Image 1 shows a general form letter (or invoice) using most of the fields
    Image 2 shows which button to use to select an initial mail merge recipients list
    Image 3 shows that you can even edit the recipients details, or use filters to fine-tune WHO receives a mail merge invoice.

    Note that with mail merge, you can do a standard form letter mail merge, and then save the resulting merge as PDF's, or you can even merge to an email, so multiple emails are sent to recipients via Outlook. There are many options with the flexibility of Mail Merge.

    I hope this provides even more insight to make your processes more productive.
    Attached Images Attached Images
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    I am amazed by your kindness! And I hope you don't think I'm taking advantage by continuing to ask questions until I have a system that works exactly how I want/you get sick of answering.

    If I want to rearrange the columns or do it slightly differently for tracking purposes, is there a way I can edit it without bothering you? Or should I tell you the exact changes?

    Thanks again!
    Sophie

  20. #20
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    Rearranging the columns in the table will affect the macro, but it will be quite easy to modify if you want. If you see the macro, it collects (for example) City from the raw data and puts that into column E. See the code below. If you rearrage columns, you must just change the column letter in the code which is marked in red...

    If it does not work out... I will help as required.

    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Thank you again! I'll try that out. Now I'm struggling with the mail merge. It looks like you can't save each document separately, so then it doesn't actually save me any time.. Is there a way to do that? I'll be using the following format to save, if that matters: Last Name_First Name_Invoice_Date (30-April-2014)_Course Number.

    Any thoughts would be greatly appreciated!

  22. #22
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    I have a macro that splits a mail merged document and even saves it as PDF.
    I see if I can dig it up on my PC and send to you.

    It will prompt you to browse for the merged letters and split it into individual docs saving as PDF.
    I may just have to run it to see if there is any changes I need to make to it...

    Will send it soon....

  23. #23
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    If you find that macro, that would be great!

    Thank so much,
    Sophie

  24. #24
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    Here is the template.
    When you run it, it prompts you to browse for the source document, which in your case will be a saved version of the mail merge (which results in a single document with multiple copies of the form letter that was addressed to the recipients. The macro splits them at the break. please note....currently the attached macro splits at a section break. I'm not sure if a mail merge inserts a section or page break. It is is a page break it will simply be a minor change to the macro. You must just let me know.

    Attached is the template...
    BTW: When the macro splits the source document, the individual copies are saved in the same location as where the source resides.
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hello again!

    Thank you so much for this! This is great, and I’m going to use it for other projects. I’m now thinking, though, that I’d like to create an invoice template in Excel rather than in word where I could type the invoice number and have all the other info pulled in from the tracking sheet where I keep track of everyone. It seems like that would be more useful for my purposes, as I could clearly create individual files fairly effortlessly.

    Could you tell me if there’s any issue with this idea that you can think of? If not, would you mind reviewing the following steps that I’m thinking about using? Keep in mind, I have no idea how to do most of it, but I’ll watch some tutorials and hopefully figure a lot of it out.

    1. Create a new invoice template
    2. Add in vlookup formulas so that when I enter the invoice number, all other info will be pulled in from my tracking sheet
    3. Create a macro to save as a PDF with a new name (I’m not sure how to do this or how to do it with the naming format I currently use, which is LastName_FirstName_Invoice_5-May-2014_Course. The only other issue with this is that I want there to be an underscore between all words in the name.)
    4. If possible, I’d love that macro to also attach it to a new email in outlook that I can send to the listed email address. (I’ll still have to figure out how to make a generic email that can be merged with the course info to send out)
    5. Create a macro to clear the contents but keep the formulas so that I can reuse the same template.

    If you have a second, I’d love to hear if you think this plan makes any sense.

    Thanks!
    Sophie

  26. #26
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi Sophie,

    Your request and all the points you suggest is very doable.
    VLOOKUP's can handle the collection of data from the source list to populate the invoice;
    Macros can handle the saving and emailing of the invoice. (In actual fact, Excel has a way of emailing straight from inside Excel. Not really needing a macro for this!)

    It's not a lot of work. If you can set up an invoice structure, I can help with the VLOOKUPs and guide you through the automation and emailing.

  27. #27
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Thank you again for your help! I’m in the process of making the form, and I think I should be able to figure out a lot of the vlookups. What I could use help with, though, is figuring out how to create a macro that will save the printable part as a PDF with the following format: Rodrigues_Alba_Maria_Clara_Invoice_15-April-2014_E0633 where I would have had cells with that info divided as follows:

    rodrigues alba
    maria clara
    4/15/2014
    E0633

    I’m not sure if that’s clear, and I can send a template—though I don’t know exactly what cells that information will be in yet, but essentially, I need all of the words to be capitalized, there to be underscores between each word, and the date to be able to appear day, month (spelled out), year.

    Is that possible?? I was thinking if I maybe had the text all in A2-D2, for instance, it could show up formatted this way in E2 to then be used through a vlookup to provide the title.

    Thanks for all of this!

  28. #28
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    I think it is best to wait for you to send a sample template with some dummy data. You don't need to worry about pulling all that info into a single cell. The macro can take care of combining various cells of into together into a valid file name.

    All I need is a "finalized" template that will not change. So put some thought into it, structure it up exactly the way you need it. Once that is in place, attach a sample of it and I'll set up the macro to deal with the file name.

  29. #29
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hello!

    Sorry for the delay in writing, and I hope you’re well.

    I was wondering if you might be able to help me again with this (same) invoicing project. First, I need to slightly change the way that I had entered data before, as the source I will be using has changed, as did the tracking sheet I will be using.
    Would you be able to help me figure out these macros? I tried to do it myself using the formulas you had made for the previous sheet, but there were too many differences, and I couldn’t get it to work.

    I have attached (1) a word document showing what information I will be entering. I have highlighted the information I would want extracted.
    (2) the invoice sheet I will be using. I’ll be copying in about 600 entries that already exist in this format, but I assume that you don’t need to see them in order to work on this. In the excel document, I have included a “Raw Data” sheet, where I would copy exactly the info from word, and a “Course” sheet, where I have information I will use for a vlookup in the first sheet.

    In the first sheet, in Row 2, I have included what I hope to extract from the Raw Data. Please note that I would like words to appear with capital letters, despite how they appear in the raw data. Please also note that the last name appears twice, once under “Name” and once under “Last Name.”

    In Row 3, I have shown you how I would eventually like the whole entry to appear, where I will enter an invoice number that is one greater than the above row, today’s date for the sent date (formatted in the British way), and course info based on a vlookup that I have created. I don’t know if these things can happen immediately when I transfer the data from the raw data sheet, but that would certainly be ideal.

    I would be forever grateful if you could help me with this. I will then make the invoice form I will need, send it to you with a few questions, and then stop bugging you!!

    Thank you again,
    SophieToImport.docxInvoices-due-2014.xlsm
    Attached Files Attached Files
    Last edited by learningslowly; 05-21-2014 at 12:44 PM. Reason: (wrong attachment)

  30. #30
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    I'll be happy to assist.
    In order to help I need to confirm a few things.

    It is easiest to set up macros when one has a few constants (consistencies) to work with.
    Please look at the image below and confirm if each Word letter (or form) you receive will have exactly the same labels. I assume you will be copying the data into the raw data sheet one at a time as you receive these letters??? If I can base the macro off of the labels, basically to find the label called: Course or First Name or Country, then I can use that as a find location and collect the data to its right side and place that in the Main Sheet.

    See image: 1.jpg

    Please confirm. Once I have an idea of how you manage the process, I can set up the macro to work based on that.

    TX

  31. #31
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Thanks so much!

    I copy and paste the information from constant contact. Here, I copied it into Word, but in general, I'll copy it directly (one entry at a time) into Excel. I would copy and paste the entire thing, and all of the labels will be the same.

    Does that answer your question? file:///C:/Users/Sophie%20Feintuch/D...%20Details.htm

    Thanks!
    Sophie

  32. #32
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Yep, that basically answers it.
    If the labels are constant I can use that and set up a macro to transfer the info from sheet 2 onto the main sheet.

    I have a few looming deadlines for code at work, so unfortunately that needs to take precedence, but i'll get to this as soon as I can...

  33. #33
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    I appreciate it so much!

  34. #34
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Invoices-due-2014.xlsmHi Rudi,

    I don't want to overwhelm you, but I wanted to send you an updated version of the document that included the invoice templates (I promise after this, I will leave you alone!!!!). I thought maybe it would be a good idea to have the invoice be a sheet within the same document that would then save separately. If that isn't possible, I can separate them.

    I created three versions (Invoice, InvoiceWithWire, InvoiceAuditor) that are essentially the same and need to be able to do the same things. The only difference is the payment info I include and if it's made for an auditor. If it's more work to do it this way, I can just have one sheet that I edit.

    Here are my questions (for after you help me get the raw data to transfer in to the InvoicesDue2014 sheet):

    1. You'll see I used vlookups so that if you change the invoice number, it brings everything in from the InvoicesDue2014 sheet. Is it possible to do this with a macro so that it doesn't all turn to "N/A" when it gets erased? Not a big deal, but since other people will use the sheet, it would be great if it could clearly indicate that you need to just enter the invoice number.

    2. I didn’t know how to combine the emails and am not sure what will happen if someone only provided one email or provided the same one twice. If there’s a simple solution to this, I’d love to hear it!

    3. I’d like a button that saves a copy of the Invoice as an excel document and another copy as a PDF. Of course, I only want to save the section with text so that it looks like a printable document (not all of the worksheets, and nothing on multiple pages.) This could be the same button, and the documents will be saved in the same place with the same name. As I previously mentioned, here is the name I would use (according to my example): Wenway_Essie_Invoice_21-May-2014_E10114.pdf (or .exl). Everything comes from a cell on the InvoicesDue2014 sheet except for the word “Invoice.” The only catch is that if someone has two first or last names, these need to have underscores between them.

    4. I’d like another button (or it could be the same one) that clears everything.

    I'm already forever grateful for the support you've given me. Thank you for being so generous with your time, knowledge, and skills.

    Thank you again for helping me with this, and I do promise I'll leave you alone after this!

    All the best,
    Sophie

  35. #35
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi Sophie,

    Don't worry with the amount of questions etc... You are on an open forum and have all the rights to ask questions and for help. In the same light, I can ignore you if I want....though that will not happen as I am not that type of person. I do want to admit that I currently have a bit of a tight schedule reg. the work I do at my "actual" place of employment. I have been traveling for work recently and have come back to the office with a lot on my plate and some looming deadlines. I will still work on your document (personally I cannot leave things I start unfinished....that's just who I am), so I will continue to assist until your document is working. However, I can put more time into this over the weekend, and see how far I can get with those questions of yours (which BTW, seem to be some great ideas ). During the week, and until my deadlines have lifted, my priorities will need to be on my own work.

    Hope you understand.
    I'll get back to you over this coming weekend with some results.

    Chat again....

  36. #36
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    I absolutely understand, and I greatly appreciate it!

    Good luck with all of your work!

    Best,
    Sophie

  37. #37
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    Two questions:

    1. Does the worksheet called InvoiceDue have to have the year (2014) after it? I'd prefer that it does not as it will be easier to refer to it in code if it remains the same name always, and not be dependent on the year suffix. Can I remove it? Remember that you do have a column in the Invoice Table that informs to the date and year of the invoice.

    2. Can the headings of the table on the InvoiceDue Sheet have the same names as the Word Form that is pasted into Excel. Again, this will make coding easier. Basically I locate the Name, Country, Job Title, City, ETC, data on the form using a find macro and then I can search for the same terms in row one of the InvoiceDue sheet so I can place the data in the correct columns. Currently the labels are different, for example the Word form (on Raw Data sheet) has Job Title where the InvoiceDue sheet lists it as Position?

    Is there a possibility that you can rename these fields (in the attached file) to be consistent between the form (see Raw Data - columns A) and the table in row 1 of the InvoicesDue sheet, and then re-attach and send me the updated template back? (I am not sure which table fields ought to correspond to which form fields)

    Is this possible?
    TX
    Attached Files Attached Files

  38. #38
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    1. The worksheet can be "InvoicesDue." It would be great if the whole file had the same name, but the sheets can be called anything that makes it easier.

    2.It's fine to change those, too.

    3. I think I made those changes. Let me know if you have any more questions!

    Thanks again!
    Sophie
    Attached Files Attached Files
    Last edited by learningslowly; 05-23-2014 at 03:09 PM. Reason: Oops, wrong document

  39. #39
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Excellent. TX.
    I will work with it based on these changes...

  40. #40
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    See if the following template is working the way you need it...
    I hope I covered all the necessary details.

    Oh, BTW:
    I did not modify the formulas in the other two invoices. I only modified the first sheets formulas (on the Invoice sheet)
    You will see that I used an IFERROR(...) function to hide the #N/A error if no value is found. You need to do the same on the other two invoice sheets...
    Attached Files Attached Files
    Last edited by RudiS; 05-23-2014 at 06:07 PM.

  41. #41
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    Thank you so much! I won't be able to try this until I'm back at work tomorrow (I seem to have problems with macros on my mac), but I'll let you know as soon as I can try.

    Thanks again for your help!

    Best,
    Sophie

  42. #42
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    Is it possible to make it so that just the first letter of each word is capitalized rather than everything?

    Also, can I tell you where to have the documents saved (it'll be on a share drive) rather than where they are currently saving by default?

    Other than that, it looks amazing, and I'm so happy with it!

    Thanks!
    Sophie

  43. #43
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Sure....and glad it is working for you...

    I'll make the change to the case option you want when you provide the fixed path. I'll change both those in the macro and send it on to you.
    Let me know...

  44. #44
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Thanks so much! Here is the address I would want them all to save to:

    https://hrea.sharepoint.com/FinanceD...rning/Invoices

    Best,
    Sophie

  45. #45
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    Please run and check if all is OK.
    The data in the records should not be Proper Case, and the fixed SharePoint path is in. I am not sure if I got the syntax correct on the path.
    I think we will quickly discover this if you run it and test....
    Attached Files Attached Files

  46. #46
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi, I get an error message ("Run-time error '91': Object variable or With block variable not set.) when I try to save it. The path is the following : https://hrea.sharepoint.com/FinanceD...rning/Invoices . I tried to correct it when it asked me to debug, but even after correcting the "sPath" I was asked to correct the "sFile," and I'm not sure what to do there.
    Thanks!

  47. #47
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    See image: 1.jpg

    Also, since I have never saved to a Sharepoint location, I am not sure how to structure the entire path?
    Does the path only have forward slashes "/" as opposed to the traditional backslashes "/" of a conventional file path?

    Maybe if you can navigate to the Sharepoint folder and copy the full path with a file name so I can see what the path and file string looks like?

    BTW: You won't need to modify the sFile variable. The result of the filename simply needs to be joined to the path. I think our problem is with the construction of the path and joining the filename to it. If I can see an example of a full path and filename from a sharepoint sample, we should be able to resolve it.

  48. #48
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    I can't tell now if it's saving to the wrong place or not uploading because of authorization problems. In any case, If I look at the properties from this document on my computer (opening the shared drive), the address is: \\hrea.sharepoint.com@SSL\DavWWWRoot\FinanceDocs\ELearning\Invoices

    If I open it online within the Office365 sharepoint, it shows up with this address:
    https://hrea.sharepoint.com/_layouts/15/WopiFrame2.aspx?sourcedoc={158F3EBF-F18B-42DE-9A74-FCEA45BE21F8}&file=Abbas_Ansar_Invoice-6-April-2014-E05114.docx&action=default

    Is that at all helpful?

    Thanks!
    Sophie

  49. #49
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    I was also playing around with it and having it save the excel doc to my desktop, and I noticed that it's saving a blank form (with the buttons still there, too) with only the invoice number filled in. Is it possible to have it save all of the information filled in (I suppose as values)? Ideally, it would be cut so that the buttons are no longer visible (definitely on the PDF version).

    Thanks again for all of this. I am amazed by your kindness.

    Best,
    Sophie

  50. #50
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi Sophie,

    It's a pleasure to help, really. If I did not enjoy it I wouldn't frequent these forums

    I am still not sure of the path to a SharePoint site. I've never had to save to this site before. I'm trying to read up or learn more about the process and will revert back to you if I find a solution. For now I have reverted the code to save back in the place where this template is stored (temporarily). I have fixed the issue about the buttons and also that the copy that gets saved rewrites to values so the data does not get lost.

    Please continue to test on this attached and updated template.
    If I learn anything more about SharePoint paths, I'll let you know and we can fix the macro. For now though, it will save to the template folder and you can always move the file to the SharePoint folders via windows explorer and drag and drop.
    Attached Files Attached Files

  51. #51
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Thanks again, Rudi.

    For now, is it possible to have it save on the desktop instead? How would I tell it to do that?

    Thanks!

  52. #52
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    Attached is the template that will save to the desktop.
    (I have left the other lines in there as well in case you want to revert back to the previous or experiment with the SharePoint locations again).
    See image: 1.jpg

    As for the SharePoint paths: I have looked at various samples in Google searches and it seems our syntax is correct, but if there might still be some underlying factors i am not aware of. Maybe you have an IT person or someone who oversees your SharePoint site in your business that can assist in this regard. See these two pages for samples: http://www.excelforum.com/excel-prog...ml#post3256860 and http://www.pcreview.co.uk/forums/pro...-t3889219.html (if that helps).
    Attached Files Attached Files

  53. #53
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    Thanks again for doing this. I'll talk to the IT person tomorrow. Should the file that you've attached most recently be able to save to the share drive if everything else is correctly set up on our end? Or would it need to be revised? The problem with saving to the desktop is that other people won't be able to use the invoices from their computers.

    Thanks!

  54. #54
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    I have left the 3 paths in the macro. You, or the IT person can comment out (or delete) the ones not needed. See image: 1.jpg

    Obviously the SharePoint path needs to be correctly inserted into the macro if your use that and the "Thisworkbook.Path" and "Desktop" path lines can be removed. I am sure it should work correctly if the correct SharePoint path is typed in. When it is in, it will combine with the sFile variable (which stored the calculated filename) to complete the FULL path.

    The proof is in the pudding
    It's just a matter of experimenting with the SharePoint path until it saves correctly.

    Remember to change it in both the XLS save procedure and the PDF save procedure.

  55. #55
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    We got the path to work! So you know, it was the \\hrea.sharepoint.com@SSL\DavWWWRoot\FinanceDocs\ELearning\Invoices, without the https:.

    I have another question...when people enter their Email and WorkEmail as the same thing, they end up showing up twice on the invoice. Is there a way to do a vlookup (like the one I did) where it only inserts the second if it's different from the first?

    Thanks!

  56. #56
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    THAT'S GREAT!!! Good for you. (Now I learn something too )
    TX

    About your question, and just to clarify. Are you saying that you want to insert the Work Email into the Invoice (Invoice sheet cell C13) if the work email is different from the normal email address in the raw data sheet?

    TX

  57. #57
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Ah.... ignore my previous post... I suddenly grasp what you are saying (silly me!)
    I'll change the VLOOKUP to do what you ask....

  58. #58
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    Here is the new formula that you can slot into cell C13 on the Invoice sheets:

    =IFERROR(IF(VLOOKUP(D17,InvoicesDue!$B:$Z,21,FALSE)=VLOOKUP(D17,InvoicesDue!$B:$Z,22,FALSE),"Email: "&VLOOKUP(D17,InvoicesDue!$B:$Z,21,FALSE),"Email: "&VLOOKUP(D17,InvoicesDue!$B:$Z,21,FALSE)&"; "&VLOOKUP(D17,InvoicesDue!$B:$Z,22,FALSE)),"")

  59. #59
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Thanks! I have two last questions, and then I may actually be satisfied!!!

    1. When saving the file, is there any way for the month to be spelled out "May" instead of "MAY" (caps)?
    2. You made a great popup requesting an invoice number for the InvoicesDue sheet, but is there a way to put one on the Invoice sheet when you open it? That way, people would know how to fill out that form, too.

    Thanks again--I can't believe how well this works!

  60. #60
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    Try these solutions:

    1. Replace this line in the save macros (for xls and pdf)
    Please Login or Register  to view this content.
    with this line:
    Please Login or Register  to view this content.
    2. To have a message box pop up on the invoice sheets (if no invoice number is assigned), do the following:

    -- Copy the code below
    -- Right click on each of the following sheets tabs (one by one) - Invoice, InvoiceWithWire and InvoiceAuditor
    -- Choose View Code from the context menu
    -- Paste the code below onto the active VBA module
    -- Press ALT + Q to close the VBA editor
    -- Save the Workbook

    Now each time you activate the invoice sheets a message will pop up to remind to add an invoice number (if there is no invoice number to start with).

    Please Login or Register  to view this content.

  61. #61
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Thanks so much!

    I pasted the code, and it works perfectly.

    In terms of saving, that makes it say "may" rather than "May", but it's not a big deal.

    I think I've figured out how to make save buttons for the InvoiceWithWire and InvoiceAuditor (viewing the macros, editing, and copying and pasting the previous code with a new name and changing the names of the worksheets).

    When I save the PDF, the file saves as .xls.pdf. Is there a reason the .xls is in there?

    Thanks!

  62. #62
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    One more thing: I know I asked for everything to be in proper case when I transfer it over, but is there any way to make the email address not do that? Otherwise, no big deal, but I figured I'd ask since you're being so helpful with everything else!

  63. #63
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi,

    Sorry that was my mistake about the May being may! I did not test it but just assumed the syntax would be correct.

    The correct syntax for that sFile line is this (for the xlsx file save):
    Please Login or Register  to view this content.
    ...and this for the sFile line in the pdf file:
    Please Login or Register  to view this content.
    Regarding the buttons, its just a matter of copying and pasting the buttons from Invoices to the other two sheets.
    See this image to use the Select Objects method. 1.jpg
    BTW: To align the buttons to the worksheet grid, drag/size them while holding down the ALT buttons to snap to the grid.

    Reg. the xls.pdf
    There must be something wrong with your sFile variable string. Check to ensure there is no xls in the sFile string that saves the PDF.
    You can always recopy the line from the attached file. I fixed everything in the file for you (except that you will need to rewrite the SharePoint path). The may will now save as May, the buttons are copied to all three sheets and the email address will be lowercase.

    I have attached the updated workbook with ALL the requested changes, incl. the lower casing of the email addresses. You can either use this attached copy as the new template (remember to update the SharePoint path in the save macros), or copy the necessary lines of code and replace the code in your file with the fixed code in this file....up to you!
    Attached Files Attached Files

  64. #64
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    Thanks again! Regarding the buttons, the way they are copied and pasted, they save a copy of the Invoice sheet but not the InvoiceWithWire sheet, for instance. I tried in the copy you sent me but had the same results. Can I just make a new macro that looks the same but saves that sheet instead?

    Thanks,
    Sophie

  65. #65
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    I tried to copy it like this:

    Sub SaveInvoiceWithWireAsPDF()
    Dim sPath As String
    Dim sInv As String
    Dim rF As Range
    Dim sFile As String
    Dim sPCell As String

    sInv = Worksheets("InvoiceWithWire").Range("D17").Value
    Set rF = Worksheets("InvoicesDue").Range("B:B").Find(What:=sInv, LookAt:=xlWhole)
    sPath = "\\hrea.sharepoint.com@SSL\DavWWWRoot\FinanceDocs\ELearning\Invoices"
    sFile = sFile = Replace(rF.Offset(0, -1) & "_" & rF.Offset(0, 13) & "_Invoice_" & Format(StrConv(rF.Offset(0, 3), vbProperCase), "DD-MMM-YYYY") & "_" & rF.Offset(0, 1) & ".pdf", " ", "_")
    Application.ScreenUpdating = False
    Worksheets("InvoiceWithWire").Copy
    ActiveSheet.Buttons.Delete
    sPCell = ActiveSheet.UsedRange.Cells(1).Address
    ActiveSheet.UsedRange.Copy
    ActiveSheet.Range(sPCell).PasteSpecial xlPasteValues
    ActiveSheet.Range(sPCell).Select
    Application.CutCopyMode = False
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=sPath & "/" & sFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
    ActiveWorkbook.Close False
    Application.ScreenUpdating = True
    MsgBox "File saved as a PDF file at:" & vbCr & sPath, vbInformation
    End Sub


    It saves, but it saves under the name "FALSE", so I'm clearly still doing something wrong!

  66. #66
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Oh gosh.... LOL. I'm overlooking a lot of things Sorry. It's getting late here! I think i must put my brain to sleep!

    No need to copy the entire code three times.
    Simply (and for both the save macros) change this line:
    Please Login or Register  to view this content.
    to become this:
    Please Login or Register  to view this content.
    That will now export the current sheet and save it instead of always the Invoice sheet.

    This assumes that the PATH stays the same???

  67. #67
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    This is amazing, and I sure hope I'm not keeping you up!

    As promised, my questions will stop soon. BUT, there isn't a way for the saved PDF to also attach to a new email (using outlook on the desktop, which is the default), is there? I hadn't thought to bring that up before, but that would be ideal!

    You are very talented, by the way!

  68. #68
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Actually, let me show you what I really want it to do, and then I will be done. Feel free to tell me it's impossible or that you don't have time. I'm attaching the workbook I'm working with, as I've already made some changes/added prior invoices.

    You will see I have an "Email" worksheet now that crafts the email I want to send with the PDF attached.

    Ideally, the macro that saves as a PDF would also:
    1. Attach the PDF to a new email in Outlook
    2. Fill out the "To" to the two email addresses provided
    3. Fill out the subject (see the "Email sheet)
    4. Fill out the body (see the "Email" sheet)

    I have no idea how much work this is or if you have time, but I thought I'd ask!

    Thanks again for everything.
    Attached Files Attached Files

  69. #69
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Thanks for the kind compliments

    Replace the entire "SaveAsPDF" macro in your workbook with the code below.

    Two things to note:
    1. You can replace the .Display to .Send if you prefer to send the email immediately without previewing it.
    2. I am not sure about the forward slash in the .Attachments.Add line in the code. If it debugs, change it to a backslash.

    Please Login or Register  to view this content.

  70. #70
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    Thank you so, so, so much! It works perfectly!

    Last question (I really think that!!!): at the moment for the email to populate, I have to remember to put the invoice number in the Invoice sheet and also in the Email sheet before clicking "SavePDF." Is it possible that it could automatically fill in the email? I thought about doing a vlookup on the Email sheet, but the problem is that sometimes I would be doing it from the InvoiceWithWire sheet, for instance.

    Is there another way to do it? For instance, putting what the email should include in the macro rather than in the Email sheet?

    Please let me know if this is possible, and then I'll leave you alone!!!

    Thanks again,
    Sophie
    Last edited by learningslowly; 05-30-2014 at 10:23 AM.

  71. #71
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Is the PDF attached to the email, but there is not To, Subject and Body in the email?

    If so, is there anything typed out in the cells on the Email sheet?
    Note that the code gets its data directly from that sheet Sheets("Email") - D2, D5 and D7
    Please Login or Register  to view this content.

  72. #72
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    It does work if there's something in the email sheet. It just means that I have to remember to write the invoice number in the Invoice sheet (or the InvoiceWithWire, etc) and again in the Email sheet for it to populate.

    Just to simplify (for other people at my office), is there a way for that to happen without me filling in the number in the Email sheet? For instance, when I click "SavePDF," could the macro transfer the invoice number from that sheet to the email sheet to populate it and then take it to the email?

    Thanks!

  73. #73
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    That was an oversight on my part.

    Modify the code to add the line: Sheets("Email").Range("B1").Value = sInv into the code as shown below...
    That will fix it...

    Please Login or Register  to view this content.

  74. #74
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    It works with the Invoice sheet but not with the InvoiceWithWire one! Any idea why?

    Sub SaveAsPDF()
    Dim sPath As String
    Dim sInv As String
    Dim rF As Range
    Dim sFile As String
    Dim sPCell As String
    Dim sResp As String

    sInv = Worksheets("Invoice").Range("D17").Value
    Set rF = Worksheets("InvoicesDue").Range("B:B").Find(What:=sInv, LookAt:=xlWhole)
    sPath = "\\hrea.sharepoint.com@SSL\DavWWWRoot\FinanceDocs\ELearning\Invoices"
    sFile = Replace(rF.Offset(0, -1) & "_" & rF.Offset(0, 13) & "_Invoice_" & Format(StrConv(rF.Offset(0, 3), vbProperCase), "DD-MMM-YYYY") & "_" & rF.Offset(0, 1) & ".pdf", " ", "_")
    Application.ScreenUpdating = False
    ActiveSheet.Copy
    ActiveSheet.Buttons.Delete
    sPCell = ActiveSheet.UsedRange.Cells(1).Address
    ActiveSheet.UsedRange.Copy
    ActiveSheet.Range(sPCell).PasteSpecial xlPasteValues
    ActiveSheet.Range(sPCell).Select
    Application.CutCopyMode = False
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=sPath & "/" & sFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
    ActiveWorkbook.Close False
    Application.ScreenUpdating = True
    sResp = MsgBox("File saved as an PDF file at:" & vbCr & sPath & vbCr & vbCr & _
    "Do you want to create an email with the PDF attached to send?", vbYesNo + vbInformation)
    If sResp = vbYes Then
    Sheets("Email").Range("B1").Value = sInv
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
    .To = Sheets("Email").Range("D2").Value
    .CC = ""
    .BCC = ""
    .Subject = Sheets("Email").Range("D5").Value
    .Body = Sheets("Email").Range("D7").Value
    .Attachments.Add sPath & "/" & sFile
    .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    End If

    Also, I copied the formula from yesterday for transferring the data, but the email addresses are still capitalized.

    Sorry this is taking so much time!

  75. #75
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi Sophie,

    I think I need to get a fresh and updated copy of the file you are using. Over the last few posts, it feels to me as if I have been putting band-aids on the code, patching up here and there. I don't have an accurate copy of your current file as some "patches" I have made I did not even save. So i think it is time to stop putting band-aids on this injured code and rather get it on the operating table. Let me take a surgeons knife to it and give it a good fix up for good!

    Please send me an updated copy of your file. I believe i know enough of what you want from this project. i'll fix it up once for good this time

    TX

  76. #76
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Thanks! It actually now does seem to be sending the email just fine, so it must have been some weird glitch. The only issue I can now see is with the email addresses being capitalized.

    I'll send you the version here.

    Thanks!
    Attached Files Attached Files

  77. #77
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Right,

    Please put this template through the paces and check if it is running OK.

    TX
    Attached Files Attached Files

  78. #78
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    The email addresses are still transferring to the InvoicesDue sheet in Proper case (they don't appear that way on the RawData sheet).

    Besides that, it look looks amazing, and I'm so happy!

  79. #79
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Ah.... I found the problem!!!!!!!!!!!! I think it was my cup of coffee that enlightened me!

    Please replace the code in your workbook with this...

    Replace
    Please Login or Register  to view this content.
    With...
    Please Login or Register  to view this content.
    This will now completely resolve the issue.

    After all of this...I bet you are pretty up and running on VBA now too

  80. #80
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Yippee!!!

    I am so, so happy about this! And so impressed that you know how to do all of this.

    Thank you for simplifying my life!!

  81. #81
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    I'm so sorry to still be bugging you, but it stopped working right. It works great for the Invoices sheet, but now I want to save an invoice made in the InvoiceAuditorWithWire, and it doesn't save right at all. The name refers to a file from the previous person (last name only) with the date 27-Sep-2013, and a different course number.

    The email is blank.

    I'm using the file you last attached.

    Sorry!
    Last edited by learningslowly; 05-30-2014 at 02:36 PM.

  82. #82
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Is it possible that it works (to save PDF and create email in the InvoiceAuditorWithWire and all other sheets) if the same invoice number is in the Invoice sheet but not if the Invoice sheet is empty or contains other information?

  83. #83
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Sophie,

    We will eventually get there.
    Don't worry about asking....I'm committed to the end...

    Actually this is a normal process. Code gets written and an extensive testing process resumes. This is the only way to iron out all the overlooked and unforeseen's.
    We are on the VERY end stretch regarding this testing...just tiny things still which will quickly start fading away.

    Here is the fix for this latest discovery...

    Replace the first two lines of code in the SaveAsPDF from this:
    Please Login or Register  to view this content.
    To this:
    Please Login or Register  to view this content.
    Let me know if that works...

  84. #84
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Oh, BTW:
    The above change I asked you to do for SaveAsPDF, you need to replace those same two lines in the SaveAsXLS.

    To answer your other question:
    The code works with the activesheet. It is not influenced by what is on the other sheets at the point when you press the SaveAsXLS (or PDF) buttons.

  85. #85
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Looks like it works! Thank you!!!

  86. #86
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Great
    If anything else pops up, just post.

  87. #87
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hello again!

    The template we (you) built is working quite well, so feel free to ignore this. However, as I use it, ways to improve it come to me and was wondering if you could help me figure out how to do them:

    I'm having some issues with the capitalization. Given that it's set up to capitalize just (and all) the first letters of words,I end up with things like Port-au-prince instead of Port-au-Prince and Ngo instead of NGO and Save The Children instead of Save the Children. Perhaps there's no solution to this besides making the corrections in the Invoice sheet myself, but since I know that Microsoft Word would be able to identify these mistakes and correct them, I wonder if there's a way for Excel to do the same.

    Thanks, and I hope you're well!

    Best,
    Sophie

  88. #88
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi Sophie,

    It's great to hear things are working well...

    Re your question:
    The formula in the macro dutifully converts these terms to proper case. It is impossible to program directly into the code when to proper case and when to not, but what can be done is to reference a "Concordance" sheet that lists the irregular term and the corrected term in the column to the right. Then, with a separate macro we can search for all terms listed in the concordance sheet and correct them in the sheet in question.

    Attached is a sample of what i mean. Give it a run to see what I mean with a concordance sheet.

    We can incorporate this concordance sheet into your template (as a hidden sheet) and integrate the macro into your code so that it runs automatically to correct any terms that were wrongly cased by the formula. Of course, you will need to maintain the concordance list by adding terms that need correcting. The concordance list can contain as many terms as you need it to, as long as it maintains that two column format with the wrong term format in column A and the corrected format in column B.

    I do not have your template anymore, so if you want me to integrate the concordance functionality into your template, please upload it again.

    TX
    Attached Files Attached Files

  89. #89
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hello! So sorry for the delay! I didn´t see your message until now. I´ve uploaded the template again so that you can show me how to integrate the sheet (a blank version because I don´t think I should put everyone´s phone numbers and email addresses in a public space). If it´s impossible to make the changes with a blank version, please let me know.

    In addition to making that change, I have two more questions/favors. As always, if you're busy, no worries!

    1. Could you please help me make the macro to have a second transfer data sheet, for when data comes from somewhere else? I occasionally need to copy it directly from an email. I've added a sheet to show you what the data would look like when it gets copied in.

    2. I sometimes have to send a lot of reminder emails at the same time. As you'll see, I've added a template for the reminder emails so that I just write the date I send the reminder email and the new date it's due and put the invoice number into that worksheet to create and send the reminders. However, at the moment I have to copy and paste 100 invoice numbers into the invoice sheet and then tell them to create the PDF and accept creating an email. This is fine, but I'm wondering if you can think of a way to do this faster (so that I'd select all of the invoice numbers at one and generate a lot of emails at once). If this isn't possible through macros, don't worry about it. I just thought I should ask.

    Thanks again!!!

    Best,
    Sophie
    Attached Files Attached Files

  90. #90
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Oops, I hadn't saved the file before uploading!
    Attached Files Attached Files

  91. #91
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Hi Rudi,

    Sorry to write again before even giving you a chance to respond, but I was thinking about what I asked in for the reminder invoices, and I think I have an idea and would like to know if it's possible.

    Given that I need to send a lot at a time, what about making the "Send Reminder" macro not only send the email but then (1) clear the form and (2) fill it with the next invoice number on the main sheet? The only issue here is that I filter for certain due dates and send reminders to those people. Thus, it would have to be the next invoice number that appears in the filtered table.

    Is this possible??

    Thanks, and I hope you're well!!

    Best,
    Sophie

  92. #92
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Converting copied text into table

    Hi Sophie,

    Physically, mentally and spiritually, I am well thanks. (TX for asking - I trust you are too)

    As you may have noticed, I have been away from this forum for a while. This is the first time I pop in for about 3-4 weeks. Even now, I am just sticking my head in to see what is happening, but unfortunately i will not be able to stay long or look into you query as i am running flat out with my own personal work currently. i have been out the office for a while on travels and training events. I am so back-logged with work (and deadlines) that I will not have time to review your latest queries. i do apologize, but instead of just ignoring your posts, i thought I'd just inform you of my absence.

    Could I insist that you post your questions in a new thread so that it attracts the attention and help of others at this point. I am sure that there are many who can and are willing to assist you, even better that I. We all back each other up, and when one has to take a leave of absence, others jump right in

    Sorry to simply abandon you like this, but its not by choice....
    Keep well and I trust your queries will be resolve quickly and efficiently.

  93. #93
    Registered User
    Join Date
    04-25-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Converting copied text into table

    Dear Rudi,

    Thanks for letting me know! I will post my questions in a new thread and see if I can get another kind soul!

    Hope all is well with you.

    All the best,
    Sophie

+ 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. Replies: 3
    Last Post: 12-16-2013, 01:34 AM
  2. macro VBA to exclude first table while converting table to text
    By sshishirkumar in forum Word Programming / VBA / Macros
    Replies: 0
    Last Post: 07-29-2013, 05:29 AM
  3. Replies: 7
    Last Post: 04-21-2009, 02:50 AM
  4. Help with converting text into an excel table
    By fivestringfreak in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-30-2009, 03:05 PM
  5. converting copied data to numbers in excel xp
    By knutsenk in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 04-02-2005, 11:06 PM

Tags for this Thread

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