0

I have a Macro which currently we download a spreadsheet Update: and it automatically opens (and doesn't save) -- from our custom internal web interface. The spreadsheet name changes each time. The current utilization instructions are to open VBA Editor, copy Macro into new module, then run Macro.

I'd like to make a stand alone file to make the instructions something like this, open workbook, then double click file.

My though would be that they could only have the workbook in question open and the standalone file code would be something like use open workbook (Very generic).

I read about vbs, but all I did was change my macro from .txt to vbs and clearly it doesn't work yet. Can anybody point me in the right direction?

  • 1
    why are you not keeping your macro in an XLAM or PERSONAL.XLSB? – cybernetic.nomad Mar 20 '19 at 21:39
  • When the workbook opens from the web it doesn't load personal.xlsb and needs to be saved a d reopened. Also I found it annoying when I used personal.xlsb that it opened in the background with each worksheet. – FreeSoftwareServers Mar 20 '19 at 21:41
  • I added an update, the workbook auto opens it doesn't download then we open it, that's why personal.xlsb didn't work without first saving, then opening, plus this is going to be used on possibly hundreds of terminals and having everybody edit their personal.xlsb is both hard to follow instructions and time consuming. – FreeSoftwareServers Mar 20 '19 at 21:45
0

While I was able to get this to work, I didn't utilize this method as it involved enabling a "Trust VBA Project" setting in Office and it doesn't sound secure to me.

https://security.stackexchange.com/questions/205803/is-it-safe-to-enable-trust-access-to-the-vba-object-model-microsoft-office/205814#205814

Here is how it would work though:

Dim  xlApp, xlBook

Set xlApp = getObject( , "Excel.Application")
Set xlBook = xlApp.ActiveWorkbook

xlBook.VBProject.VBComponents.Import "\\PATH\Module1.bas"
xlApp.Run "Sub_Name"
'objExcel.ActiveWorkbook.Close


xlApp.Application.Quit
WScript.Echo "Finished."
WScript.Quit

What I ended up doing was distributing the custom "OfficeUI" via --> https://www.freesoftwareservers.com/display/FREES/Distributing+Macro+via+Add-In+-+Customized+Ribbon+-+Via+Batch

For some reason the files in XLSTART & Personal.xlsb both didn't load when loading excel via the link in our web interface. But, the OfficeUI custom Ribbon loaded. I still placed the AddIn.xlam into XLSTART because then Excel didn't warn about "Macros", but it wasn't loaded upon start. It doesn't matter though because OfficeUI called the file!Sub directly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.