Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
Showing results for 
Search instead for 
Did you mean: 

Save workbook with macros

New Contributor

Hi, Is there any way to save a workbook without losing the macros in databricks?


Community Manager
Community Manager

Hi @LightyagamiWhen working with Databricks and dealing with macros, there are a few approaches you can consider to save a workbook without losing the macros:

  1. Export to Excel with Macros Enabled:

  2. Convert to Pandas and Export:

    • While there’s no direct way to save an Excel document from a Spark DataFrame, you can convert your Spark DataFrame to a Pandas DataFrame and then export it. Here’s an example:
      # Create a dummy Spark DataFrame
      spark_df = spark.sql('SELECT * FROM default.test_delta LIMIT 100')
      # Convert Spark DataFrame to Pandas DataFrame
      pandas_df = spark_df.toPandas()
      # Export Pandas DataFrame to an XLSX file
      pandas_df.to_excel('excel_test.xlsx', engine='xlsxwriter')
      Note that you might need to adjust the file paths and permissions as needed 1.
  3. Workaround for Macros in XLSM Files:

    • If you specifically need to work with XLSM files (Excel files with macros enabled), you can use recent versions of XlsxWriter. Extract the VbaProject.bin macro file from an existing XLSM file and insert it into your new file. Here’s a snippet:
      import pandas as pd
      df = pd.DataFrame({'First': [5, 2, 0, 10, 4], 'Second': [9, 8, 21, 3, 8]})
      writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
      # Continue with writing your DataFrame to the XLSM file
      Again, ensure that you have the necessary libraries installed 2.

Remember to adapt these solutions based on your specific use case and requirements. Happy coding! 🚀

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!