Edited by author 10-06-2008 12:52 PM
/m2018

Richard,

Thanks for your responses. Sorry for the delay in responding. I could not access the forum for a while. I thought I had subscribed but the system did not acknowledge my email address. I did not realise I could post and receive email without subcribing, so it looks like I never subscribed. Anyway...

I followed the link to the MS Excel forum and read various related feeds. None that solved my problem so far though.

The thing that puzzles me is that I ran the MakeHTML macro as it was on the site and had no problem. All characters on the MakeHTML page rendered properly in the browser.

Using the same MakeHTML code as the basis for my own project, I then commented out the stuff I did not need and added my own code, applied it to some sheets created in my Excel 2003 and the "£" sign and other characters do not render correctly in the browser; all I get is "?" in place of the missing items. This would indicate the the character is not being recognised as UTF-8 Unicode by my browser, which makes sense since Excel does not use UTF-8. However, why do the original MakeHTML sheets work and my modifications of same not work. There is nothing in the original or my VBA that I can see would have any effect on the code used to interpret the special characters.

The only difference I can figure has nothing to do with the VBA. It is that MY sheets were created on My machine and exported from MY version of Excel. The original was created on a different machine, not changed on my machine, but the VBA was still run and the file exported from MY Excel. I therefore have to assume that the difference lies in my version of Excel 2003 and the way it codes the symbols, not the way VBA runs and exports the code otherwise it would affect the original MakeHTML when run on MY machine, and it doesn't.

I do not know how to check this or change it.

Can you suggest anything?

Thanks for your help.

/m2024

Hello Kevin,

This is not something I have experience of so I can't help directly. I still think using the Excel forum is a good bet. If not, you might be able to drop a line in to one or other of the real Excel pros. You could try to contact Mr Excel, Excel Guru, or Ron deBruin. They are all usually friendly and very helpful.

Good luck.

Can anyone provide the details on Microsoft Certification on MS-
Excel 2003.

What are the types of MS-certifications like Master, Professional,etc., along with prerequisites and their exam codes.

Please provide the above details, it would be appreciated

Thanks,
Vinod

Hello Richard,

Thanks for your continued interest.

I have gone through the forums quite extensively but have not come up with an elegant solution yet. Some of the explanations get too technical for me. I will follow up on the Excel pros to see what they can do.

I did find one inelegant workaround from the forum which is to run the macro and then open the resulting file in Notebook from which it is possible to save the file as a UTF-8 file. This solves the problem but is messy, so I am going to keep looking.

I still do not understand why the MakeHTML file downloaded from
(www.meadinkent.co.uk) works fine with no problems from my computer but my model of the same file does not.

I will keep plugging and if I get anywhere I will post my findings.
Thanks for you help

Kevin

< replied-to message removed by QT >

I am trying to write a formula that use WordNum(). But not working is properly only work 1st time but second time workbook open a error view in formula's cell #Name? and function arguments box viewing this message 'This function takes no arguments'. Thanks for the help!

/m2028

Hello Ahmed,

Can you put and paste the formula here so we can see how you have used it?

Thanks

I am trying to Display a number as words.
use this formula

Option Explicit
Public Numbers As Variant, Tens As Variant

Sub SetNums()
Numbers = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
End Sub

Function WordNum(MyNumber As Double) As String
Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
' This macro was written by Chris Mead - www.MeadInKent.co.uk

If Abs(MyNumber) > 999999999 Then
WordNum = "Value too large"
Exit Function
End If

SetNums

' String representation of amount (excl decimals)
NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))

For n = 3 To 1 Step -1 'analyse the absolute number as 3 sets of 3 digits
StrNo = Format(ValNo(n), "000")

If ValNo(n) > 0 Then
Temp1 = GetTens(Val(Right(StrNo, 2)))
If Left(StrNo, 1) <> "0" Then
Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred"
If Temp1 <> "" Then Temp2 = Temp2 & " and "
Else
Temp2 = ""
End If

