BYU Student Author: @Mark
Reviewers: @Erick_Sizilio, @Spencer, @Christian
Estimated Time to Solve: 15 Minutes
We provide the solution to this challenge using:
- Excel
- VBA
Need a program? Click here.
Overview
The company you work for, CSV Lovers, uses accounting software that allows you to export many different types of reports as csv files. You manipulate and develop the reports for management and operations, mostly using VBA code that you’ve written. One mistake you make quite often is that you forget to change the file type to xlsx and the csv format ruins all of your hard work. You’re sick of this mistake occurring and recently learned that you can call a Sub procedure from another procedure by simply typing the name of the Sub you’re trying to call in the code of the procedure you’re working on. You decide that you will write a Sub that will save the current csv file you’re working in as an xlsx and then call the Sub in every other macro you run. You want to get rid of those pesky csv files!
Instructions
Open up Visual Basic and look at the Project window. If you do not see VBAProject (PERSONAL.XLSB), see this link and follow the steps. The Personal Macro Workbook allows you to run any macro within the Personal Macro Workbook while working in any Excel file. We want to create our macro within the Personal Macro Workbook.
- Open up a module in VBAProject (PERSONAL.XLSB) and create a Sub procedure called Save_As. Create a variable called “directory” that is the directory where the Challenge67_Data.csv file is located.
- Create an input box that will prompt the user to input the desired file name. Tell the user not to include “.xlsx” in their file name. Also, tell the user not to use a file name that already exists within the previously defined directory and explain that this will overwrite the other file.
- Once the user presses the “Ok” button, save the file as an xlsx file. Make a message box pop up that says, “Your file is saved in the following folder:” followed by the information from the “directory” variable.
- If the user presses the “Cancel” button, do not save the file as an xlsx file and make a message box pop up that says, “Your file is not saved as a .xlsx file!”.
- Create another Sub procedure called Test. Write only “Save_As” in the Sub (see below). Run the Test macro to see if your Save_As Sub runs as intended.
Data Files
Suggestions and Hints
Some of your lines of code can get pretty long. You can continue code onto the next line by writing “ _” (note the space before the underscore) and then continuing your code on the next line. However, you cannot use this method within a string. For more info, see this link.
Be sure to include any “\”s and file extension (“.xlsx”) necessary to properly save the file.
Write an If statement to deal with whichever button the user presses.
Solution
