Client Background
Client: A leading tech consutling firm in the USA
Industry Type: IT & Consulting
Products & Services: IT & Consulting
Organization Size: 100+
About the Client:
The client is an organization responsible for managing structured evaluation and scoring processes, where accuracy, efficiency, and secure document handling are critical. Their workflow relies heavily on macro-enabled Excel systems to calculate results and generate official PDF reports used for assessments or decision-making. With a focus on improving operational flexibility and user experience, the client sought to eliminate storage limitations tied to OneDrive by enabling customizable file management, ensuring better control over document storage, accessibility, and overall workflow efficiency for end users and administrators.
The Problem
The current workflow for processing scores generated by judges relies on a macro-enabled Excel workbook, where the results are computed and subsequently exported as PDF files. However, these PDF files are automatically saved to OneDrive, which poses limitations on accessibility and file management for users who require alternative storage solutions. The project aims to modify the existing macro functionality to allow users to specify a different file path for exporting the PDFs. This change is essential to ensure that the PDFs are saved in a location that meets the specific needs of users, while also maintaining an organized and efficient workflow. Furthermore, careful attention must be given to ensure that the new file path is not linked to OneDrive, preventing potential synchronization issues and enhancing the overall usability of the document management process.
According to the video, the macro files are stored in a folder within OneDrive. The code to export the PDFs specifies that the generated files are saved in the same directory as the macro files. Consequently, this means that the PDFs are automatically uploaded to OneDrive.—-(this can be solved by changing the location of macro files in clients system).
Our Solution
To address the issue of PDF files being automatically downloaded to the same location as the macro file, we implemented a modification to the macro code responsible for exporting the PDF documents. The current workflow defaults to saving the exported PDFs in the same directory as the macro file, which can lead to inefficiencies and confusion for users who prefer to store files in a designated location.
We began by analyzing the existing macro code to locate the specific section responsible for the file path during the PDF export process. After identifying this area, we modified the macro to allow for a dynamic file path instead of a static one. This change enables users to specify their preferred storage location by defining the new file path as a variable within the macro, thereby allowing for greater flexibility.
To enhance usability, we integrated a user input prompt that requests the desired file path for saving the PDF. This prompt ensures that users can easily set their preferred location each time they run the macro.
Once a file path is confirmed, the macro exports the PDF file to the specified location, ensuring that the documents are stored exactly where the user intends. By implementing these changes, users will have the flexibility to save exported PDFs in a location that suits their workflow, minimizing reliance on OneDrive. This approach not only streamlines the document management process but also enhances user satisfaction by providing greater control over file storage.
Solution Architecture
The project was executed using three different approaches to provide flexibility in how users can save their exported PDF files. Each approach addresses the need for modifying the file path and optimizing the user experience. Below are the three methods, along with their respective folder structures where the results are saved.
- Direct File Path (Saved in ‘directly_save_pdf’)
- User Selects Path Every Time (Saved in ‘ask_everytime_before_safe_PDF’)
- User Selects Path Once, Saves for Future Exports (Saved in ‘ask_once_then_save_path_pdf’)
Deliverables
- Modified Macro-Enabled Excel Workbook
- An updated Excel workbook with VBA macros allowing dynamic file path selection for exporting PDF files.
- Includes three methods for managing file paths:
- Direct File Path: Automated saving to a predefined location.
- User-Specified Path on Every Export: Allows users to select a file path dynamically during each export.
- User-Specified Default Path: Enables users to set a default path for future exports.
- Documentation
- Detailed instructions for configuring and using the new macro features.
- Steps to update the predefined file path according to user system requirements.
- Troubleshooting guide for common issues (e.g., invalid paths, folder creation errors).
- Codebase with Comments
- Well-documented VBA code with inline comments explaining the purpose of each section and key functionalities.
- Test Report
- A report summarizing the testing process and outcomes for the modified macro under various scenarios, including different file paths, folder structures, and user preferences.
Tech Stack
- Tools used
- Microsoft Excel (Macro-Enabled Workbooks): Used as the primary platform for developing and running VBA macros to process and export PDF files.
- Visual Basic for Applications (VBA): Language for writing and modifying macros within Excel.
- Language/techniques used
- VBA (Visual Basic for Applications): Primary programming language for scripting macros and automating PDF export processes.
- Skills used
- Macro Development: Expertise in designing and debugging VBA macros for automated workflows.
- User Experience Design: Focus on creating intuitive prompts and dialogs for seamless user interactions.
- File System Management: Proficient in handling dynamic file path creation, folder structure management, and file export processes.
- Problem-Solving: Addressing limitations of OneDrive-linked file paths by designing flexible alternatives.
- Testing & Validation: Ensuring all implemented methods function reliably under various scenarios.
What are the technical Challenges Faced during Project Execution
- Dynamic File Path Implementation
- Challenge: VBA macros traditionally rely on static file paths, making it difficult to introduce user-defined dynamic paths without significant code restructuring.
- Folder Structure Automation
- Challenge: Automatically creating required folders (e.g., “Ancillary Sheet Output”) without manual user intervention posed a technical challenge.
- Ensuring Backward Compatibility
- Challenge: Introducing new functionality risked breaking existing workflows for users accustomed to OneDrive-based storage.
How the Technical Challenges were Solved
- Dynamic File Path Implementation
- Solution: Used the Application.FileDialog object to enable users to select paths dynamically while ensuring compatibility with existing workflows.
- Folder Structure Automation
- Solution: Integrated VBA’s MkDir and Dir functions to check for and create missing directories seamlessly.
- Ensuring Backward Compatibility
- Solution: Designed fallback mechanisms that default to the original OneDrive path if no alternative was specified, ensuring continuity.
Contact Details
This solution was designed and developed by Blackcoffer Team
Here are my contact details:
Firm Name: Blackcoffer Pvt. Ltd.
Firm Website: www.blackcoffer.com
Firm Address: 4/2, E-Extension, Shaym Vihar Phase 1, New Delhi 110043
Email: ajay@blackcoffer.com
WhatsApp: +91 9717367468
Telegram: @asbidyarthy