If n = 3 Then
If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
WordNum = Trim(Temp2 & Temp1)
End If
If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " thousand " & WordNum)
If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million " & WordNum)

End If
Next n

NumStr = Trim(Str(Abs(MyNumber)))

' Values after the decimal place
DecimalPosition = InStr(NumStr, ".")
Numbers(0) = "Zero"
If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
Temp1 = " point"
For n = DecimalPosition + 1 To Len(NumStr)
Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
Next n
WordNum = WordNum & Temp1
End If

If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then
WordNum = "Zero" & WordNum
End If
End Function

Function GetTens(TensNum As Integer) As String
' Converts a number from 0 to 99 into text.
If TensNum <= 19 Then
GetTens = Numbers(TensNum)
Else
Dim MyNo As String
MyNo = Format(TensNum, "00")
GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
End If
End Function

/m2030

I think you probably do not have the function declared in the right place. Make sure you have it in a Module in the VBA editor.

And, to have it work for me I had to move the declaration of MyNumber As Double from within the Function to the Option Explicit.

So I have this:

Option Explicit
Public Numbers As Variant, Tens As Variant, MyNumber As Double

Sub SetNums()
Numbers = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
End Sub

Function WordNum(MyNumber As Double) As String
' Dim MyNumber As Double
Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
' This macro was written by Chris Mead - www.MeadInKent.co.uk

And it seems to work fine now.

HTH

/m2031

Another possibility is your Macro security level. Set it to medium if this is not already the case.

Good luck.

replica watches
Replica Watches guide
Rolex Replicas,
Tag Heuer watches,
Breitling watches,
Cartier watches,
Gucci watches,
Omega watches,
Sony PCGA-BP1N battery
laptop battery
laptop batteries
40Y7003
samsung aa-pb2nc6b
02K6620
VGP-BPS2
Sony PCGA-BP1U battery
Sony PCGA-BP2E battery
Sony PCGA-BP2EA battery
Sony PCGA-BP2NX battery
Sony PCGA-BP2NY battery
Sony PCGA-BP2R battery
Sony PCGA-BP2S battery
Sony PCGA-BP2SA battery
Sony PCGA-BP2S/HI battery
Sony PCGA-BP2T battery
Sony PCGA-BP2V battery
Sony PCGA-BP3T battery
Sony PCGA-BP4V battery
Sony PCGA-BP505 battery
Sony PCGA-BP51 battery
Sony PCGA-BP51A battery
Sony PCGA-BP51A/L battery
Sony PCGA-BP52 battery
Sony PCGA-BP52A battery
Sony PCGA-BP52A/L battery
Sony PCGA-BP52AUC battery
Sony PCGA-BP7 battery
Sony PCGA-BP71 battery
Sony PCGA-BP71A battery
Sony PCGA-BP71AUC battery
Sony PCGA-BPZ51 battery
Sony PCGA-BPZ51A battery
Sony VGP-BP2EA battery
Sony VGP-BPL1 battery
Sony VGP-BPL2 battery
Sony VGP-BPL2C battery
Sony VGPBPL2.CE7 battery
Sony VGP-BPL2C/S battery
Sony VGP-BPL4 battery
Sony VGP-BPL4A battery
Sony VGP-BPL5 battery
Sony VGP-BPL5A battery
Sony VGP-BPS1 battery
Sony VGP-BPS2 battery
Sony VGP-BPS2A battery
Sony VGP-BPS2A/S battery
Sony VGP-BPS2B battery
Sony VGP-BPS2C battery
Sony VGP-BPS3 battery
Sony VGP-BPS3A battery
Sony VGP-BPS4 battery
Sony VGP-BPS5 battery
Sony VGP-BPS5A battery

