Excel / ActiveX help!

N

neilmonk

New Member
Joined
Jun 21, 2004
Messages
22
Right, where to start....!

Firstly, here's what I'm wanting to do:
Have a HTML page that has a form, which writes data to "somewhere".

Due to my ****tyy conmpany, and all the red tape, I do not have access to a webserver, so for those of you that say "get a webserver"; I'll thank you for your comments in advance ;-)

I've managed [eventually] to find a script that writes an Excel file, which is spot on, exactly what I wanted. The only problem is that it WRITES an excel file. I want it to UPDATE an excel file.

Here's the code:

Code: 
<HTML>
<HEAD>
<SCRIPT LANGUAGE="JavaScript">

<!-- Begin
function xlWrite(r,c) {
  var xlApp, xlSheet;
  xlApp = new ActiveXObject("Excel.Application");
  xlApp.Visible = true;
  xlApp.Workbooks.Add();
  xlSheet = xlApp.ActiveSheet;
  xlSheet.Cells(r,c).Value = frm.txt.value;
  xlSheet.SaveAs("C:\\xlText.xls");
  xlApp.Quit();
  xlApp = null;
  setTimeout("CollectGarbage()",1);
}
//  End -->
</script>
</HEAD>

<FORM NAME="frm">
  <INPUT TYPE="Text" NAME="txt" Value="Some cell text">

  <INPUT TYPE="Button" VALUE="Put in row 1, column 2" ONCLICK="xlWrite
(1,2)">
</FORM>

Also, to avoid the "File exists, overwrite (Yes,No)" dialog - use 
SaveCopyAs instead of SaveAs.

</HTML>

I then have an Excel file that has in it:

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)

rowcounter = 2
While Range("a" & rowcounter) <> ""
rowcounter = rowcounter + 1
Wend
Rows("1:1").Copy
Rows(rowcounter).Select
ActiveSheet.Paste
Selection.CutCopyMode = False

End Sub

The Excel code works. But when the ActiveX/HTML form is run again, it replaces the existing excel file. I want it to write to a pre-defined cell within the existing excel file.

Any suggestions (other than "get a webserver")? :-)

TIA, Neil.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Instead of Workbooks.Add() use Open to open the existing workbook.
 
Upvote 0
Instead of Workbooks.Add() use Open to open the existing workbook.
Click to expand...

Using:

Code: 
<HTML>
<HEAD>
<SCRIPT LANGUAGE="JavaScript">

<!-- Begin
function xlWrite(r,c) {
  var xlApp, xlSheet;
  xlApp = new ActiveXObject("Excel.Application");
  xlApp.Visible = true;
  xlApp.Workbooks.Open("C:\xlText.xls");
  xlSheet = xlApp.ActiveSheet;
  xlSheet.Cells(r,c).Value = frm.txt.value;
  xlSheet.SaveAs("C:\\xlText.xls");
  xlApp.Quit();
  xlApp = null;
  setTimeout("CollectGarbage()",1);
}
//  End -->
</script>
</HEAD>

<FORM NAME="frm">
  <INPUT TYPE="Text" NAME="txt" Value="Some cell text">

  <INPUT TYPE="Button" VALUE="Put in row 1, column 2" ONCLICK="xlWrite
(1,2)">
</FORM>

Also, to avoid the "File exists, overwrite (Yes,No)" dialog - use
SaveCopyAs instead of SaveAs.

</HTML>

Generates an error:

Line: 10
Char: 29
Error: Expected hexadecimal digit
Code: 0
Url: (references the url of the actual html file)
 
Upvote 0
Which line does the debugger see as line 10?

I notice in the SaveAs you have double backslashs (\\), have you tried
them with the Open?
 
Upvote 0
Which line does the debugger see as line 10?

I notice in the SaveAs you have double backslashs (\\), have you tried
them with the Open?
Click to expand...

I have now, and it works (in a fashion!) Thanks a lot for your help and speedy response.
 
Upvote 0
I have now, and it works (in a fashion!) Thanks a lot for your help and speedy response.
Click to expand...
How is it not working as you want it?
 
Upvote 0
I have now, and it works (in a fashion!) Thanks a lot for your help and speedy response.
Click to expand...
How is it not working as you want it?
Click to expand...

Excel is playing up!

I'll get my resident Excel person (RichardMGreen incidently!) to have a look, failing that, I'll post back.
 
Upvote 0
Right,

The Excel probel is sorted. The only issue is now that the "file XXX already exists, do you wish to replace it?" message is being displayed.

The code states
Also, to avoid the "File exists, overwrite (Yes,No)" dialog - use
SaveCopyAs instead of SaveAs.
Click to expand...

(I obtained the code from a site similar to this).

However, using the above, hence making my code:



Code: 
<HTML>
<HEAD>
<SCRIPT LANGUAGE="JavaScript">

<!-- Begin
function xlWrite(r,c) {
  var xlApp, xlSheet;
  xlApp = new ActiveXObject("Excel.Application");
  xlApp.Visible = true;
  xlApp.Workbooks.Open("C:\\xlText.xls");
  xlSheet = xlApp.ActiveSheet;
  xlSheet.Cells(r,c).Value = frm.txt.value;
  xlSheet.SaveCopyAs("C:\\xlText.xls");
  xlApp.Quit();
  xlApp = null;
  setTimeout("CollectGarbage()",1);
}
//  End -->
</script>
</HEAD>

<FORM NAME="frm">
  <INPUT TYPE="Text" NAME="txt" Value="Some cell text">

  <INPUT TYPE="Button" VALUE="Put in row 1, column 2" ONCLICK="xlWrite
(1,1)">
</FORM>

Also, to avoid the "File exists, overwrite (Yes,No)" dialog - use
SaveCopyAs instead of SaveAs.

</HTML>

Gives an error:

Line: 13
Char: 3
Error: Object doesn't support this property or method
Code: 0
File: The html file.

Line 13 is:
Code: 
  xlSheet.SaveCopyAs("C:\\xlText.xls");

Any ideas?
 
Upvote 0
Why don't you just close the file?

Then you should have the option to save changes or not.

By the way there is no SaveCopyAs method for the worksheet object.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Macro modification
Replies
1
Views
1K
DanteAmor
DanteAmor
N
  • Question Question
Automation assistance - VBA newb
Replies
2
Views
1K
Norie
Norie
vijnanamatrata
  • Question Question
Recreating NTILE function on running SQL in Excel
Replies
1
Views
2K
vijnanamatrata
vijnanamatrata
F
  • Question Question
Application.match no longer found in object browser after upgrading to excel 2016
2
Replies
10
Views
3K
fullerm
F
T
  • Question Question
update pdf files with excel and adobe acrobat pro
Replies
0
Views
2K
theprincipal78
T

Forum statistics

Threads
1,224,847
Messages
6,181,326
Members
453,032
Latest member
Pauh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top