Excel VBA Introduction Part 7 - Workbooks

Поделиться
HTML-код
  • Опубликовано: 29 окт 2024

Комментарии • 92

  • @jscrivens9426
    @jscrivens9426 3 года назад +2

    Simply the best VBA video's I've found in using VBA over the last 20 years.

  • @maurocastagnera8949
    @maurocastagnera8949 7 лет назад +2

    Your videos should be translated in all the languages ​​of the world and be used in all computer programming schools. You are simply the best! Thank you very much, God bless you.

  • @johassan6509
    @johassan6509 4 года назад +5

    This seems to be one of the shortest videos in this series so far, yet I have spent the most amount of time on it.I have found this so helpful in my worklife. I am going through all of these videos, just so I can understand arrays, but was so impressed by watching video 22 or 20something, I decided to start from the beginning so I could refresh everything I have learned on the fly over my years. I said in another post I had watched many other tutorials, some were very good, some were to boring for me to watch, but this guy, he is incredible. I hope I get a full understanding of what I need to know so I can do what I need to do.

    • @WiseOwlTutorials
      @WiseOwlTutorials  4 года назад

      Thank you for the kind comments Jo! Very happy to hear that you've found the videos so useful and thank you for taking the time to let us know!

  • @wasimdingankar3599
    @wasimdingankar3599 9 лет назад +14

    your video is a completely Bible of Excel VBA
    - Thanks for the good and noble work

  • @W3DRM
    @W3DRM 9 лет назад

    I'm new to Excel VBA and macros, in general. Your video tutorials are excellent and have answered many of my questions as I learn this subject. I am most interested in User Forms and how they are put together. I have watched your Userform videos but am forcing myself to watch everything from Part 1 to the end so I don't miss any of the pertinent details of Excel VBA. Thanks so much for providing these videos!

  • @jezreeltan8108
    @jezreeltan8108 7 лет назад

    Wow you have the greatest tutorial ever. I been writing VBA for a quite of time but after watching these videos I learned so much even from the basics. Thank you so much.

  • @henrikijonkoping4694
    @henrikijonkoping4694 2 года назад +1

    Great!, your site is always a way to learn something new

  • @Tat-cx1lr
    @Tat-cx1lr 7 лет назад

    Bonjour je suis entrain de revoir toutes vos vidéos. Je comprends beaucoup mieux. Merci et BRAVO encore. You are the best.

  • @nicholas4492
    @nicholas4492 8 лет назад +2

    u r a great teacher!! love the way you teach, now i can uderstand VBA quiteeasily! thank you so much, will keep wathcing these series.

  • @peterhontaru5537
    @peterhontaru5537 6 лет назад +1

    Thank you so much for all the effort you've put into teaching us VBA, for free. Genuinely appreciate it and one of the most useful things I've found on youtube to date

  • @davida.taylor8444
    @davida.taylor8444 Год назад +1

    I've been re-watching these after doing so a few years ago and still learning new content. I had no idea you could save as with an optional FileType argument that lets you save as one of the dozens of file types - or that you even needed to do so for macro-enabled wkbks. Nor did I know that I could hit F1 on a method to open a help menu. There is no end to learning in Excel.

    • @WiseOwlTutorials
      @WiseOwlTutorials  Год назад

      Thanks David, happy to hear that you're still finding value in the videos!

  • @tristanquintus3200
    @tristanquintus3200 10 лет назад

    Thanks WiseOwl for another interesting Excel vba lesson. I will try to get through all your Excel vba lessons since I use Excel frequently at work.

  • @jamievoeckler516
    @jamievoeckler516 10 лет назад

    Your videos are an awesome resource and these VBA tutorials have made my work life much better! Carry on good sir!!!

  • @EOO-Stand
    @EOO-Stand 7 лет назад

    Great tutorials for slow learners like myself. well put, very detailed and the pace is perfect.

  • @colina1793
    @colina1793 9 лет назад

    Thanks again for the video. Good to see how to open, save and save as. Fairly straight forward in Excel itself but a whole different matter in VBA - especially the xlsm filetype!

  • @siggig2463
    @siggig2463 7 лет назад

    Your videos are the best available training videos for VBA. Thank you so much for that, i've learned a lot from them! :)

  • @sarwarrazakhan
    @sarwarrazakhan 8 лет назад +1

    Awesome Videos on VBA coding for a complete novice, thanks for the upload

  • @rd143920
    @rd143920 Год назад +1

    Excellent way of Teaching..!!!

  • @janakpayal7681
    @janakpayal7681 10 лет назад +4

    Thanks wise and kind owl. I love your videos. You are superb. May God bless you.

  • @joannelee4453
    @joannelee4453 6 лет назад

    I really enjoy your tutorials. Well organized and well explained.

  • @gaidindi
    @gaidindi 5 лет назад

    Thank you WiseOwl. Note that in 13:51 you can put directly the value 52 and it will work as well instead of copying and pasting :) Try it this way:
    Sub ChangingTheFileType()
    Workbooks.Add
    ActiveWorkbook.SaveAs "C:\Test workbook.xlsm", 52


    End Sub

    • @WiseOwlTutorials
      @WiseOwlTutorials  5 лет назад +1

      Hi there, yes of course that's true, you can use the underlying value of any constant in VBA and it will still work. The problem is that it doesn't make the code very readable!

  • @khouidermiloudisk
    @khouidermiloudisk 9 лет назад +2

    شكرا جزيلا على دروسك القيمة وعلى طريقة الشرح

  • @briancox5562
    @briancox5562 8 лет назад

    You are a Master Teacher! Thanks for this great resource!

  • @GhXcel
    @GhXcel 8 лет назад

    Awesome videos. I am really enjoying it.

  • @TheMessironaldo
    @TheMessironaldo 7 лет назад +2

    Hi. I'm using Excel 2016 for these lessons. F1 does not take me to the context sensitive help system in Excel. Has the short cut key changed? Thanks

  • @abidalica
    @abidalica 5 лет назад +1

    You are amazing, God bless you brother

  • @eothawne7833
    @eothawne7833 8 лет назад

    Thank you so much. You are a great teacher!

  • @tamoghnaacharyya7212
    @tamoghnaacharyya7212 10 лет назад +1

    Woohoo!!!! Excel vba is back....:)

  • @MohammedKalache
    @MohammedKalache 9 лет назад

    Great thanks already have great ideas to use this tips , but one question , is there a code name for the worksheet like the sheet ?

  • @QQ-nd3qq
    @QQ-nd3qq 10 лет назад

    These videos are the best instruction for VBA I have found on RUclips.
    Will you be doing any for the Find and Findnext functions in VBA?

  • @madogmadson
    @madogmadson 8 лет назад

    Andrew, thank you so much for your videos. My boss thinks i'm a genius! One thing I was hoping to get from this video was how to open a workbook that has a revolving file name by date. On a daily basis I have to copy and paste data from a workbook into my master workbook. The file name changes daily by the date (Workbook 4-26-16.xlsx to Workbook 4-27-16.xlsx). Is there a way to use VBA to make this process easier for me? Thank you again for your great videos.

  • @Sydneyinvestor
    @Sydneyinvestor 9 лет назад

    Hi,
    Firstly, thank you for all the videos. They're really informative and easy to understand. I just saw the video on selecting workbooks. I was wondering if you have a video on selecting/activating workbooks that have a variable name. How would you refer to this workbook in your code if you don't know what the name is? Thanks.

  • @ChapelofJoyYouthMinistries
    @ChapelofJoyYouthMinistries 9 лет назад

    WiseOwl, I am so grateful to have come across your tutorials, it really explained a whole lot of things that was giving me problems.
    i am working on a data base that will include First Name, Last Name, Address, Email, Phone Number, Date of Birth, Date of first Appointment, Date of Last Appointment, Date of Next Appointment, Current Spectacle prescription, Gender, Remarks.
    Will it be possible to do a tutorial on this for me, I am trying to do it for my Optometry clinic. - Ona Seriu-Aig

  • @ilikethiskindatube
    @ilikethiskindatube 7 лет назад

    Thanks for this! I have a list of workbooks I want to extract data from. Is it possible to run through this list, and get the data from each file destination? For example, maybe put the list into a spreadsheet, and reference the range?

  • @mansiagrawal5738
    @mansiagrawal5738 8 лет назад

    Thanks for all the videos of vba. They were quite helpful. The only problem that I am facing is how to add drop down menus within the vba code so that certain data can be selected according to requirement. It would be very kind of you if you could help me with this problem.

    • @mansiagrawal5738
      @mansiagrawal5738 8 лет назад

      Thankyou for the video. I have applied this code in my programming and has saved time. One thing I wanted to ask when details are entered then the keyboard shortcuts need to be entered again to make the formulas work. Kindly help me towards this query.
      Thanks
      Mansi

  • @nikwak
    @nikwak 10 лет назад

    I love your training videos - nice pace and clear instructions. I have just watched part 6 'Worksheets, Charts and Sheets' and have learned half of what I need to create. I need to copy the current worksheet to the beginning of the list of worksheets (learned that bit!) but WITHOUT any formulas being copied. How would I go about that?

    • @nikwak
      @nikwak 10 лет назад

      ***** Wow thanks for the quick reply - I actually only want to stop the sheet copying the formulas over though. So I need the formatting and the contents MINUS the formulas. Is this similar?

    • @nikwak
      @nikwak 10 лет назад

      ***** Andrew. Thanks again. I have just hit an obstacle. The original sheet is password protected so I have written the following code which is probably all wrong as it sticks after the password entry saying the password is not correct although I have checked it and I can unlock the sheet using the same password:
      Sub MoveCopyFigures()
      Worksheets("Front Sheet").Copy before:=Sheets(1)
      ActiveSheet.Unprotect Password = "nikwak1"
      Cells.Copy
      Cells.PasteSpecial xlPasteValues
      ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
      ActiveSheet.Protect Password = "nikwak1"
      End Sub

    • @nikwak
      @nikwak 10 лет назад

      ***** Fantastic - HUGE thanks to you and I will now continue past video 7! This has always been a real grey area to me but you have made it really simple (so far lol) Nick

  • @hanishsandhana5215
    @hanishsandhana5215 7 лет назад

    Simply Awesome..!! Do you have any kind of code or functions glossary into pdf or any other format which can be downloaded?

  • @flufchan
    @flufchan 4 года назад

    Great video (as all of them are). Maybe a good idea to also explain on how to reference a variable to other workbooks (to open and closed workbooks)? I do this already, but recently encoutered a problem with this referencing. Normally "Set wb = Workbooks(Workbookname whithout extension)" works well on open workbooks. But apperntly when you put "Make extensions of files visible" to true, this no longer works. I do not know why.

  • @bryanpicon9644
    @bryanpicon9644 3 года назад +1

    You are Awesome!

  • @Creuzillion
    @Creuzillion 7 лет назад

    Nice videos! Do you have any practice for us to apply the things that we have learned?

  • @michaelsilverberg2623
    @michaelsilverberg2623 8 лет назад

    I'm having a little trouble. I could only create all the subs in the same Code Window, so I end up having to scroll down.
    Referring to 0:40 and 7:24, how do you program VBA so that a line automatically appears after typing a comment?
    How do you do that so that they are written as separate subroutines in new Code Windows without creating them as new individual modules in the Project Window?

  • @ekessler1963
    @ekessler1963 9 лет назад +1

    Excellent Videos!

  • @Globalpeaceseeker
    @Globalpeaceseeker 5 лет назад

    i have written code to open a workbook whose reference is in a cell in an existing workbook and hace made this a variable....this works...however when i try to close the workbook by referring to the variable...it doesnt work and says subscript out of range.....any one know how to fix this?

  • @gabrielmolefe8599
    @gabrielmolefe8599 7 лет назад

    I want to use sumifs in vba to sum a column or row that is in another workbook saved somewhere in the c drive. I managed to do it when THEY ARE IN THE SAME WORKBOOK. PLEASE ASSIST

  • @nour-eddineoumakhlouf5296
    @nour-eddineoumakhlouf5296 7 лет назад

    in my experience and in my case (using office 2013)you don't have to add the extension xlsx or xlsm for workbooks. thank you.

  • @vadymroz2774
    @vadymroz2774 5 лет назад

    Hey everyone,
    I'm on excel 2016. When I place the cursor anywhere on the SaveAs and click F1, instead of "Excel Developer Reference" (which looks like an amazingly useful tool), internet explorer opens a Microsoft docs page about visual studio 2012. Any idea why is it doing that? And how else can I access Developer reference?
    Thank you in advance!

    • @drawingsimon8889
      @drawingsimon8889 5 лет назад

      Because the new office package doesn't contain a integrated manual anymore. The internet one is fine.

  • @raswartz
    @raswartz 7 лет назад

    Your tip for using the F1 key doesn't seem to work in the VBA editor for Excel 2016. It opens Microsoft Edge and then goes to the following URL: msdn.microsoft.com/en-us/library/dd831853(v=vs.110).aspx?appId=Dev11IDEF1&l=en-US&k=k(vbaxl10.chm199282)%3bk(TargetFrameworkMoniker-Office.Version%3dv16)&rd=true
    Which is not helpful. Is there some way around this or to get back to the behavior of earlier versions of Excel?

  • @ngonishoniwa9753
    @ngonishoniwa9753 10 лет назад

    Many thanks to you

  • @thewindrises
    @thewindrises 7 лет назад

    Creating new workbook based on Top Movies didn't work for me, it says something you can't open two workbooks with the same name, and why did you change file type to xltm from xlsm? i didn't follow there.
    change file type:
    Workbooks.Add
    ActiveWorkbook.SaveAs "c:\Test workbook.xlsm", xlOpenXMLWorkbookMacroEnabled
    can you tell what is wrong here? it didn't work for me.

  • @irynamusiiovska828
    @irynamusiiovska828 6 лет назад

    Thank you for video!

  • @amitbond1
    @amitbond1 6 лет назад

    Hi Andrew
    I'm trying to copy data from 3 workbooks(Amit.xls,Amit2.xlsx,Amit3.xlsx) into another workbook (Zmaster.xlsm) using do loop and dir method but getting an error mesage:-
    Sorry, we couldn't find amit.xlsx.Is it possible it was moved, renamed or deleted.
    However when I check for a value for
    myfile it is showing the name of the file from the folder and also the file is available in the folder.
    Pls help

  • @djamail75
    @djamail75 10 лет назад

    Many thanks

  • @qasimawan3568
    @qasimawan3568 Год назад

    How do I get to the VBA documentation help? I'm using Excel 2021

    • @WiseOwlTutorials
      @WiseOwlTutorials  Год назад +1

      Hi! You can go here learn.microsoft.com/en-us/office/vba/api/overview/excel
      I hope it helps!

  • @shwetalgajbhiye7471
    @shwetalgajbhiye7471 9 лет назад

    In excel 2013 i didnt have to copy the code to change the file type it could save directly by specifying xlsm. :)

  • @exp3905
    @exp3905 9 лет назад

    Thank you!

  • @age_of_reason
    @age_of_reason 6 лет назад

    12:09 Why doesn't it save as "C:\Test workbook.xlsx"? You put the C:\ in quotes also.

  • @dhanajipawar9508
    @dhanajipawar9508 8 лет назад

    hi
    After "openexistingworkbook" i am trying to add new workbook with template but new workbook is not creating. error appearing "run time error-1004, Sorry, we could not find location...... is it was moved,renamed or deleted?.
    What should I do?

    • @concert_music
      @concert_music 7 лет назад

      I am finding that worbooks I save as templates are saved as xltx rather than xltm - thank you for your videos by the way they are very clear

    • @concert_music
      @concert_music 7 лет назад

      thanks!

  • @c777cw
    @c777cw 7 лет назад

    super cool

  • @alik.f4331
    @alik.f4331 10 лет назад

    Hi Andrew, First of all I thank you so much for these beautiful and wonderful series of VBA video. Please, could you help me with this dilemma.
    I have created couple of worksheet (like Invoice sheet, Contract sheet etc.) in a workbook for my customers.
    I have a folder name is : Customer 2014
    whenever I write a contract or invoice for customer, I need that workbook to be saved in new sub-folder with the name of customer inside the folder "Customer 2014".
    I really appreciate if you can help me with code.

  • @yqzhang3430
    @yqzhang3430 7 лет назад

    Thanks

  • @johannesmp3
    @johannesmp3 Год назад

    What happens with this? If you close a workbook, it remains open in Project Explorer. Is this a bug?

    • @WiseOwlTutorials
      @WiseOwlTutorials  Год назад

      Yes, it is a bug! You can see some possible solutions here stackoverflow.com/questions/71154862/excel-workbooks-that-i-open-in-vba-still-appear-in-the-project-explorer-after-cl
      I hope it helps!

  • @Search4Knowledge
    @Search4Knowledge 8 лет назад

    Awesome!

  • @ranjitreddy7985
    @ranjitreddy7985 9 лет назад

    hi Andrew,
    i need these modules,
    how can i download

    • @ranjitreddy7985
      @ranjitreddy7985 9 лет назад

      ho.... ok
      if possible please send to my mail id
      really i loved this

  • @roderickcamenzuli6959
    @roderickcamenzuli6959 10 лет назад

    Hi Andrew,
    Thanks for your time doing these videos.
    I'm using the file name referring to a cell but if the filename already exist i would like to know the code how to refer to another cell or value.
    I have tried this IF but no luck.
    Thanking you again in advance.
    Roderick
    ChDir "C:\Users\Roderick\Documents\Order Form Tests"
    Dim newFn As Variant
    newFn = Worksheets("Quotation").Range("k8").Value & (" ") & Range("c6").Value
    If newFn.fileexcists("C:\Users\Roderick\Documents\Order Form Tests") Then
    Range("k8").Value +1
    ActiveWorkbook.SaveAs newFn, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End If

  • @tamleduy4524
    @tamleduy4524 7 лет назад

    How can the workbook keyword appear in the line code? I couldn't do like you did.

  • @WalyB01
    @WalyB01 3 года назад +1

    And then they made the help function link to Visual Studio, online. terrible

    • @WiseOwlTutorials
      @WiseOwlTutorials  3 года назад

      Yes, it's really annoying! Most keywords still work correctly with the F1 help but there are plenty of exceptions. I tend to just navigate to the root of the Excel VBA Object Model on Microsoft Docs and use the menu on the left or the search box to find the relevant item docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model

    • @WalyB01
      @WalyB01 3 года назад +1

      @@WiseOwlTutorials Many thanks for the quick answer and these great videos!

    • @WiseOwlTutorials
      @WiseOwlTutorials  3 года назад

      @@WalyB01 No worries, thanks for the support!