Many people don't know that in PPT, using VBA can easily retrieve data from Excel, and you can do a few exercises through a slide. Let's take a look at how to combine PPT and Excel to practice with Xiaobian. In the classroom, many teachers use PPT for classroom exercises. In general, how many slides are used in how many exercises. There is a drawback to this approach: if you want to change the test questions in the slide to a uniform font or font size, you need to modify them one by one. If the slides used in the test are less, this method is fine; however, Imagine that there are dozens of hundreds of exercises, and if you want to modify them one by one, it is really troublesome and very troublesome. thing. After continuous research, the author finally came up with a very effective method: store some of the questions you need in an Excel worksheet, and then use VBA to read the data in the Excel worksheet to the slide in the PPT. So you only need a slide to practice a few exercises. To modify it, just modify the text box in this slide. If you want to know how this method is implemented, please come with me! Preparing Worksheets and Slides This process is mainly to prepare an Excel worksheet for the test questions and a slide show showing the test questions. First, prepare an Excel file, store the required questions in the Sheet1 worksheet, and put the Excel file and PPT file in the same folder. Then, open PowerPoint 2003 and insert a blank slide. In this slide, first insert two text boxes, one of them is used to store the test questions, one is used to store the correct answer (in the text box where the test questions are stored, you can enter a prompt statement first, in the correct storage Enter a space in the text box of the answer (Figure 1). Finally, select the “slide show → action button” menu to insert 5 action buttons at the appropriate position on the slide. You can right-click on the corresponding action button to add the corresponding text to them (Figure 2). The process of adding VBA code is mainly to write VBA code that controls the text boxes in Excel worksheets and PPT slides. When it comes to writing code, you may be a little dizzy. In fact, the program code is not so mysterious, here you can first enter or copy my code into your file, and then look at my explanation to you, everything is much simpler! First, we choose the "Tools & Rarr; Macro & Rarr; Visual Basic Editor" menu, then the Microsoft Visual Basic program window will pop up, which is the VBA editor. Select the “insert →module" menu, and you will see a blank code editing area on the right side. We can try to enter the code in the image (Figure 3). Looking at the code, you are a little confused! It doesn't matter, you see me explaining the explanation to you, and you are suddenly clear. In the above code, first define the four variables xlApp, xlBook, xlSheet, i with Public. The first three are used to control the Excel worksheet, and the i is used to represent the number of rows in the current Excel worksheet. Then, using 5 "Sub… End Sub"; defines five event processes, the events in VBA are placed between Sub and End Sub, each process has a corresponding name, such as opening the question bank ( ), the next question () and so on. Now let's explain "Open the question bank ()" this process: Set xlApp = CreateObject ("Excel.Application") is used to create an Excel control; xlApp.Visible = False means that the control is not visible, that is, hide Excel do not let it show in the current window; Set xlBook = xlApp.Workbooks.Open (CurDir () + " \\ book1.xls ") represents Excel to open the file named book1.xls, CurDir () indicates the current directory. Set xlSheet = xlBook.Worksheets(1) indicates that the permission of the first worksheet in the control workbook, Sheet1, is assigned to xlSheet. ActivePresentation represents the currently active presentation. .Slides(1) represents the first slide in the presentation, and .Shapes(1) represents the first text box in the slide. .TextFrame.TextRange.Text represents the text in the text box. We connect them together, ie ActivePresentation.Slides(1).Shapes(1).TextFrame.TextRange.Text represents the content of the first text box in the first slide in the current presentation. xlSheet.Range("a" & i) represents the value of the cell in Sheet1. Now i=3, the value of cell A3 is obtained. The function of the Replace function is replacement, Chr(10) stands for line feed, Chr(13) stands for carriage return, and Replace(xlSheet.Range("b" & i), Chr(10), Chr(13)) Indicates that the newline character in cell B3 is replaced with a carriage return. “&” This symbol acts as a join and can be used to force connections of different types of values. The entire sentence ActivePresentation.Slides (1) .Shapes (1) .TextFrame.TextRange.Text = xlSheet.Range (" a " & i) & ", " & Replace (xlSheet.Range (" b" & i), Chr(10), Chr(13)) means adding the value of cell A3 in Sheet1 to “, ” plus the value after replacing the newline character with a carriage return in B3 Assigned together to the first text box in the slide. In the process of "Next question ()", i=i+1 means that the current line in the worksheet is moved down one line, that is, now it is the third line, after i+1, the value of the fourth line can be taken. . This process applies the IF statement for conditional judgment to determine whether it has reached the end of the question bank. If the value of xlSheet.Range("a" & i) is not empty, the corresponding value is assigned to the title text box; otherwise, The msgbox() function prompts for the message. At the same time, in order not to prompt the correct answer, ActivePresentation.Slides(1).Shapes(2).TextFrame.TextRange.Text = " " means to make the second text box in the slide, the correct answer text box, appear as a space. Explained so much, do you understand this? Then the other processes and their meanings are similar, you understand and understand. Adding an event to a button is mainly a process of adding a corresponding event to each button. For example, we add the event to the “correct answer” button. We can right click on it and select the “Action Settings” menu. In the pop-up “Action Settings” window, select “Run Macro” and click The right arrow selects the corresponding process, and finally clicks “OK” The addition of other button events is the same as it does (Figure 4). Activating VBA Code To make these events, VBA code, work, we need to adjust the security of the macro. Select “Tools & Rarr; Macro & Rarr; Security", set the security of the macro to “low", so that you can run the VBA code smoothly (Figure 5). After the performance demonstration above is completed, we can play the slideshow. When using, we first click the “open the question bank” button, then the first question in the question bank will be presented. Only when you click the “correct answer” button will the correct answer of the question be displayed; if you click &ldquo The next question & rdquo; button, the title will be switched, and the correct answer to the next question will not be displayed. If you feel that the title, font size, color, etc. of the title or correct answer are not too satisfactory, you only need to set the corresponding text box format. Look, this method is not so cool! Using this method, it doesn't matter how many questions you have, don't try it quickly (Figure 6)! ******************* Code ************* Public xlApp, xlBook, xlSheetPublic i As IntegerSub open exam () Set xlApp = CreateObject ("Excel.Application")xlApp.Visible = FalseSet xlBook = xlApp.Workbooks.Open(CurDir() + "\\book1.xls")Set xlSheet = xlBook.Worksheets(1)i = 3ActivePresentation.Slides(1) .Shapes(1).TextFrame.TextRange.Text = xlSheet.Range("a" & i) & "," & Replace(xlSheet.Range("b" & i), Chr( 10), Chr(13))End SubSub Next question()i = i + 1If xlSheet.Range("a" & i) <> "" ThenActivePresentation.Slides(1).Shapes(2 ) .TextFrame.TextRange.Text = " " ActivePresentation.Slides (1) .Shapes (1) .TextFrame.TextRange.Text = xlSheet.Range (" a " & i) & ", " & amp Replace(xlSheet.Range("b" & i), Chr(10), Chr(13)) ElseMsgBox (" has reached the last question!")End IfEnd SubSub Previous question ()i = i - 1If xlSheet.Range (" a " & i) & lt; & gt; " " O r xlSheet.Range (" a " & i) & lt; & gt; " ID " ThenActivePresentation.Slides (1) .Shapes (2) .TextFrame.TextRange.Text = " " ActivePresentation.Slides (1) .Shapes(1).TextFrame.TextRange.Text = xlSheet.Range("a" & i) & "," & Replace(xlSheet.Range("b" & i), Chr( 10), Chr(13)) ElseMsgBox (" is the first question! ")End IfEnd SubSub Correct Answer ()ActivePresentation.Slides(1).Shapes(2).TextFrame.TextRange.Text = xlSheet.Range("c" & i)End SubSub Close Question Bank()xlBook.Close ( False)xlApp.QuitSet xlApp = NothingEnd Sub This article is from [System Home] www.xp85.com