Workaround to use VBA in Excel Doc in Sharepoint

[wtom0412]

Hello,

Hopefully, this is the correct forum to use for this question, so please forgive me if it isn't...

I have embedded an Excel Doc in a SharePoint page. The doc contains a clickable bit of VBA to save the doc, and email it as an attachment.

I am aware that VBA can't run on the online version of Excel, so I need a workaround to enable me to send the doc as an attachment (once the user has made changes) to a predefined email address.

I have tried to activate the "Open Documents in Client Applications by Default" so it will open in the Desk Top App, but it still opens in the browser.

Please help, I have run out of ideas!!


Cheers, Toby.

 

[Micron]

If memory serves, you could use vba to access the file and do things to/with it by employing the CanCheckOut property. I have used Access to check out a SP workbook and push data from Access to an Excel wb in order to update wb charts. You probably could checkout a SP wb file, email a copy of the file (or pdf's of ranges, or whatever) then check the wb back in to SP.

Workbooks.CanCheckOut method (Excel)

Office VBA reference topic

learn.microsoft.com

EDIT - you would need to know the UNC path of the SP server. If you can map to it you can see the server name and path to the folder where your wb is stored on the SP server. This is a requirement to avoid opening the wb in a browser.
The path will start with \\, like \\SomeServerNameHere\TopFolderNameHere...

 

[wtom0412]

If memory serves, you could use vba to access the file and do things to/with it by employing the CanCheckOut property. I have used Access to check out a SP workbook and push data from Access to an Excel wb in order to update wb charts. You probably could checkout a SP wb file, email a copy of the file (or pdf's of ranges, or whatever) then check the wb back in to SP.

Workbooks.CanCheckOut method (Excel)

Office VBA reference topic

learn.microsoft.com

EDIT - you would need to know the UNC path of the SP server. If you can map to it you can see the server name and path to the folder where your wb is stored on the SP server. This is a requirement to avoid opening the wb in a browser.
The path will start with \\, like \\SomeServerNameHere\TopFolderNameHere...

Thanks Micron, I am a little bit aware of the CheckOut function at the Desktop App Level, but given VBA won't work once it is placed on Sharepoint, where would I put the code from the attachment you included?

Cheers, Toby

 

[Micron]

The code would be in an wb on your pc. You can do pretty much anything to or with the SP workbook from your desktop wb. This code wb can also reside on the normal network server. It doesn't have to be on your desktop.

 

[wtom0412]

If memory serves, you could use vba to access the file and do things to/with it by employing the CanCheckOut property. I have used Access to check out a SP workbook and push data from Access to an Excel wb in order to update wb charts. You probably could checkout a SP wb file, email a copy of the file (or pdf's of ranges, or whatever) then check the wb back in to SP.

Workbooks.CanCheckOut method (Excel)

Office VBA reference topic

learn.microsoft.com

EDIT - you would need to know the UNC path of the SP server. If you can map to it you can see the server name and path to the folder where your wb is stored on the SP server. This is a requirement to avoid opening the wb in a browser.
The path will start with \\, like \\SomeServerNameHere\TopFolderNameHere...

Thanks again Micron, but I'm still sure how I can make this work? Maybe if I explain the setup?

I have an Excel doc embedded on a SP page. The doc is sitting in a public folder in SP. I want the user to go to the SP page, fill in the Excel doc, click the button to save the doc and email it to me.

If the code is sitting in the doc but can't be accessed until it is open in the desk top app, how will the code be read at the point the user is filling in the form in SP? Do they have to download the doc so the VBA can run?

If so, there doesn't seem to be a way they can do that?

Cheers, Toby

 

[Micron]

I'm no SP expert by far. All I can do with this issue is give you food for thought. If you're saying that you can't run code 'embedded' in SP files like Excel I'll have to take your word for it. So my take on it is that a desktop wb or network stored wb could run code with the right permissions. That code could email all or portions of the SP files like Excel or Word or edit it, or whatever it is you want to have happen. That code would be specific to a particular SP document, methinks. How anyone knows when to do anything with the SP document I don't know. That's for you to figure out how your business can take care of that. F'rinstance, maybe they update the SP workbook, sign out and go to email or pick up the phone and say "Do that thing". Then they open the network/desktop file and it does something to/with the SP file. Put another way let's say the SP document needs to go shopping. The other wb with the code takes it for a ride to the mall.
Hope that makes sense.

To me, SP seems to be some kind of lost child, somewhere between a browser app with php/xml code and apps with stand alone documents.

 

[wtom0412]

Thanks again Micron, I think I am going to have to put this in the "Microsoft not working for the user and being really annoying" box.

There doesn't seem to be any logical reason that Macros cant run on the Cloud 365 version of Excel, but there it is, another mystery goes unsolved.

I will do a 180 on this and think of another way (hopefully one that doesn't involve MS!!).

Thanks again for all your advice.

Cheers, Toby

PS, I think SharePoint/Teams in the Microsoft suite is probably the worst devised platform that Microsoft has every developed (worst than Word!!) It is supposed to make things work seamlessly, but it does exactly the opposite. I actually hate it, I wish our workplace would dump it and invest in something that works!!