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. 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?
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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
  8. OldFella

    Obtaining Data from CSV Files

    Sure enough - I used the other address - [email protected] I'll resend now. Sleep well!
  9. 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
  10. 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! 😩
  11. 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
  12. 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? 😉
  13. 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
  14. 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
  15. 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
  16. 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
  17. 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
  18. Caleeco

    Use SelectionChange Event to Switch Chart

    Hello Ziya Good to see you again. That's an awesome idea! Certainly something we can use the SelectionChange Event to implement. Try this code out and see if it does what you need. Obviously you'll need to align your charts up first & past the code in the relevant SHEET object and not a VBA module. Private Sub Worksheet_SelectionChange(ByVal Target As Range) '//--- Source: www.ExcelWTF.com '//--- Purpose: Quick Switch between charts If Target.Count > 1 Or Intersect(Target, Range("B3:B4")) Is Nothing Then Exit Sub If Target.Address = "$B$3" Then ActiveSheet.Shapes("Chart 1").ZOrder msoBringToFront If Target.Address = "$B$4" Then ActiveSheet.Shapes("Chart 2").ZOrder msoBringToFront End Sub Hope That Helps Caleeco 😎
  19. Hey Caleeco, I'm upgrading my dashboard with some new charts, however, space is a premium as most people in the office work on small laptop screens! 😶 Would you be able to help me with something i've dreamt up? Basically... i want to stack 2 charts one on top of the other. Then be able to click on Cell B3 to bring the first chart to the front (hiding the other one behind it), or click cell B4 to bring the second chart to the front. So it will look like they're coming out of nowhere 😁 My Chart names are 'Chart 1' and 'Chart 2' if you need them. Can you write some VBA magic? Z
  20. Hey Sapron, Awesome, glad we could finally get it working! No problem, I'm always happy to help where I can. Ok great, I look forward to hearing from you on the near future then Caleeco
  21. Hi Caleeco, thanks for the addition, it works great now. With this addition it will prevent to send the emails twice. If you have 2 persons, no problem, but a team can also be 20 persons, that is why I want to prevent sending double emails. But it works fine now. Finally it's done ! Thank you so much for you time spent in this case. You really helped me a lot !! I wish I could thank you personally, but that's not possible unfortunately. Thank you !! You rock dude!! Ps. I will come back to you later this month with the conditional formatting case... Sapron75
  22. ZiyaSepp

    Want to learn VBA?

    Hey Cal, Just checked out your other side... looks pretty slick I will be browsing! Hopefully I'll be less of a Excel Newbie soon enough Z
  23. Hi Sapron, Good spot! Great to see you got that figured out! I was a bit confused by that code.. The variable TemplateBonusgrAfhank doesnt actually get used anywhere, so i ignored it. TemplName = .Range("D1").Value If TemplName <> .Range("Z" & CustRow).Value And TemplName = .Range("AB" & CustRow).Value Then The line above is doing the following: IF Template Name (Cell D1) Is not found in row Z of each team member AND Template Name (Cell D1) is the same as row AB of each team memeber THEN run the code I didnt see the point of line 1 above. Surely row Z and AB should always match? Either way the line I replaced it with is shown below: If wsAdd.Range("W" & r.Row).Value = arrManagerUnique(i) And wsAdd.Range("AB" & r.Row).Value = wsAdd.Range("D1").Value Then Can you explain what the code isn't doing? Preferably with an example, and I'll see what code edits are needed. If you need my code to match exactly the original requirements, this should work: If wsAdd.Range("W" & r.Row).Value = arrManagerUnique(i) And wsAdd.Range("AB" & r.Row).Value = wsAdd.Range("D1").Value and wsAdd.Range("Z" & r.Row).Value <> wsAdd.Range("D1").Value Then Look forward to hearing from you Caleeco
  24. Hi Caleeco, I found the problem. I needed to check the "Microsoft Word 14.0 Object Library" in "Tools-References". Now it works almost great :-) Almost for the part that I do can run the macro twice although there is a timestamp and "template used" available. So I checked the code and saw that this wasn't in : LastRow = .Range("C999").End(xlUp).Row 'Laatste rij in tabel bepalen For CustRow = 6 To LastRow TemplateBonusgrAfhank = .Range("AB" & CustRow).Value If TemplName <> .Range("Z" & CustRow).Value And TemplName = .Range("AB" & CustRow).Value Then I think this is the missing piece, but I can't integrate it in your code. Can you do this, then the code will be finished finally :-) Thanks again for all your effort and helping with this one ! Hope to hear from you very soon. Sapron
  25. Hi Sapron, That is very strange.. i just tested the code again, with Existing Word docs open... and with no Word docs open.. and the emails generate as expected. Is there an error code that pops-up in the message box? I presume, you're working in the same version of excel... and in the same excel file (not saved as an older version of excel). We may need to switch to late binding. If you run the code i posted two posts ago.. do you get the same error? Let me know Thanks Caleeco
  26. Hi Caleeco, first thing I get is an error I didn't get this morning...strange... It sais that "Dim WordContent As Word.Range" has not been defined. Do you see straight away where the problem lies ? Thanks and then I can test it big time :-)
  1. Load more activity
×