Thank you Madam. I am learning many things from you. I pray the almighty to bestow good health & Prosperity to you, as you are doing a great & noble job for the benefit of Excel lovers/users.
@@LeilaGharani Hi, this video is for one perticular file in folder, how can we check if there are multiple .pdf files in particular folder. If .pdf file exist it should return the value true else false, Need your support leila, thanx
Hi Leila. I really like your videos and they're super easy to follow but I'm having problem in Vba and macros tutorial. Please elaborate more in your videos.
Thanks for clear video, here I have a question, i.e., if folder already exists and user is willing to overwrite the current folder. Could u explain how to proceed plz.
Mam really your tutorial is to good Mam now my question is. I am using a network excel macro enable file but when I open it and start work and same time my partner also try to open that file .can teach me how I make a make macro to restrictions it reopen please mam
Dear Leila I would like to know how to create a new folder, using the same name, but add a number in front of this folder already created, this method is like windows does, New Folder, New Folder (2). Oh and thanks for you explanation about this method, it's help me a lot.
Thank you so much for your tutorial videos, need your help to get a VBA code to copy and paste a data from specific folder in one Excel sheet and files names are as per worksheet tab name.
Hi Leila, rather than entering that full file path, can we auto return to the active workbook path? as directory path may vary please resolve. I'm trying to use Workbooks.Open "ActiveWorkbook.Path" & FileName or ThisWorkbook.Path instead of the file path. but doesn't work!
Hi, Thank you for this informative video. Request you to please share information on Microsoft ActiveX library...which version should we use, which version is capatible with different excel versions. Thank you Abhinav
Hi I also need very similar help as the previous one how to display Available & Not in available in the column if file exists in folder it should print in the column available if not it should print Not available in the column
Hello, Basis this tutorial, I created the folders. Now i have multiple files and I want to copy these files in different folders basis the excel where I have mentioned the folder name and next to it have mentioned the file names. Need a Macro which can read the folder name mentioned in excel column and copy those respective files in that respective folder.
Can you make a video of VBA Code for Button -> Make a Pic of the sheet -> open Mail -> paste and write the correct mail adress please. I have a KPI Overview Sheet for my Employees and want to send each of them there KPIs. #ideas #curses #recommend #questions #onenote
Hello Leila, Is it possible to take file name from cell? Lets say I got folder with 52 excel files named 1-52 each one represent a week in the year and I would like to use macro to always open file for current week.
Hi,thanks for the good vba explanation, and i want to move on some files one floder to another with of its file name ex- some of the file start with "aaa" it will move to the "A" floder can help me
Leila Gharani, I have a question: Why is it that you use the A1 style of notation instead of the R1C1 notation? It is far more convenient, especially when using VBA, to refer to NUMERIC row and column. The following example from VBA code shows how much more convenient Row/Column reference is: SourceRow = ActiveCell.Row Tenure = Cells(SourceRow, 2).Text NameLast = Cells(SourceRow, 3).Text NameFirst = Cells(SourceRow, 4).Text ActiveWindow.ActivatePrevious Cells(DestinRow, 1).Value = Tenure Cells(DestinRow, 5).Value = NameFirst Cells(DestinRow, 7).Value = NameLast This is especially convenient when dealing with worksheets with more than 26 columns. Yes, I realize that this can also be done with A1 notation, but not as cleanly.
@@LeilaGharani I use ONLY R1C1 style so that I don't make mistakes. I see ZERO value in the A1 style. I don't want to have to remember which letter of the alphabet Q is.
Hi, this video is for one perticular file in folder, how can we check if there are multiple .pdf files in particular folder. If .pdf file exist it should return the value true or highlight that column where the file path is given. Need your support leila, thanx
I have thousands of pdf files with reference like xxxx-yyyy-zzzz each part denotes some structure. Is there any vba method available to move the files in to folders based on references structure using match function ? A:A - with file names B:B - corresponding folders to move individual files
Hi Leila.. thanks for this video on DIR and examples of how to use it.. very helpful. I noticed that your code indents are 2 characters vs. the default of 4. Is that your normal preference or do you do that just for the sake of the video presentation? I have always used the default of 4 spaces, but I see lots of debate on the Internet about 2, 3 or 4 spaces. Any comments on what you recommend for students learning VBA today? Thanks again and Thumbs up!
Thank you for your comment Wayne. My indent on the Surface (where I film) is 4 characters, but you're right, it actually looks like 2. When I open the same file on my other computer, it looks much more indented. I've always stuck to 4. It's worked well for me :)
Hi, I am facing trouble to check the existing file in a specific folder. Because I am creating the file name automatically with a specific cell reference. Could you please help to get the VBA code to check if the file exists? Here I am showing my file path: [[ActiveSheet.Range("A1:O102").ExportAsFixedFormat xlTypePDF, FileName:="C:\Levi's Recipe\" & ActiveSheet.Range("P1").Value, openafterpublish:=True]] Thanks in advance.
Great video as usual I have question not a VBA related!😉 What function can I use to make lookup for multiple items? Such as an example I have many student results I want to view the students who has the same results in one cell separated with “,” Is that possible? I thought of aggregate function with combination with others functions!!
Leila Gharani Thank you 👍🏻 In my excel 2016 there is no built in TEXTJOIN , so I used an array code for TEXTJOIN from this website codereview.stackexchange.com/questions/180332/test-for-array-vs-range-in-my-textjoin-udf It worked greatly using your function
Great video but one question. Why did you say that you were not smart in the other video when I told you that you are. Don't you have a Master's degree? I graduated from high school 30 years ago but barely. I struggled in school now I am 48. Are you kidding me? You are brilliant. Having said that about myself, now who isn't smart, you or me. Don't say you are not smart.
Thank you for the kind words. Yes - I have a master's degree - but honestly I don't think that makes me smart. I knew how to study and pass exams, but that doesn't mean I remember anything I learnt :) I think everyone is good at something. In my case, it might be explaining formulas or VBA code - in your case it might be something else - but then again, you're watching Excel videos in your free time! Not many people do that. The education system has a way of branding us with our grades and schools but the most important things I learnt in my life that impacted my career was actually outside the education system. So let's say, depending on the topic, we're all smart in our own ways...
Hi I tried to use this to check a sharepoint directory to see if the file is located there. Its giving me a Run Time Error '52' Bad file name or number. Do you have any suggestions? I've been using your videos to catch up on a new job so any help would be appreciated
hello, @leila wonderful videos. Always check your videos if i get stuck. Can i ask a question, maybe a topic for a subsequent video. If my data set is from A1-B4 (A1-apple,B1 banana, C1-Oranges, D1 Mangoes) and B1-B4 is quantities. i need to create a visual dynamic basket where if a person enters 4 units of apples - 4 cells show up A, then say 3 Banana subsequent 3 cell are B, and next 0 oranges then nothing in the basket and say 5 mangoes then 5 cell after Banana(B) shows M. is such a thing even possible.
Thank you Gaurav. Regarding your query, the cells that show up as A, B are they below the headers? So you have B1 - Banana, B2 - Quantity - let's say 2, then you'd like B3-B4 to show a B? Do I understand it properly? If yes, you can type this formula in B3: =IF(ROWS($B$3:B3)
@@LeilaGharani thanks for your revert. To clarify, i wrote incorrectly, data set is from A1-B4 (A1-apple,A2 banana, A3-Oranges, A4 Mangoes) and B1-B4 is quantities between (1 to 10). My basket is in cell D1 to G10. Now the fun part is if B1=5; B2=6; B3=2; B4=8 (that is 5 units of apples, 6 0f Banana, 2 of Oranges; 8 of Mangoes) then say D1-D5 will have have letter A; D6-D10 & E1 will have letter B; E2 toE3 will have letter O; E4 to E10 & F1-2 will have letter M. So, can we have it filled dynamically so that if value in units (B1 to B4) changes than the letter in the basket will also change ?
I want to activate the workbook on error the code is to open the respective workbook from the path. I have tried with the below using on error goto label name. On error goto nxt WORKBOOKS("filename").activate 'Step 1 Nxt: Workbooks.open filename:=.....'step 2 On this code macro completed the step 1 and moving to step 2 please help
How can you check whether the file exists without having to write the full path "C:\users\LG...". let's say you're checking whether a file in the same directory as the excel sheet you're writing the macro on...or rather, how do you get the directory of the current spreadsheet? please answer soon, my assignment depends on it
Ok, here is the situation most of the time I convert my work to pdf using pdf pro. And I use multi sheets workbook. But what bothering me is that I always have to write the sheet's name manually. Is there away to force the excel to print my sheet with its name not the workbook name.
You can define the file name to be a specific sheet name or you can have the names you want inside cells and the macro can use these. There is a good blog post here that might help you: www.contextures.com/excelvbapdf.html
Sub addWorksheet() Dim wbk as workbook Dim wsht as worksheet set wbk = activeworkbook 'or Workbooks("someOtherWorkbook") set wsht = wbk.Sheets.Add End sub That's it in its simplest form. You copy this into a module (in visual basic editor, select Insert/Module). You can assign to a button or whatever. The .Sheets.Add statement will allow you to position the sheet, or enter multiple sheets: set wsht = wbk.Sheets.Add After:=Sheets("test"), Count:=3 'adds 3 new sheets directly after a sheet called test. Hope this helps.
Great!!! Leila, please review the function below. Sub arquivo_existe() Dim NomeArquivo As String NomeArquivo = VBA.FileSystem.Dir("C:\Users\Paulo Candido\Downloads\ResultadoLotoFacilCaixa\D_lotman.htm") If NomeArquivo = VBA.Constants.vbNullString Then MsgBox "Arquivo não existe." Else MsgBox NomeArquivo '< Leila, I ask you: What function do I use to save the file "D_lotman.htm" as "D_lotman.mhtml? > End If End Sub
![Blox Fruits Dragon Rework Update [Full Stream]](http://i.ytimg.com/vi/EqDAp8udhm0/mqdefault.jpg)
Grab the file I used in the video from here 👉 pages.xelplus.com/vba-dir
Leila Gharani
i have become a Great Fan of you,the way you explain the concepts,tips and communication . ...Excellent
I'm glad to hear that my tutorials are helpful for you.
Your tutorials are TOP quality!! Thank you! 😊
Excellent. Taking lot of trouble to make the things very easy for Excel/VBA learners. Thanks a lot to my beloved/beautiful/brainy EXCEL TEACHER.
Thank you Madam. I am learning many things from you. I pray the almighty to bestow good health & Prosperity to you, as you are doing a great & noble job for the benefit of Excel lovers/users.
Hello Leila. Very well explained and useful in future projects using VBA. I am a fan of your channel. Thanks for sharing this content.
Thank you Luis! I'm glad you find the content useful :)
Thanks so much. This video was exactly what I need for my project. You are a life saver
Amazing Presentation! It was very helpful to me. Thank you Leila
You're very welcome!
It is just great!
You are for sure the best!
Your didactic is just perfect!
Thank you! 😃
Great tutorial! Thanks!
Hi Leila, Your videos' are very helpful. Thank you so much! :-) God bless, David
You are so welcome, David!
Thank you so much Leila.
Once again you saved my life, it works like charm !
Keep it up with the great work
I'm glad to hear that!
@@LeilaGharani Hi, this video is for one perticular file in folder, how can we check if there are multiple .pdf files in particular folder. If .pdf file exist it should return the value true else false, Need your support leila, thanx
i needed it..
Thank you
Thanks for another VBA video!
Hi Leila. I really like your videos and they're super easy to follow but I'm having problem in Vba and macros tutorial. Please elaborate more in your videos.
As always such a good logical explanation
I love your tutorials. Keep it up.. Thx
Thank you! Will keep going :) Appreciate your support.
Thank you so much Leila.
Please help me
How to delete the file that i did it in the folder two days ago
Any solution for URL path-and-filenames on OneDrive?
Thanks Laila for this DIR function VBA
You're very welcome Ashis.
Thank you Leila! :)
You're very welcome Malina :)
Thanks from Bangladesh
Hi Leila, can you help me telling if is there any method to take a print out of a PDF file by VBA
Great, Thank You.
Thanks for clear video, here I have a question, i.e., if folder already exists and user is willing to overwrite the current folder. Could u explain how to proceed plz.
Could you please upload sql database connection videos.. it will help us to learn more on database connections
great explanation
Hello your video on DIR function is very helpful. Can you help me that can we open a folder and search with a word by VBA code
Mam really your tutorial is to good
Mam now my question is. I am using a network excel macro enable file but when I open it and start work and same time my partner also try to open that file .can teach me how I make a make macro to restrictions it reopen please mam
For file path, is it possible to search in a sharepoint instead of local drive?
Dear Leila I would like to know how to create a new folder, using the same name, but add a number in front of this folder already created, this method is like windows does, New Folder, New Folder (2).
Oh and thanks for you explanation about this method, it's help me a lot.
Thank you so much for your tutorial videos, need your help to get a VBA code to copy and paste a data from specific folder in one Excel sheet and files names are as per worksheet tab name.
Hi Leila, rather than entering that full file path, can we auto return to the active workbook path? as directory path may vary
please resolve. I'm trying to use Workbooks.Open "ActiveWorkbook.Path" & FileName or ThisWorkbook.Path instead of the file path. but doesn't work!
Thank you so mutch.
Hi,
Thank you for this informative video.
Request you to please share information on Microsoft ActiveX library...which version should we use, which version is capatible with different excel versions.
Thank you
Abhinav
Request : Hi Leila, please you create a video where show us how VBA code can convert excel to pdf and email it as attachment. thanks
Great tutorials thank you,
Could u do tutorial on transfer specific files from one folder to another folder using vba...
Hi I also need very similar help as the previous one how to display Available & Not in available in the column if file exists in folder it should print in the column available if not it should print Not available in the column
I have created a flow to find onedrive files but what condition should I use if respone to email me that file does not exist.
Hello,
Basis this tutorial, I created the folders. Now i have multiple files and I want to copy these files in different folders basis the excel where I have mentioned the folder name and next to it have mentioned the file names. Need a Macro which can read the folder name mentioned in excel column and copy those respective files in that respective folder.
can you tell me specific file open specific folder only please (if that file cope then past desktop or ex...will not open or destroy self)
Can you make a video of VBA Code for Button -> Make a Pic of the sheet -> open Mail -> paste and write the correct mail adress please. I have a KPI Overview Sheet for my Employees and want to send each of them there KPIs. #ideas #curses #recommend #questions #onenote
Hello Leila, Is it possible to take file name from cell? Lets say I got folder with 52 excel files named 1-52 each one represent a week in the year and I would like to use macro to always open file for current week.
Hi,thanks for the good vba explanation, and i want to move on some files one floder to another with of its file name ex- some of the file start with "aaa" it will move to the "A" floder
can help me
Please, would you consider a lesson on how to interact between Excel, Access and SharePoint.
Would anyone know how to use this exact functionality with sharepoint files? If exists exit sub else save file end if, for example.
doesn't work on a Mac. any updates for browsing/selecting folder for modern Macs?
Leila Gharani, I have a question:
Why is it that you use the A1 style of notation instead of the R1C1 notation? It is far more convenient, especially when using VBA, to refer to NUMERIC row and column. The following example from VBA code shows how much more convenient Row/Column reference is:
SourceRow = ActiveCell.Row
Tenure = Cells(SourceRow, 2).Text
NameLast = Cells(SourceRow, 3).Text
NameFirst = Cells(SourceRow, 4).Text
ActiveWindow.ActivatePrevious
Cells(DestinRow, 1).Value = Tenure
Cells(DestinRow, 5).Value = NameFirst
Cells(DestinRow, 7).Value = NameLast
This is especially convenient when dealing with worksheets with more than 26 columns. Yes, I realize that this can also be done with A1 notation, but not as cleanly.
For me it depends on the task at hand. If I'm looping through columns I go R1C1 style, other times I generally prefer A1 style.
@@LeilaGharani I use ONLY R1C1 style so that I don't make mistakes. I see ZERO value in the A1 style. I don't want to have to remember which letter of the alphabet Q is.
Leila always coming through
How do I check if the file exists if it is on a SharePoint URL?
Hi, this video is for one perticular file in folder, how can we check if there are multiple .pdf files in particular folder. If .pdf file exist it should return the value true or highlight that column where the file path is given. Need your support leila, thanx
Does it work with files on a server or OneDrive?
how to you write? \\server\trackers$ - trackers folder? in vba how do you declare it?
I have thousands of pdf files with reference like xxxx-yyyy-zzzz each part denotes some structure.
Is there any vba method available to move the files in to folders based on references structure using match function ?
A:A - with file names
B:B - corresponding folders to move individual files
Hi Leila.. thanks for this video on DIR and examples of how to use it.. very helpful. I noticed that your code indents are 2 characters vs. the default of 4. Is that your normal preference or do you do that just for the sake of the video presentation? I have always used the default of 4 spaces, but I see lots of debate on the Internet about 2, 3 or 4 spaces. Any comments on what you recommend for students learning VBA today? Thanks again and Thumbs up!
Thank you for your comment Wayne. My indent on the Surface (where I film) is 4 characters, but you're right, it actually looks like 2. When I open the same file on my other computer, it looks much more indented. I've always stuck to 4. It's worked well for me :)
Thank you mam...
Hi, I am facing trouble to check the existing file in a specific folder. Because I am creating the file name automatically with a specific cell reference. Could you please help to get the VBA code to check if the file exists? Here I am showing my file path: [[ActiveSheet.Range("A1:O102").ExportAsFixedFormat xlTypePDF, FileName:="C:\Levi's Recipe\" & ActiveSheet.Range("P1").Value, openafterpublish:=True]] Thanks in advance.
Great video as usual
I have question not a VBA related!😉
What function can I use to make lookup for multiple items?
Such as an example I have many student results I want to view the students who has the same results in one cell separated with “,”
Is that possible?
I thought of aggregate function with combination with others functions!!
This video has the answer: ruclips.net/video/fDB1Ktyhp3Y/видео.html to get them together in one cell you can use the 2nd formula shown there.
Leila Gharani
Thank you 👍🏻
In my excel 2016 there is no built in TEXTJOIN , so I used an array code for TEXTJOIN from this website codereview.stackexchange.com/questions/180332/test-for-array-vs-range-in-my-textjoin-udf
It worked greatly using your function
Great video but one question. Why did you say that you were not smart in the other video when I told you that you are. Don't you have a Master's degree? I graduated from high school 30 years ago but barely. I struggled in school now I am 48. Are you kidding me? You are brilliant. Having said that about myself, now who isn't smart, you or me. Don't say you are not smart.
Thank you for the kind words. Yes - I have a master's degree - but honestly I don't think that makes me smart. I knew how to study and pass exams, but that doesn't mean I remember anything I learnt :) I think everyone is good at something. In my case, it might be explaining formulas or VBA code - in your case it might be something else - but then again, you're watching Excel videos in your free time! Not many people do that. The education system has a way of branding us with our grades and schools but the most important things I learnt in my life that impacted my career was actually outside the education system. So let's say, depending on the topic, we're all smart in our own ways...
i could you some help with a vba code to repeat a macro on all the sheets in my workbook
Hi I tried to use this to check a sharepoint directory to see if the file is located there. Its giving me a Run Time Error '52' Bad file name or number. Do you have any suggestions?
I've been using your videos to catch up on a new job so any help would be appreciated
I have the same problem
How to search multiple path?
Hi..
I'm new to VBA, have a doubt and would like to clarify...how to check if a word file opened before opening in vba.
I get Exit Sub not allowed in function or property, it breaks on Exit sub line.
Excel is pretty :)
hello, @leila wonderful videos. Always check your videos if i get stuck. Can i ask a question, maybe a topic for a subsequent video. If my data set is from A1-B4 (A1-apple,B1 banana, C1-Oranges, D1 Mangoes) and B1-B4 is quantities. i need to create a visual dynamic basket where if a person enters 4 units of apples - 4 cells show up A, then say 3 Banana subsequent 3 cell are B, and next 0 oranges then nothing in the basket and say 5 mangoes then 5 cell after Banana(B) shows M. is such a thing even possible.
Thank you Gaurav. Regarding your query, the cells that show up as A, B are they below the headers? So you have B1 - Banana, B2 - Quantity - let's say 2, then you'd like B3-B4 to show a B? Do I understand it properly? If yes, you can type this formula in B3: =IF(ROWS($B$3:B3)
@@LeilaGharani thanks for your revert. To clarify, i wrote incorrectly, data set is from A1-B4 (A1-apple,A2 banana, A3-Oranges, A4 Mangoes) and B1-B4 is quantities between (1 to 10). My basket is in cell D1 to G10. Now the fun part is if B1=5; B2=6; B3=2; B4=8 (that is 5 units of apples, 6 0f Banana, 2 of Oranges; 8 of Mangoes) then say D1-D5 will have have letter A; D6-D10 & E1 will have letter B; E2 toE3 will have letter O; E4 to E10 & F1-2 will have letter M. So, can we have it filled dynamically so that if value in units (B1 to B4) changes than the letter in the basket will also change ?
I want to activate the workbook on error the code is to open the respective workbook from the path. I have tried with the below using on error goto label name.
On error goto nxt
WORKBOOKS("filename").activate 'Step 1
Nxt:
Workbooks.open filename:=.....'step 2
On this code macro completed the step 1 and moving to step 2 please help
How can you check whether the file exists without having to write the full path "C:\users\LG...". let's say you're checking whether a file in the same directory as the excel sheet you're writing the macro on...or rather, how do you get the directory of the current spreadsheet?
please answer soon, my assignment depends on it
hello Leila Gharani. I want to ask you, can we create bending moment diagram in excel. (For Civil Engineering Major)
I have no idea! Is that similar to the step chart here: ruclips.net/video/2HXigXrTE9I/видео.html
is there a way i can enter a range of values within one cel at the same time put it to graph say 5-20%?????
not sure about this.....
Ok, here is the situation most of the time I convert my work to pdf using pdf pro.
And I use multi sheets workbook.
But what bothering me is that I always have to write the sheet's name manually.
Is there away to force the excel to print my sheet with its name not the workbook name.
You can define the file name to be a specific sheet name or you can have the names you want inside cells and the macro can use these. There is a good blog post here that might help you: www.contextures.com/excelvbapdf.html
@@LeilaGharani Thanks Miss Leila that's exactly what I'm looking for.
My filename is in korean language and i cannot open the workbook since it returns ?????. Help please
Can u please tell me how to add worksheets using vba?
Sub addWorksheet()
Dim wbk as workbook
Dim wsht as worksheet
set wbk = activeworkbook 'or Workbooks("someOtherWorkbook")
set wsht = wbk.Sheets.Add
End sub
That's it in its simplest form. You copy this into a module (in visual basic editor, select Insert/Module). You can assign to a button or whatever. The .Sheets.Add statement will allow you to position the sheet, or enter multiple sheets:
set wsht = wbk.Sheets.Add After:=Sheets("test"), Count:=3 'adds 3 new sheets directly after a sheet called test.
Hope this helps.
@@ricos1497 thnaks
Thanks a lot....
Thank you
You're very welcome.
What can I do if changes path file name??
You can keep the path name in a cell or use GetOpenFileName to have the user pick the file using the open dialog box.
OUCH! My brain is hurting 🤪
Hai mam how ru? Mam i want one video how to use excel in logistics plz
That was pretty neat, however, It would have been more useful if you had explained how to overwrite an existing folder without a prompt.
did you ever figure this out?
@@mbj920 Yes, I did.
thank u
Showing path not found
Thnx leila ,bt i want make a folder with name any date -yyyy.mm..and save my file in to folder with name -DD-MM-YY
Great!!! Leila, please review the function below.
Sub arquivo_existe()
Dim NomeArquivo As String
NomeArquivo = VBA.FileSystem.Dir("C:\Users\Paulo Candido\Downloads\ResultadoLotoFacilCaixa\D_lotman.htm")
If NomeArquivo = VBA.Constants.vbNullString Then
MsgBox "Arquivo não existe."
Else
MsgBox NomeArquivo
'< Leila, I ask you: What function do I use to save the file "D_lotman.htm" as "D_lotman.mhtml? >
End If
End Sub
Thanks
You're very welcome Saiful. Thanks for watching :)
thanks..
Type mismatch
Hello,