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. Yesterday
  2. Last week
  3. Obtaining Data from CSV Files

    haha! I can neither confirm or deny my involvement in such things! Thanks for the new problem! You can access closed .xlsx files, but this tends to be very slow - I've never tried it on CSV files but dont imagine it's possible. I have some thoughts as to how to go about this....there are several options: Query 1 Open each CSV file in a set folder (will keep opening until there are none left), extract the data you need, close the CSV, move onto the next Setup a data connection to each CSV, so they are imported upon workbook open. Then VLOOKUP should work as you expect on the imported data It would be useful to see the format of the data in the CSV files, is it consistent? are the columns delimited by a special character? are you able to email me a sample file? Query 2 You can use the Format Cells command: Highlight Cells > CTRL + 1 On the 'Number' Tab, Choose 'Date' and pick a format you like However, I presume this is imported data, that excel sees as TEXT instead of a DATE. In which case you need a formula to convert it. So Cell C2 will dispay the 'True' date and can be manipulated as expected. The formula for reference is below: =LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4) Haha, not to worry. I only watch the World Cup for the entertainment.. not that invested into the sport as a whole! Kind Regards Caleeco
  4. Obtaining Data from CSV Files

    Hi Caleeco Nice to see a little bit of new blood on the site, to help keep the grey matter up to scratch for designing eVTOL flying taxis or whatever else they keep you busy with during the day. I have a new problem. I'm trying to import data into an open worksheet, on a daily basis, from a series of unopened .csv files. If the .csv file is opened with Excel, each file may have hundreds of rows but only two columns - a date in col A and a numerical value in col B. The date is currently displayed in yyyymmdd format although of course it's text, not a date. I'm planning to change the display so that it reads ddmmyyyy which, to me, will look a bit more normal, even though without separators. On the open worksheet my thinking was to enter the date of interest, then use VLOOKUP to find that date in the .csv files of interest and return the numerical value in col B. That works fine if the .csv file is open, but not so good when it's closed. So - I have two queries: 1. Since VLOOKUP won't work with a closed file, I need VBA to achieve what I'm after as my understanding is that VBA can access closed files. As I'm now totally out of my depth, could you possibly generate appropriate coding for me? The .csv files are in F:\Trading\ASX\Portfolio. I won't run through the whole list, I'll be able to add to or modify your coding once I see what you've done, but the first few are AJM.csv, CTM.csv, CTMOB.csv and CVS.csv. The destination workbook is Portfolio.xlsm and is in the F:\Trading folder. The destination for the numeric value from the AJM.csv file is cell F7; from CTM.csv is F8; from CTMOB.csv is F9; and so on. Let me know if you need further detail. 2. A date that displays as ddmmyyyy looks horrible without separators. How do I convert that text into either dd.mm.yyyy or dd/mm/yyyyy? And having done that, can the date in that format be used to match up with the date in the .csv files? If they won't match, then maybe I'll need to use a hidden column on the destination worksheet and do it in reverse - convert dd.mm.yyyy (which will display on the worksheet) into ddmmyyyy (which will be in a hidden column). Does that make sense. Many thanks Ye Olde one (and I won't mention that game that was played in Russia recently)
  5. Hi Caleeco, I think this is a tough one to crack no ? I also tried it with an advanced filter before and after the email coding, but I just don't know how to manage it... Hopefully you will :-) Thanks again !
  6. Earlier
  7. Recieved! Thanks for the source files, I will get started on this now.. hopefully I can crack it this evening and report back Caleeco
  8. Hi Caleeco, I just send you the email. Thanks in advance !!! Sapron
  9. Hi Sapron, Thanks for the question! I'd be happy to take a look Yes, It will make it easier if i could have the original file. Please email your file to me at [email protected] Kind Regards Caleeco
  10. Hi, I'm working on a vba project for creating pdf documents automatically with excel and then send it to 1 email recipient. If it is 1 document it's easy, but I want to send multiple pdf documents in the same group to the email address which is available in the excel sheet. I'm sure it is possible but I can't get it right. This is the code so far. With this coding individual emails are sent perfect, but not in group :-( Sub WordDocumentenmaken() Dim CusRow, CustCol, LastRow, TemplRow As Long Dim DocLoc, TagName, TagValue, TemplName, FileName, TemplateBonusgrAfhank As String Dim CurDt, LastAppDt As Date Dim WordDoc, WordApp, OutApp, OutMail As Object Dim WordContent As Word.Range With Blad16 If Range("B3").Value = Empty Then MsgBox "Please select a correct template from the drop down list" .Range("D1").Select Exit Sub End If TemplRow = .Range("B3").Value 'Template rij vaststellen TemplName = .Range("D1").Value 'Template naam vasstellen DocLoc = Blad1.Range("B" & TemplRow).Value 'Word document naam 'Open het word document template On Error Resume Next 'Als Word toevallig al loopt Set WordApp = GetObject("Word.Application") If Err.Number <> 0 Then 'Nieuw Word sessie starten Err.Clear 'Wanneer fout dan foutbehandeling Set WordApp = CreateObject("Word.Application") WordApp.Visible = True 'Maakt Word zichtbaar voor gebruiker End If 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 Set WordDoc = WordApp.Documents.Open(FileName:=DocLoc, ReadOnly:=False) 'Open de template For CustCol = 3 To 20 'Dit zijn alle kolommen met de tag naam TagName = .Cells(5, CustCol).Value 'Je geeft de rijnummer op waarin de tags staan vermeld TagValue = .Cells(CustRow, CustCol).Value ' Tag waarde With WordDoc.Content.Find .Text = TagName .Replacement.Text = TagValue .Wrap = wdFindContinue .Execute Replace:=wdReplaceAll 'Forward:=True, Wrap:=wdFindContinue End With Next CustCol If .Range("G1").Value = "PDF" Then FileName = ThisWorkbook.Path & "\" & .Range("C" & CustRow).Value & " " & .Range("F" & CustRow).Value & " " & .Range("G" & CustRow).Value & " " & .Range("H" & CustRow).Value & " - " & .Range("U" & CustRow).Value & ".pdf" 'creeert file met persnr,voorletter, tussenvoegsel,achternaam en Bonus subgroup WordDoc.ExportAsFixedFormat OutputFileName:=FileName, ExportFormat:=wdExportFormatPDF WordDoc.Close False Else 'Als het in Word moet FileName = ThisWorkbook.Path & "\" & .Range("C" & CustRow).Value & " " & .Range("F" & CustRow).Value & " " & .Range("G" & CustRow).Value & " " & .Range("H" & CustRow).Value & " - " & .Range("U" & CustRow).Value & ".docx" WordDoc.SaveAs FileName End If .Range("Z" & CustRow).Value = TemplName 'Template Name .Range("AA" & CustRow).Value = Now If .Range("G2").Value = "Email" Then Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.Createitem(0) With OutMail .To = Blad16.Range("W" & CustRow).Value .CC = Blad16.Range("X" & CustRow).Value & ";" & Blad16.Range("Y" & CustRow).Value .Subject = "Bonus letter(s) of your team" .Body = "Dear " & Blad16.Range("D" & CustRow).Value & " , attached you will find the bonus letter(s) for your team. Please ensure they receive this letter individually within 1 week after receiving this e-mail." .Attachments.Add FileName .Display 'Als je zonder van tevoren wilt zien dan .Display to.Send End With Else: WordDoc.PrintOut WordDoc.Close End If Kill (FileName) 'Gooit de pdf of word document weg wat je hebt aangemaakt End If Next CustRow WordApp.Quit End With End Sub The column "W" contain the managers of different people. If I run this code, individual emails are being created for the individual people. I need 1 email with the pdf's of every employee with the same templatename and same Manager email address. Can you help me with this one ? If you need the file, please let me know where to send it to. Thanks a lot !!
  11. Create general password protected pdf file

    Hi Caleeco, thank you for your answer. That wouldn't be an option, because the recipient can alter the data afterwards in excel and that's just the issue. For audit purposes the data must be data which can not be altered afterwards. So we will leave it like it is..no password thusfar, thank you for your input though !!
  12. Create general password protected pdf file

    Ah ok, I thought that may have been the case. Can you not instead just send a copy of the Excel sheet which is password protected? You could duplicate the sheet, paste everything as values and then set a password & email it to the recipient. Let me know if this solution would suffice? Caleeco
  13. Create general password protected pdf file

    Hi Caleeco, Thank you for your answer. Unfortunately I am , indeed, working on a restricted network which doesn't allow us to install third party software. I think I have to go to another solution, but not sure what I can do. The main goal is to send something to someone so that no one can read it unless they are supposed to. That's why I thought I could make a password protected pdf ... Do you have any other suggestions ? Thanks Ron
  14. Looks pretty good to me!
  15. Create general password protected pdf file

    Hi Ron, Thanks for the question. Your code looks good so far! In terms of setting a PDF password, you need to have either the Full Adoble Suite (not just the reader) or be able to install some third party PDF creators. I have seen this used a few times: http://www.pdfforge.org/pdfcreator You would setup a default password using the software and then print your file to the PDF creator. More information available here: https://www.exceltrainingvideos.com/tag/how-to-create-password-protected-pdf-file-with-vba/ Let me know if this helps. I can write some modified code for you once you confirm you are able to install software (not sure if you're doing this on a restricted network or not) Caleeco
  16. Hi, I'm busy with converting excel files to pdf and send them out with Outlook. The converting is okay, but I want to add a password protection. The password will be a general one, so it doesn't have to put in manually each time the excel file will be converted. Can you help me with this. This is a part of the vba code : If .Range("F2").Value = "PDF" Then FileName = ThisWorkbook.Path & "\" & .Range("C" & CustRow).Value & " " & .Range("G" & CustRow).Value & ".pdf" 'creeert file met persnr en achternaam WordDoc.ExportAsFixedFormat OutputFileName:=FileName, ExportFormat:=wdExportFormatPDF WordDoc.Close False Else 'Als het in Word moet FileName = ThisWorkbook.Path & "\" & .Range("C" & CustRow).Value & " " & .Range("G" & CustRow).Value & ".docx" WordDoc.SaveAs FileName End If If .Range("J2").Value = "Email" Then Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.Createitem(0) With OutMail .To = Blad16.Range("U" & CustRow).Value .Subject = "Beste " & Blad16.Range("E" & CustRow).Value & "ADV en ziek" .Body = "Beste " & Blad16.Range("E" & CustRow).Value & "Hier de brief mbt adv en ziek" .Attachments.Add FileName .Display 'Als je zonder van tevoren wilt zien dan .Display to.Send End With Else: WordDoc.PrintOut WordDoc.Close End If Kill (FileName) 'Gooit de pdf of word document weg wat je hebt aangemaakt Can you help me with this one ? Thanks Ron
  17. Changing Chart Y-Axis Maximum with VBA

    Thanks so much for this good sir! Worked like a charm I have other improvements to make to my dashboard so I'll pop by again soon!
  18. Changing Chart Y-Axis Maximum with VBA

    Hey Kirabo, I thought that may have been the case! It's always a good idea to rename your charts however, as this makes things easier when modifying them via VBA. You can make use of the CEILING function to round up to the nearest 10, and I have modified the code slightly to just look at the entire columns of B:C. Sub ChangeAxisScales_1Line() '// Source: www.ExcelWTF.com '// Purpose: Set Max Y-Axis using MAX of 2 x datasets Dim Max1 As Long, Max2 As Long Dim ws As Worksheet Set ws = Sheets("Utilization") With ws Max1 = WorksheetFunction.Max(.Range("B:B")) Max2 = WorksheetFunction.Max(.Range("C:C")) If Max1 > Max2 Then ' Change the chart names below! .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max1, 10) .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max1, 10) Else .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max2, 10) .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max2, 10) End If End With End Sub Give it a try and let me know how you get on Thanks Caleeco
  19. Changing Chart Y-Axis Maximum with VBA

    haha turns out my charts ARE called Chart1 & Chart2 anyway That's cool, seems to work great! However, is it possible to change it so that the axis find the biggest number in my dataset and then rounds up to the nearest 10? Also how can i change it so that columns B:C can be dynamically filled with more data and I wont have to keep editing the code?
  20. Changing Chart Y-Axis Maximum with VBA

    Hey Kirabo, If you are using Excel 2013 or greater, you can just click on the chart and then the Chart Name will be displayed in the Top Left of the screen (Next to the formula bar). You can then edit the code below to suit, in my example i have called them 'Chart1' and 'Chart2'. Sub ChangeAxisScales_1Line() '// Source: www.ExcelWTF.com '// Purpose: Set Max Y-Axis using MAX of 2 x datasets Dim Max1 As Long, Max2 As Long Dim ws As Worksheet Set ws = Sheets("Utilization") With ws Max1 = WorksheetFunction.Max(.Range("B2:B8")) Max2 = WorksheetFunction.Max(.Range("C2:C15")) If Max1 > Max2 Then ' Change the chart names below! .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = Max1 .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = Max1 Else .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = Max2 .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = Max2 End If End With End Sub Let me know how you get on Thanks Caleeco
  21. Changing Chart Y-Axis Maximum with VBA

    Hey Where do I find out the name of the chart? is it the title? - My two datasets are in columns B2:B8 & C2:C15. - My sheet name is "Utilization" Thanks for your help!
  22. Changing Chart Y-Axis Maximum with VBA

    Hi Kirabo, As far as I know, this is not possible using the Excel interface. I believe Microsoft are looking to introduce equation controlled axis' in a future release of Excel, but for now we have VBA Can you let me know the following: Your sheet name The names of the 2 charts and where the data for each charts if (Cell references eg. C1:C20) Cheers Caleeco
  23. Hey, I have 2 charts on my sheet (one bar & one line), and I need to make sure their MAX values on the Y-Axis are the same. They are using different datasets, so I need the highest Y-Axis to apply to both so that I can overlay the charts over one another on the sheet. I can't find a way to do this with the GUI, is this something VBA can do? TIA Kirabo
  24. I've Messed Up =LOOKUP

    Awesome, thanks for letting me know that it worked! Haha, no problem.. don't mind doing quick fire questions too, helps me sharpen my skill set All the best Caleeco Marked As Solved!
  25. I've Messed Up =LOOKUP

    Hi Caleeco For a "stab in the dark", that was pretty well aimed! As expected, it works perfectly - but I've learned by now to expect nothing less Thank you so much for that, now the sheet is performing as normal again. I'll try to come up with something that taxes your brain a bit better next time. Cheers
  26. I've Messed Up =LOOKUP

    Hey OldFella, Good to see you, hope yo've been keeping well! Yes, I haven't really been promoting the forum in recent months, my 'real' job is taking up most of my time these days. Always happy to answer Excel questions though, so thanks for posting. Without seeing your sheet, this is a bit of a stab in the dark, but we could make use of Mathmatical operators! Try using this formula instead: =LOOKUP(2,1/(S:S<>""),S:S) Let me know how you get on Cheers Caleeco
  27. I've Messed Up =LOOKUP

    Hi Caleeco Looks like you've been having it pretty easy with me not pestering you with my problems. However, I have a new problem. Still on the same spreadsheet as previous, but a little enhancement has stopped working. I've been using =LOOKUP(2,1/(1-ISBLANK(S:S)),S:S) to find the last value in a column that consists of blanks and manually entered numbers. It worked fine until I decided to use a formula to automatically enter new values in the column instead of me entering them manually. There are now about a hundred blank cells in the lower section of the column, each with an underlying formula. Now my =LOOKUP cell is blank; presumably it's showing the value of the last formula in the column - a blank. How do I resolve this? I searched the 'net and tried an assortment of similar formulae, but nothing worked. Now I need proper help. As always, I'm open to using a formula or VBA, whichever is the more efficient. Many thanks.
  28. Slow Running Code

    Hey, That's excellent news! Glad it is now working. Haha, yes that's the trouble with coding. Seemingly insignificant things can have big consequences! Ah ok yes, sounds like it would require a lot of modification. Almost better to wait until the next gen of tech is out and build a shiny new system No problem, glad I could help. Like always, stop by the forum if you have any other Excel/VBA problems you'd like me to take a look at Cheers Caleeco MARKED AS SOLVED
  1. Load more activity
×