Hi
I'm using the Makehtm macro which is great and have saved my life :)
But is there a way to make specify what sheet I want to make an htm in the macro?
I mean I have several sheets in a the workbook, and to make each one an htm I need to go to the macro change the pagename and run it, then change sheet go to the macro change the pagename and run it, and on.
I was trying to find currentsheet or something like that in the macro (but I'm not very good in vba)
Maybe there is a line of code I can change so I make different macros with a specific pagename for every sheet and run them all at once.
Hope you can help
Greets
Javier

/m2034

Hello Javier,

I don't know anything about this macro specifically but you could try the following.

Put the tab name in a specific cell, say Sheet1!A1. Then, in the macro define pagename as the cell value. pagename = worksheet("Sheet1").range("A1").value

Then, when you run the macro it will do it for the sheet referenced in cell A1 on sheet1.

HTH

Hi Javier

I have recently modified the MakeHTM script to achieve what you want as follows:

What I say below assumes that

a) the saved HTML files will have the same names as the Sheets from which they are derived
b) all the files will be saved to the same folder on your system.
Then do the following:

1) Copy and Paste the "HTML" button to each Sheet you are going to process. (On each page where you place the button, make sure the button is still MakeHTM script is still assigned to the button)

2) Edit the script as follows:

2a)Add 2 new variables to the list at the top of the script :

 Dim mySheet As String, myPath As String

2b) Find the line (near the top of the script)

 PageName = "path_&_file_name.html"
 
 and comment it out (or delete it if you are comfortable doing that)
2c) In place of that line insert the following 3 lines

 myPath = "path\to_folder\on_your_system\" (replace my stuff with the path where you will save your output files)
 mySheet = ActiveSheet.Name (Gets the name of the active sheet)
 PageName = myPath & mySheet & ".html" (Constructs the path and file name where the page will be named and saved )



That should do it.


FILE NAMES DIFFERENT FROM SHEET NAMES?

If you want to use file names other than the Sheet names from which they are derived, do the following

1) on each sheet select an empty cell away from range that is being processed by the script.

1a) Type the file name you want to save this sheet to into that cell.
1b) Now you need to give the cell containing the File Name a RangeName. (Let us call it "fileName" for the purposes of this exercise)

On the menu bar for the sheet, go to
 
 Insert > Name > Define - which will open the "Define Name" dialog box

1c) In the "Names in workbook" slot at the top of the dialog box type the SheetName!RangeName. (e.g Sheet1!fileName)

IMPORTANT! You must include the SheetName! as well as the RangeName so the range name is only visible from that sheet and from no others in the workbook. If you omit the SheetName! and just give the range a name, this will cause you all sorts of grief and foul up your VBA in this instance.
Repeat for each sheet

Now, with each sheet containing a cell with the required fileName go back to the script

2) In place of mySheet = ActiveSheet.Name (see above) insert

 mySheet = Range("fileName").Value


That's it.

DIFFERENT FOLDER FOR EACH FILE?

If you want to save each sheet to a different folder on your system you will have to repeat the above process placing a cell on each sheet and place the Path you want to save that sheet to in the cell then name the cell containing the path with the same name on each sheet. ("Sheet1!pathName" "Sheet2!pathName" etc for example)

Then in place of myPath = "path/to_folder/on_your_system/" as above, you would say

myPath = Range("pathName").Value

That's it

Hope this helps and does not confuse the hell out of you!

Cheers

Kevin


< replied-to message removed by QT >

I have a sheet that contains a list of dates (every date) from the 1st April to end of next March. I have conditional formatting that colours todays date but I would also like to have the date appear below the freeze pains line (row 3) when I open the spreadsheet up - how can i do this.

Cheers

Mark

Mark,

I'm probably not fully understanding what you're wanting to do but if you simply want today's date to appear in a specific cell use either =today() or =now() in the cell.

________________

Gary


< replied-to message removed by QT >

/m2037

Hi Mark,

If you want to filter your list from today's date then you could try this macro.

Range("G5").Select
    Selection.AutoFilter
    ActiveCell.FormulaR1C1 = "Date"
    Range("G6").Select
    Selection.AutoFilter Field:=1, Criteria1:=">=" & Range("G2").Value, Operator:=xlAnd

Where G5 is the column header of the dates. G2 is the date (I had to do some trickery to get the right format but yours may work start away). Run the macro when the book is opened and the older dates will not be shown.

HTH