Opening Excel file without running VBA code

[Alex_Hughes6GQ7S]

Hello everyone! I'm working on a lisp command that will open an premade excel file, read some contained information (length, width, etc), and then draw a structure based off that info. However, the excel files this command will be reading from all contain faulty VBA code. When a file is opened "manually" (like double clicking on it in file explorer), Excel displays a message below the ribbon about security disabling some active content. If you enable the content, a VBA compiler opens and displays "Compile error: Can't find project or library". The user can't access the excel file until closing out of the warning and closing the compiler with another accompanying warning. Okay, if you want to access information from the excel file, just don't enable that content.

The problem is that when opening excel via AutoLISP (I am using Jeffery Sander's GetCells function, also attached to this post) excel always tries to run that active content, resulting in the compiler opening and the error appearing, causing the command to pause until all those warnings are closed.

So, the question: Is there some vlax property or other command I can use to prevent excel from running that code when opening a file?

I have found that if the excel file is already opened, excel does not attempt to run the faulty VBA code, and the command executes correctly. However, part of my goal with this command is to save the user as much time and effort as possible. Needing the user to navigate to and open the excel file beforehand defeats part of that purpose, in my opinion.

Editing the excel files to remove the faulty VBA code does work, but this would require every user to open the file beforehand and then delete a section of code (see above point), or would need management to allow a change to the base template that these excel files are based on.

Thank you for your time!

[CodeDing]

@Alex_Hughes6GQ7S ,

I made a DLL (alx.dll) with some extended autolisp functions which has some Excel functions that you can try.

It's posted here:

https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/autolisp-extended-alx-functions/m-p/... 

There is a help file associated with it. After you NETLOAD the DLL, just run the (alx-help) lisp function in the command line.

Make sure you unblock the file if necessary:

Unblock the file

Unblock the DLL/DBX file by following these steps:

1. Place the files in a local folder.
2. Right-click each file and select Properties.
3. Under the General tab, Security section (at the bottom), click Unblock.
4. Confirm with OK.
5. Replace the files in the appropriate folder.

---

Best,

~DD

[Alex_Hughes6GQ7S]

Thanks for the reply! Is the unblocking something that would need to be done by every device that wants to run the lisp command? In my current position (very junior CAD drafter working on a personal project) I don't think I would be able to get IT/management to allow code written by someone else to run on all of our computers, even if it is clearly harmless.

[CodeDing]

@Alex_Hughes6GQ7S ,

I work for an Engineering and Survey company as a drafter, and I manage our Lisp/DLL/AutoCAD routines for the department. I have not needed the users to unlock that DLL. I believe I merely did it once, I have it centralized on our network drive and it loads to their computers just fine EVERY time they open CAD. We've been using it for about3 years now.

Here's the lisp code I use to load it via my acad.lsp file:

((lambda ( / dll)
  (if (setq dll (findfile "X:\\network drive\\some folder\\alx.dll"))
    (progn
      (command "_.NETLOAD" dll)
      (princ "\nSuccessfully Loaded - ALx.dll")
    );progn
    (princ "\nError loading ALx.dll, file not found.")
  );if
  (princ)
))

Best,

~DD

[Alex_Hughes6GQ7S]

That's amazing to hear. I'll give it a shot. Thanks again for your help!

[CodeDing]

I should mention..

The help file that opens when you run the (alx-help) function will NOT display properly if it is opened in a network location. It will look like this on network location:

The help file (named "ALx Functions Help.chm") should be created in your Documents folder by default. So look for it there. I will attach a copy of the help file here for good measure, but once it's on the Local machine, it will display the help properly like so:

Best,

~DD

[Alex_Hughes6GQ7S]

I managed to load it and access the help file with no problem, but I don't see any functions that apply to the problem in my original post. While these all do look useful to me in general, I don't believe any of them will allow me to open an excel file from an AutoLISP command without also running any attached VBA code.

[CodeDing]

@Alex_Hughes6GQ7S ,

I'm not 100% if the VBA code will get evaluated yet or not. Was just an option for you to explore. Have you tried it?

Might look something like:

(defun c:TEST ( / wbPath worksheets data)
  (setq wbPath "C:\\users\\me\\my folder\\some workbook.xlsx")
  (alx-excel-wb-set wbPath)
  (setq worksheets (alx-excel-ws-list))
  (alx-excel-ws-set (car worksheets))
  (setq data (alx-excel-range-get "A1:C3"))
  (print data)
  (alx-excel-wb-close)
  (princ)
)

Best,

~DD

[Alex_Hughes6GQ7S]

You're right, I missed that while looking over the functions. But I'm sorry to say after fiddling with it for a while and getting errors, I've realized the workbook has the .xlsm extension. According to the help file, that won't work. Again, this all looks like really good stuff, but I think I'm out of luck in this case.

Oh, one little nitpick: the wb-set function is shown as "alxa-excel-wb-set" at a few points in the help document. That messed me up the first few times.

 

[CodeDing · Accepted solution]

---

Oh, one little nitpick: the wb-set function is shown as "alxa-excel-wb-set" at a few points in the help document. That messed me up the first few times.

---

Poop, yeah I just noticed that. Will have to update help file! Thank you for pointing out.

I'm not giving up yet lol.

How often do you need to make this call to this workbook? Is it a large file?

If it is a medium or small file, and only a single or couple calls are made.. we COULD just create a temporary copy (which automatically gets deleted when Acad session is closed) and read that file instead?

Would look like this:

(defun c:TEST ( / wbPath tmpFile worksheets data)
  (setq wbPath "C:\\Users\\me\\my folder\\macro file.xlsm")
  (if (and (findfile wbPath)
           (setq tmpFile (vl-filename-mktemp ".xls"))
           (vl-file-copy wbPath tmpFile))
    (progn
      (alx-excel-wb-set tmpFile)
      (setq worksheets (alx-excel-ws-list))
      (alx-excel-ws-set (car worksheets))
      (setq data (alx-excel-range-get "A1:C3"))
      (alx-excel-wb-close)
      (print data)
    );progn
  ;else
    (prompt "\nError creating temporary Excel file.")
  );if
  (princ)
)

Best,

~DD

[Alex_Hughes6GQ7S · Accepted solution]

I'm happy to say I solved my own problem! The Excel.Application ActiveX object has a property "EnableEvents". If you set this to :vlax-false, this will prevent the workbook from running any macro events when opened.

;The 'GetCells' function linked in the OP
(vl-load-com)
(defun getCellsFunction(fileName sheetName cellName / myXL myBook mySheet myRange cellValue)
  (setq myXL(vlax-get-or-create-object "Excel.Application"))
  (vla-put-visible myXL :vlax-false)
  (vlax-put-property myXL 'DisplayAlerts :vlax-false)
  (vlax-put-property myXL 'EnableEvents :vlax-false) ;THIS LINE RIGHT HERE PREVENTS THE MACRO EVENTS FROM RUNNING
  (setq myBook (vl-catch-all-apply 'vla-open (list (vlax-get-property myXL "WorkBooks") fileName)))
;...and then the rest of the function

 It's a great feeling to figure out an issue like this. I hope this ends up helping someone in a similar situation in the future.

[Alex_Hughes6GQ7S]

After finding a solution myself I was about to start ignoring this thread, but I decided to give your idea a try. Turns out it works just as well! Thanks once again for all your help with these issues.