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.
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
@@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, 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 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.
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.
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 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, 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.
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
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.
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.
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 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
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
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.
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.
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
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
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.
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
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 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 :)
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 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 ?
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!!! 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
Grab the file I used in the video from here 👉 pages.xelplus.com/vba-dir
Your tutorials are TOP quality!! Thank you! 😊
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.
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.
Great tutorial! Thanks!
Thanks so much. This video was exactly what I need for my project. You are a life saver
It is just great!
You are for sure the best!
Your didactic is just perfect!
Thank you! 😃
Any solution for URL path-and-filenames on OneDrive?
Thanks for another VBA video!
As always such a good logical explanation
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.
Hi Leila, can you help me telling if is there any method to take a print out of a PDF file by VBA
Amazing Presentation! It was very helpful to me. Thank you Leila
You're very welcome!
doesn't work on a Mac. any updates for browsing/selecting folder for modern Macs?
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 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 :)
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
Thank you so much Leila.
Please help me
How to delete the file that i did it in the folder two days ago
Hi Leila, Your videos' are very helpful. Thank you so much! :-) God bless, David
You are so welcome, David!
For file path, is it possible to search in a sharepoint instead of local drive?
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!
I love your tutorials. Keep it up.. Thx
Thank you! Will keep going :) Appreciate your support.
how to you write? \\server\trackers$ - trackers folder? in vba how do you declare it?
i needed it..
Thank you
Would anyone know how to use this exact functionality with sharepoint files? If exists exit sub else save file end if, for example.
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.
Thank you Leila! :)
You're very welcome Malina :)
I get Exit Sub not allowed in function or property, it breaks on Exit sub line.
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)
How do I check if the file exists if it is on a SharePoint URL?
Does it work with files on a server or OneDrive?
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.
Thanks Laila for this DIR function VBA
You're very welcome Ashis.
Thanks from Bangladesh
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.
How to search multiple path?
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 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
My filename is in korean language and i cannot open the workbook since it returns ?????. Help please
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,
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.
Could you please upload sql database connection videos.. it will help us to learn more on database connections
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
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.
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.
great explanation
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 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
i could you some help with a vba code to repeat a macro on all the sheets in my workbook
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
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.
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.
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.
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
Great, Thank You.
Great tutorials thank you,
Could u do tutorial on transfer specific files from one folder to another folder using vba...
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
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.....
Please, would you consider a lesson on how to interact between Excel, Access and SharePoint.
Thank you so mutch.
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
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
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
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: ua-cam.com/video/2HXigXrTE9I/v-deo.html
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.
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 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 :)
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 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 ?
Excel is pretty :)
Thank you mam...
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: ua-cam.com/video/fDB1Ktyhp3Y/v-deo.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
Showing path not found
Leila always coming through
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
Hai mam how ru? Mam i want one video how to use excel in logistics plz
Thanks a lot....
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
Thank you
You're very welcome.
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
Thanks
You're very welcome Saiful. Thanks for watching :)
thanks..
OUCH! My brain is hurting 🤪
Type mismatch
Hello,