Replies

Hello RodeRunner,

Thank you for replying, and for your tips on how to use Windows Live Mail as a replacement for Thunderbird. I’ll compare the feature set of WLM against the components of Thunderbird which I am currently using (mainly filing and searching of incoming messages). I’m not sure I understand the future for Thunderbird – I thought it would remain “alive”, but without further support of development by Mozilla? I don’t know of anything I want which the current Thunderbird does not provide, and would prefer to avoid having to install and “learn” new email software. That said, I did start looking for alternatives to Thunderbird, and thought that eM Client (http://www.emclient.com/) looked interesting. The free version is limited to 2 accounts, but (at present) I only use one account.

As to my problem of garbled emails from Windows Secrets and other group email services, that ceased as soon as I changed my internet service provider. I had been experiencing constant problems for the last 2 weeks accessing all web sites (typically 4 fails then a successful connection), and didn’t receive a satisfactory solution from my ISP, so yesterday I switched. The new cables and kit arrived and were installed yesterday. Result – no more connection problems, and the latest Windows Secrets Newsletter was correctly handled and processed by Thunderbird. I guess that something in the Newsletter requests access to the server of Windows Secrets, and that when the connection cannot be made the message comes through (to Thunderbird) in the garbled form indicated in the attached file above. I’ll know for next time.

Hope this helps others.

Cheers,

Richard.

I’ve found the random number functions available from http://www.ntrand.com/ to be very powerful (and FREE!!). Richard.

If you prefer to use a function:

=TEXT(3,”0000″)

produces 0003. Tested using Excel 2003. I don’t have Excel 2000, but I think the TEXT function was available in earlier versions of Excel. Worth trying.

Richard (from Piracicaba)

Troy,

You could try and see if the following snippet of code (in the code module behind the sheet you wish to monitor), captures what you are trying to trap, and then adapt to your precise requirements:

—————————— VBA code: ———————————————————————-

Option Explicit

Dim last_edited_cell As Range

Private Sub Worksheet_Change(ByVal Target As Range)

Set last_edited_cell = Target

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim usr_msg As VbMsgBoxResult

If Not last_edited_cell Is Nothing Then
usr_msg = MsgBox(“new selection (target) is ” & Target.Address & Chr(10) & _
“last edited cell: ” & last_edited_cell.Address & Chr(10) & _
“Select last edited cell?”, vbQuestion + vbYesNo)
If usr_msg = vbYes Then
Application.EnableEvents = False
last_edited_cell.Select
Application.EnableEvents = True
End If
End If

End Sub

——————— end of VBA code ————————————-

(is there an easy way to make the editor “show VBA code” – indents, mono-spaced font etc…)

Richard (from Piracicaba).

Delenca,

Here’s what I do when I want to see the range (matrix, array, block, …) of cells referred to by OFFSET:

Insert > Name > Define

brings up the Define Name dialogue, with 3 boxes. Type a name into the first (temp seems a good choice!), then in the bottom box type the OFFSET function you want to “see”, for example:

=OFFSET($F$6,2,-1,6,3)

Click Add. At the end of the Refers to box there is an icon that looks like a grid. Click on this, the Define Name dialogue will shrink, and the range of cells will be outlined, so you can “see” the result of the OFFSET function. By ringing the changes, you should soon come to master this very useful function for defining an array of cells. For the example above:

$F$6 –> anchor
2 –> shift down 2 rows (takes you to $F$8)
-1 –> shift right -1 column (takes you to $E$8)

that’s defined the top left corner of the range, the remaining 2 parameters give the height and width of the range

6 –> height (rows 8 to 13)
3 –> width (columns E to G)

so OFFSET($F$6,2,-1,6,3) returns the range with $E$8 as the top left cell, and $G$13 as the bottom right cell.

Richard (from Piracicaba)

Larry,

From the forum/lounge in which you have posted your question, I’m guessing that the .csv file is an export from an Excel spreadsheet? Do you have access to the original Excel file from which the .csv was exported? If yes, then you should be able to construct the tables within this excel file from the raw data, using some formulae. If not, then you will first need to import the .csv file into an Excel file. Once you have the tables in Excel, putting them into Word will be a simple copy and paste (which could be automated). The only difficulty I can see is that you have a mixture of bold and plain fonts in some of the cells of your table. With Excel formulae it is not possible to switch between bold and plain fonts within a cell, mixing the fonts can be achieved using VBA macros.

I’ve made a stab at setting up the formulae in the attached Excel workbook. Sheet “Raw” is the raw data, in the attachment I set up the raw data by reading in from the .txt (.csv) file you provided. While doing this I found that some of the data in the text file is not in accord with some of the table entries in the Word document. I’ve also produced a VBA macro to do the bold formatting and to select the range which holds the report, ready to be copied and pasted into word. To run the macro just click on the button “Report” on sheet “Raw”.

Hope this helps,

RAK.

Hi Steve,

The following VBA should get you started – it includes a loop to examine all worksheets:

[indent]Sub chart_killer()

Dim n_charts As Long, k_chart As Long
Dim usr_msg As VbMsgBoxResult
Dim wk_sheet As Worksheet

For Each wk_sheet In ThisWorkbook.Sheets
n_charts = wk_sheet.ChartObjects.Count
If n_charts > 0 Then
wk_sheet.Activate
For k_chart = n_charts To 1 Step -1
wk_sheet.ChartObjects(k_chart).Activate
usr_msg = MsgBox(“Delete the selected chart?”, vbQuestion + vbYesNo, _
“Delete”)
If usr_msg = vbYes Then wk_sheet.ChartObjects(k_chart).Delete
Next k_chart
End If
Next wk_sheet

End Sub[/indent]

Note: Activate rather than Select
Hope this helps,

RAK.

Two useful tips:

1. The Y/N is a data mask, remove the N data (replace by null string), and then pack the data to the left to remove the N data.
2. The ranges you wish to plot are dynamic (width depends on number of Y data) – handle this by using dynamic named ranges.

In the attached file I’ve used the SMALL function to achieve task 1. I forget where I first encountered this method, I have found it to be extremely useful as it’s often desirable to remove blanks and shift the remaining data to the left (or to the top) so that there are no blank cells in the repacked data.

Once the data are in contiguous cells, you can set up named ranges, whose width is equal to the number of data points to appear in the plot. The ranges are defined by using the OFFSET range function. Insert the names into the ranges to be plotted.

The attached workbook shows 1 and 2 in action with your data.

Hope this helps!

RAK

Hmmm… All this sorting using Excel’s Sort tool seems like hard work to me, and may be difficult to remember the “how to” for future usage. I’d like to propose a different strategy – using some of Excel’s formulae! I’ve come up with a solution, which you can find in the attachment. I think some of the “work” could be done better with the use of array formulae, but it works! Data in, sorted immediately, no need to run the sorter!! I’d be interested in seeing other solutions which don’t use Excel’s sort tool.

RAK