Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Problem With ThisWorkbook File in Excel


  • Please log in to reply

#1
Danimagus

Danimagus

    Member

  • Member
  • PipPip
  • 29 posts
Hello all! I'm trying to get a spreadsheet Iíve made to save itself automatically as an HTML file whenever the worksheet itself is saved conventionally with ctrl+s or file->save, etc. I currently have the following code, the first in a module, the second in the ThisWorkbook file.

Sub ExportAsHTML()

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"T:\...\System\EngineeringHealthGauges.htm" _
, FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
End Sub



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.DisplayAlerts = False
Call ExportAsHTML
Application.DisplayAlerts = True

End Sub


Now, whenever I save, excel crashes and the changes are lost, though it does export. I figure that because I can run the ExportAsHTML macro without ill effects, I'm doing something wrong with the ThisWorkbook file. Does anyone know how to fix my code or just get it to do what I want?

Also, Iíve also been wanting to put text in charts that changes based on changes to some sort of source cell in the worksheet. That is, Iíd like titles and other text boxes to change when some cell in the worksheet does. Also, can I hide rows and still have they data they actually contain be graphed? Or should I just cover them up with an enormous white-filled text box?

Edited by Danimagus, 06 June 2005 - 12:57 PM.

  • 0

Advertisements


#2
stu_design

stu_design

    Member

  • Member
  • PipPipPip
  • 217 posts
tring putting in the code from the sub into the Workbook_BeforeSave

if that dont work post back

and yes if u hide the rows, the data in the chart dissappears

Stu Design
  • 0

#3
Danimagus

Danimagus

    Member

  • Topic Starter
  • Member
  • PipPip
  • 29 posts
Like this?


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"T:\...\System\EngineeringHealthGauges.htm" _
, FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True

End Sub


I stuck that in ThisWorkbook (right?), and got the same old crash.

Edited by Danimagus, 06 June 2005 - 12:57 PM.

  • 0

#4
lt b0ngo

lt b0ngo

    Member

  • Member
  • PipPipPip
  • 161 posts
To hide figures you could just change the text colour to white.
  • 0

#5
stu_design

stu_design

    Member

  • Member
  • PipPipPip
  • 217 posts
lol, now why didnt i think of that

FROM DANIMAGUS
Also, can I hide rows and still have they data they actually contain be graphed? Or should I just cover them up with an enormous white-filled text box?


FROM IT BONGO
To hide figures you could just change the text colour to white.


But for the other prob: excel crasshing etc

ill look into it

Stu Design

Edited by stu_design, 06 June 2005 - 07:16 AM.

  • 0

#6
Danimagus

Danimagus

    Member

  • Topic Starter
  • Member
  • PipPip
  • 29 posts
Thanks a lot, Stu--let me know what you find... I've been fiddling about with this thing a bit over the weekend and haven't been able to fix it...
  • 0

#7
Danimagus

Danimagus

    Member

  • Topic Starter
  • Member
  • PipPip
  • 29 posts
I think it may be because of a loop of some kind--perhaps BeforeSave doesn't like being told to run SaveAs...? Not sure, though--thoughts on how to fix such a thing?
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP