Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!

All Activity

This stream auto-updates     

  1. Earlier
  2. Hi, Recently I have Office 365 and also SharePoint with the new "Team Sites" environment (previously called workgroups). Anyhow, I have a macro which convert the excel sheet into pdf and puts this pdf in an email ready to send. This is the coding : sub sendPDF() Dim OutlookApp As Object Dim OutLookMailItem As Object Dim PdfFile As String, Title As String Dim myAttachments As Object ' Not sure for what the Title is Title = ActiveSheet.Range("D19") ' Define PDF filename PdfFile = ActiveWorkbook.FullName i = InStrRev(PdfFile, ".") If i > 1 Then PdfFile = Left(PdfFile, i - 1) ' PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf" dit is voor tabbladnaam erbij te geven PdfFile = PdfFile & "_concerning_" & ActiveSheet.Range("D19") & ".pdf" ' hiermee wordt de naam van persoon in bestand opgenomen ' Export activesheet as PDF With ActiveSheet .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False End With ' Use already open Outlook if possible On Error Resume Next Set OutlApp = GetObject(, "Outlook.Application") If Err Then Set OutlApp = CreateObject("Outlook.Application") IsCreated = True End If OutlApp.Visible = True On Error GoTo 0 ' Prepare e-mail with PDF attachment With OutlApp.CreateItem(0) ' Prepare e-mail .Subject = "MPR " & ActiveSheet.Range("H16") .To = "[email protected]" ' <-- Put email of the recipient here .Body = "Dear, " .Attachments.Add PdfFile .Display End With End Sub The excel file is located in a sharepoint team site, as it was previously in a workgroup. When running this macro, the e-mail is being prepared but when the pdf must be attached, Outlook will show the pdf as attachment, but underneath the pdfname there is notification of "Download failed" . Then I have to doubleclick it, then a notification comes up with the text, " you don't have the correct authorization" with two options "Again" and "Delete" . If I click again, then the pdf will be attached... :-( . How can I fix this ? Thank you ! Sapron75
  3. Hey everybody! I was wondering if anyone could point me in the right direction on how to use an Excel macro to communicate with an external application. In this particular case I am wanting excel to basically copy and paste information from completed cells to a mainframe application. One with multiple screens. For example, let's say I was needing to make 30 accounts using the mainframe, and each account has specific fields that need to be filled in (Acct #, various sales codes etc.); I would like to make a spreadsheet with labelled columns for each specific field that needs to be filled in, the user could input all the necessary information for all the accounts in the spreadsheet, press a Run Macro button, and have the process run in the background saving loads of time wasted on very manual data entry. I'm assuming VBA would work for this... I do know the application we are using to access our mainframe (Microfocus RUMBA+) has the ability to run it's own internal macros as well, and there are a few very basic interactions it can perform "right out of the box" so to speak with Microsoft products (exporting screen data to excel etc.). I know something like this is definitely possible as I've seen it in previous workplaces with similar applications. Could the starting place be as simple as Set IE = CreateObject ("Rumba.application")? I'm guessing there are some simple solutions and a couple more advanced ones, and pros & cons to both. Any help would be greatly appreciated! Thank you, -Dane
  4. Caleeco

    Find, copy, paste, find again, repeat

    No worries, give me a shout if you need any help with the rest of the suite! Caleeco
  5. GeeperZ

    Find, copy, paste, find again, repeat

    Ah so simple! Thanks for your assistance. Now all I have to do is make the rest of the suite work and we'll be flying! Again, many thanks.
  6. Caleeco

    Find, copy, paste, find again, repeat

    Hi GeeperZ Thanks for sending over the file. I seem to have a website bug where the NOT (<>) symbol doesnt appear correctly in code samples. If you look at your TransposeData2 sub-routine, you'll see this line of code: Loop While rFound.Address = FirstAddress Change it to this: Loop While rFound.Address <> FirstAddress Tested on my end, seems to work. Let me know how you get on. Thanks Caleeco
  7. GeeperZ

    Find, copy, paste, find again, repeat

    Hey Caleeco, thanks for getting back to me. I'll email the file through. I'll have to strip out some info first but other than that it will be the same as what I'm working on.
  8. Caleeco

    Find, copy, paste, find again, repeat

    Hey GeeperZ Post #2 should be the last 'working' code we made. Has the sheet structure changed at all? Are you able to email me the sheet you're working on so I can do some code testing? Thanks Caleeco
  9. GeeperZ

    Find, copy, paste, find again, repeat

    Hey Caleeco, I'm being driven to resurrect this again. The project never got finished the first time around but as things have developed this has once again become of interest. Since I was last working on this I've had a new computer and I don't appear to have a working copy of the module. Any chance you could help? I've tried running the code as it appears on your forum but it does not work as expected, it gets to selecting the first instance and then Excel asks where I would like to paste the data. If I step into the macro and F5 it just returns the first result over again. I've looked on MrExcel too but no luck 😞
  10. OldFella

    Change Dr to Drive

    If you use Find & Select / Replace on the column that contains the addresses you should be able to replace St without problem. Replace recognizes spaces. I assume that your street names that begin with St. will not have a space before St, whereas Something St does have a space before St, so in Find what: you type " St" (without the quote marks but with a space before the S) and in Replace with: you type " Street" (again, without the quote marks but with a space before the S). That should fix the street names and leave names like St. Peter unchanged.
  11. RareNasturtium

    Change Dr to Drive

    Hey Everyone I have a spreadsheet with about 44,000 rows in it. The address column has short forms like Rd for Road and Dr for Drive. I'd like to convert all short forms. My problem is find and replace won't work on things like St without also affecting street names that start with St. Anyone have any suggestions on the best way to tackle this beast?
  12. OldFella

    How to extract data from PDF to Excel?

    Hi SyafiniAlias I had a similar requirement some time ago. Eventually I developed an easier way of getting the data, but while I was trying to get the data from a pdf file I was working through https://www.myengineeringworld.net/2012/07/vba-macro-to-open-pdf-file.html Have a look at that link - it may help you.
  13. OldFella

    Automated Table Updating + Dynamic Chart

    Hi Caleeco If/when you get around to this one, I've simplified it a little bit. After hours of trial and error I've managed to create coding that achieves a) Lock in the Current Data, so now it's only the three sub-wishes and the dynamic charting that are still confounding me. No doubt what took me hours would have taken you minutes, but at least I finally nutted it out so I'm happy.
  14. Hi, I have a problem. I want to copy all words that begin with “clr_” from pdf file. And save all the word that i had copy in an Excel file. Thanks.
  15. Hi Caleeco I have a couple of new issues to exercise your grey/white matter. They're not urgent or critical so if you're happy to attack them, please just work on them when the mood suits. No need for a 0300hr finish! 🙂 I have a worksheet with 30-odd rows of data, below which I have a table that sits alongside a chart that plots the data in that table. Column B of the table contains dates and column C contains data values taken daily from cell Y11, higher up on the worksheet. Headers are in cells B42 and C42. The dates in the table show the most recent date at or near the top and the oldest dates at the bottom. As needed, I add in 5 more rows of blank cells below the headers. I actually managed to create coding to do this - to move the existing data down 5 rows and put borders around each of the 10 newly created cells below the headers. I would like to improve the sheet in two ways: a) Lock in the Current Data Each morning, in the cell in col C alongside today's date in col B, I set =Y11. At the end of the day I lock in that value. Next morning I move up 1 row and in col C, alongside what has now become the current date, I again set =Y11. I'd like to automate that process with coding that I can attach to a button that will: Look for the first cell below C42 that displays a value (it will currently contain the formula =Y11); Set that as a fixed value, not as a formula; Move up 1 row; If that cell is blank then set that cell =Y11, otherwise (if the table has no blank cells) Call Add5Rows and then set the blank cell (above the first displayed value below C42) =Y11; There are now three sub-wishes. I actually have a number of iterations of this workbook, which is one reason I'm trying to reduce the repetitive manual data entry. In each book, one sheet handles daily results, one handles weekly results, and the other handles monthly results; the wish list and comments above referred to the daily sheet. If it's practicable to do it, after entering =Y11 in col C I'd like to move to the corresponding cell in col B and increment the value. For weekly and monthly sheets I'm guessing it's straightforward - increment weekly by 7 days and monthly by one month from the value in the cell below. Daily could be an issue given that I only record data Mon - Fri, so maybe after Calling Add5Rows the coding could add three days instead of one? That will lock the current value in the table, add in 5 new rows if the table is full, and next day's/week's/month's date will automatically display together with the new value of Y11. b) Static v Dynamic Chart This is one we touched on a while back and I'll confess I've never been able to consistently implement. The current chart is static; the data range is fixed and has to be updated every time I add in a new block of 5 rows. I'd love to make it dynamic so that whenever those new rows get added the chart grows like Topsy to accommodate the new data range. As an example, the chart on screen at the moment shows Chart Data Range = Summary!$B$43:$C$70. Once I Call Add5Rows it becomes Chart Data Range = Summary!$B$48:$C$75 so I have to manually change $B$48 back to $B$43. How, using Offset or whatever, do I convert this into a dynamic chart? Now you can go have a well-deserved beer!! And in case you want it, here's my amateur effort Add5Rows. One improvement it needs but I don't know how - how do I get rid of Range.Select ActiveSheet.Paste? : Sub Add5Rows() Dim LastRow As Long Dim rng As Range With ActiveSheet LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row 'Find the last row with data in it End With Range("B43:C" & LastRow).Cut 'Take the existing block of data and move it down 5 rows Range("B48").Select ActiveSheet.Paste Set rng = Range("B43:C47") 'Put borders around each of the newly created blank cells With rng.Borders .LineStyle = xlContinuous .Color = vbBlack .Weight = xlThin End With End Sub Many thanks.
  16. OldFella

    Obtaining Data from CSV Files

    Hi Caleeco I can see where the problem is - the notifications I'm getting, and your emails, are showing the originating address as [email protected], not [email protected] Anyway, I've now deleted the [email protected] variety from my address book so that should no longer be a source of confusion. Befuddled Olde One
  17. OldFella

    Obtaining Data from CSV Files

    Sure enough - I used the other address - [email protected] I'll resend now. Sleep well!
  18. Caleeco

    Obtaining Data from CSV Files

    Hey OldFella, I just had a scan of my email account... I couldn't see any files from you in July. Could you please re-send and I'll start coding tomorrow! (It's 1am here). [email protected] Look forward to hearing from you Caleeco
  19. OldFella

    Obtaining Data from CSV Files

    Hi Caleeco You should already have both an early iteration of the worksheet and a sample .csv file, both emailed to you on 18 July. The worksheet is very much a work-in-progress and the current layout looks somewhat unlike the version emailed to you, but the concept is identical. I noted in the first post on Monday that the value that's read from the .csv file has to be written to 'Summary' column F; in the sample worksheet I emailed to you it's to be written to column G. Let me know if you don't have those files and I'll resend them. I recall we had confusion months ago with me sending things to the wrong WTF address; maybe I did it again! 😩
  20. Caleeco

    Obtaining Data from CSV Files

    Hey OldFella! Thanks for getting back to me. Sounds like an interesting problem to solve! Are you able to share any of your source files? This will enable much easier testing (when importing data, the structure of both source and output data is important to understand properly). If not, don't worry I'll create a dummy setup and let you test. Thanks for the udemy link! I'll give it a read Caleeco
  21. OldFella

    Obtaining Data from CSV Files

    Hi Caleeco I've just come across a web page that shows that accessing a closed .csv file is practicable with VBA. Have a look at https://blog.udemy.com/vba-open-file/ and scroll down to Example 2. I'm sure that will make far more sense to you than it does to me - hope it helps. Or open it as a workbook? 😉
  22. OldFella

    Obtaining Data from CSV Files

    Hi again Caleeco Sorry to be a pain but I've just been simulating the effect of the requested coding and I've found a small issue. There are times when not every symbol in 'Summary' col B will have a corresponding .csv file. I can readily overcome this by increasing the amount of data in each .csv file, but it complicates the coding slightly because instead of reading data from a specific cell in the .csv file it will now need to read the last value in the column. So, in items c) & h) above, please replace "Read the value in the .csv file cell B2" with "Read the last value in col B of the .csv file". That should ensure there is always a .csv file to correspond with each symbol in 'Summary' col B and hence each symbol would have a value in col F, although for safety it might be sensible to include some error trapping to allow for a situation where there is no corresponding .csv file. And of course the first line in d) of the upper block is no longer valid; there will now be multiple cells of data and different numbers of cells in each .csv file. In text mode, it's always the last value in the last line that has to be read. Thanks
  23. OldFella

    Obtaining Data from CSV Files

    Hi Caleeco My sincere apologies for the prolonged delay following up with this. Far, far too many side issues! I've made some slight changes to the requirements, which hopefully have simplified it a bit. Anyway, here's the description: a) In a folder named 'Results' I have a worksheet named 'Summary'; b) Column B contains a list of symbols, usually three alpha characters but the length can be between 1 and 5. Data starts in row 6 and currently extends down to row 23 although the last data row will change from week to week; c) In the 'Results' folder there is a sub-folder named 'Data1' in which there are (currently) 18 .csv files. These files have the same name as each symbol in col B of the 'Summary' sheet. Cell B6 of 'Summary' has the symbol AAD; in the 'Data1' folder there is a .csv file named AAD.csv. Cell B7 of 'Summary' has the symbol BBD; in the 'Data1' folder there is a .csv file named BBD.csv. And so on . . . . d) If I open them with Excel, each .csv file has just 4 cells of data. A1 and B1 are headers, A2 contains a date and B2 contains a numeric value. The .csv files are comma delimited and are created, on demand, using an external process. A typical file (in text mode) is: "Date","Close" "24/09/2018","0.67" The quote marks are optional and are not really needed. That was the easy part. I need VBA coding that I can attach to an "UPDATE" button and that will: a) Read the value in 'Summary' cell B6; b) Open the .csv file (in read only mode) that has the title of the value in B6; c) Read the value in the .csv file cell B2; d) Close the .csv file; e) Write the value to 'Summary' cell F6; f) Read the value in 'Summary' cell B7; g) Open the .csv file (in read only mode) that has the title of the value in B7; h) Read the value in the .csv file cell B2; i) Close the .csv file; j) Write the value to 'Summary' cell F7; k) Read the value in 'Summary' cell B8; l) Open the .csv file (in read only mode) that has the title of the value in B8; and continue looping until it reaches a blank cell in 'Summary' col B, each value in 'Summary' col B has been read and the value in cell B2 of the corresponding .csv file has been read and then written to 'Summary' col F. I've found that I'm unable to achieve the required result using formulae because regardless of how I try, I cannot get the data in 'Summary' to automatically update whenever I generate each new batch of .csv files unless I open each individual .csv file. Hence the need for VBA coding to open and close each .csv file. Hope all that makes sense. I've done away with the need for VLOOKUP, and the date problems have been resolved, so it's down to just needing looping coding that will sequentially copy a single cell of data to the summary sheet. Many thanks
  24. ZiyaSepp

    Use SelectionChange Event to Switch Chart

    oooh I see, very clever! Thanks for this. I love all the new emojis by the way haha 🤪 I'll no doubt be back soon with other questions, i''ve got some other ideas I'd love to implement Z
  25. Caleeco

    Use SelectionChange Event to Switch Chart

    Ah, that's not a VITAL bit of code.. it's just generally good practice when using the SelectionChange Event. It basically prevents the code running if you select more than one cell with your mouse. Eg... if you selected both B3 and B4 at the same time, the code will get confused and not trigger either event! 😵 Glad it worked 😊 Caleeco
  26. ZiyaSepp

    Use SelectionChange Event to Switch Chart

    Hi Caleeco! That works great... only 3 lines of code!? 😵 I thought it would be way more complicated haha Im trying to decipher what you've done.. i get most of it, except this line: If Target.Count > 1 Whats that supposed to do? Z
  1. Load more activity
×