excel-tools

import { tool, type Tool } from "@lmstudio/sdk";
import { z } from "zod";
import * as XLSX from "xlsx";
import * as path from "path";
import * as fs from "fs";
import { checkReadable, resolvePath } from "../utils/config";

export function getVbaTools(workingDir: string): Tool[] {
  return [

    // ── 28. excel_read_vba ───────────────────────────────────────────────────
    tool({
      name: "excel_read_vba",
      description: "Extract and display VBA module code from a macro-enabled Excel file (.xlsm). Requires a .xlsm file. VBA creation is not supported (requires Python+openpyxl).",
      parameters: {
        filePath: z.string().describe("Path to a .xlsm file"),
      },
      implementation: async ({ filePath }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        const ext = path.extname(filePath).toLowerCase();
        if (ext !== ".xlsm") return `Error: VBA reading requires a .xlsm file. Got: ${ext}`;
        try {
          const wb = XLSX.readFile(filePath, { bookVBA: true });
          if (!wb.vbaraw) return "No VBA project found in this file.";

          // Write raw VBA binary to temp, then try to parse module names from it
          const vbaBuffer = Buffer.from(wb.vbaraw as string, "binary");

          // Extract module names and code snippets from VBA binary (text search)
          const vbaStr = vbaBuffer.toString("latin1");
          const modules: { name: string; preview: string }[] = [];
          const moduleMatches = [...vbaStr.matchAll(/Attribute VB_Name = "([^"]+)"/g)];

          if (moduleMatches.length > 0) {
            for (const match of moduleMatches) {
              const name = match[1];
              // Extract code between Attribute blocks (heuristic)
              const start = match.index ?? 0;
              const snippet = vbaStr.substring(start, start + 500)
                .replace(/[\x00-\x08\x0b-\x1f\x7f-\x9f]/g, " ")
                .trim();
              modules.push({ name, preview: snippet });
            }
          }

          return JSON.stringify({
            file: filePath,
            vba_present: true,
            vba_size_bytes: vbaBuffer.length,
            modules_found: modules.length,
            modules,
            note: "VBA binary extracted. For full source editing, use Excel or Python+openpyxl.",
          }, null, 2);
        } catch (e) { return `Error: ${e}`; }
      },
    }),

  ];
}