JS Excel

Hi Experts

Using JS, is it possible to open an existing excel file, update it, and preserve its vba?
APD TorontoSoftware DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Yes!

To demonstrate this, I have attached two files; an MS-Excel workbook (MS-Excel 2007 & above ".xlsm" format), & a text file containing HyperText Markup Language & JavaScript code (".htm" file).

The Visual Basic for Applications code within the Workbook_Open() event of the workbook is as follows:

Option Explicit
Private Sub Workbook_Open()

  MsgBox "Contents of cell [A1]: " & _
         vbCrLf & vbLf & _
         Worksheets("Q_28700214").Cells(1&, 1).Value, _
         vbInformation Or vbOKOnly, _
         ThisWorkbook.Name
         
End Sub

Open in new window


The content of the attached HTML file is as follows:

<html>

<!-- Author: fanpages, Experts-Exchange.com, 26 July 2015 -->

<head>
<title>Q_28700214 JS Excel - fanpages [b.00.01a] (26 July 2015)</title>

<meta http-equiv="Content-Type"				content="text/html; charset=iso-8859-1">
<meta name="author"				lang="en"	content="fanpages, Experts-Exchange.com, 26 July 2015">
<meta name="description"		lang="en"	content="JS Excel - last updated 26/05/2015">
<meta name="keywords"			lang="en"	content="Q_28700214">
<meta name="copyright"			lang="en"	content="&copy 2015 Clearlogic Concepts (UK) Limited">
</head>

<body>

<style>
p 	{
	font-family:	"Courier New";
    font-size:		12;
    font-style:		normal;
	}
</style>

<script type="text/javascript">

<!-- Hide if non-JavaScript compliant Browser...
// ------------------------------------------------------------------------------------------------------------------ \\
function Q_28700214()
{
  var datDate 											= new Date();
  var objExcel_Application 								= new ActiveXObject("Excel.Application");

  objExcel_Application.Visible = true;

  objExcel_Application.Workbooks.Open("file://d:\Q_28700214.xlsm", 3, false);

  objExcel_Application.Worksheets('Q_28700214').Cells(1, 1) = datDate;

  objExcel_Application.ActiveWorkbook.Save;

  objExcel_Application.ActiveWorkbook.Close;

  objExcel_Application.Quit();
}
// ------------------------------------------------------------------------------------------------------------------ \\
// end of hidden JavaScript -->
</script>

<p>
' -------------------------------------------------------------------------------------------------------------------------------<br>
' [ <a href="http://www.experts-exchange.com/questions/28700214/JS-Excel.html">http://www.experts-exchange.com/questions/28700214/JS-Excel.html</a> ]<br>
'<br>
' Question Channel: Experts Exchange > JS Excel<br>
' Topic Area:       [ <a href="http://www.experts-exchange.com/topics/ms-excel/">http://www.experts-exchange.com/topics/ms-excel/</a> ]<br>
'<br>
' ID:               Q_28700214<br>
' Question Title:   JS Excel<br>
' Question Dated:   2015-07-25 10:45 PM<br>
' Question Asker:   APD_Toronto<br>
' Asker Profile:    [ <a href="http://www.experts-exchange.com/members/APD_Toronto.html">http://www.experts-exchange.com/members/APD_Toronto.html</a> ]<br>
' Attachment(s):    (none provided)<br>
'<br>
' Solution posted:  26 July 2015 by fanpages<br>
'<br>
' Expert Profile:   [ <a href="http://www.experts-exchange.com/members/fanpages.html">http://www.experts-exchange.com/members/fanpages.html</a> ] | [ <a href="http://www.experts-exchange.com/M_258171.html">http://www.experts-exchange.com/M_258171.html</a> ]<br>
'<br>
' Copyright:        (c) 2015 Clearlogic Concepts (UK) Limited                                            [ <a href="http://NigelLee.info">http://NigelLee.info</a> ]<br>
' -------------------------------------------------------------------------------------------------------------------------------<br>
</p>

<form name="frmQ_28700214">

	<input type=button onClick="Q_28700214()" value="Open Q_28700214.xlsm">

</form>

</body>
</html>

Open in new window



Please note that within the JavaScript, the location of the "Q_28700214.xlsm" file is "D:\Q_28700214.xlsm" (i.e. the root folder of the D: drive).

Change this location ("D:\") to suit your own requirements/filing system.


If you save both files locally, & open the "Q_28700214.htm" file within Microsoft Internet Explorer, you may be prompted to enabled blocked content; this being the ActiveX "Excel.Application" object.

Example prompts:

Internet Explorer restricted this webpage from running scripts or ActiveX controlsAn ActiveX control on this page might be unsafe
Once you have confirmed the prompts (positively; i.e. "Allow block content" & "Yes", you do wish to allow interaction), the MS-Excel workbook may then be opened from the specified location ("D:\Q_28700214.xlsm") via the Form button, [Open Q_28700214.xlsm], on the HTML page.

When the button is clicked, the MS-Excel workbook is opened, & the Workbook_Open() event code will execute, displaying the (present) contents of cell [A1] of the worksheet [Q_28700214] within a Message Box.

After confirming the Message Box, cell [A1] will be updated (with the current system date/time) via the JavaScript code within the HTML document.

The MS-Excel workbook will then be saved, & closed, & the MS-Excel instance will be removed from the screen.

If you then re-click the [Open Q_28700214.xlsm] Form button, the workbook will be re-opened, the Workbook_Open() event code will (re-)execute, & you will see the updated contents of cell [A1].

All being well, this will demonstrate that the Visual Basic for Applications code was retained when the workbook was opened & updated via JavaScript.
Q-28700214.xlsm
Q-28700214.htm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongCommented:
>>Using JS, is it possible to open an existing excel file, update it, and preserve its vba?

first of all, where's the Excel file location? Server side or at local machine? Where you're trying to execute that Javascript? From Internet webpage or local machine?
0
[ fanpages ]IT Services ConsultantCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.