Excel macro experts, fancy a challenge?

Factual Questions
1

Well if you’re an expert it won’t be a challenge, but fancy it anyway.

If I had time I’d figure it out myself, but I’d probably end up wasting a few hours on it googling and such like. I have other stuff to do.

I want some code which, when a button is pressed it does the following…

checks that some cells are selected (doesn’t matter which)

If no cells are selected it informs the user and then does nothing more.

If some cells are selected it does a ‘save as web page’ to a given network address and file (such as //aserver/afolder/afile.htm)

It does this using the ‘replace file’ option.
For bonus points, it then opens up a browser with http://intranet/afile.htm (so the user can see the results)
Nobody has to do this, I am asking for those who might like something to pass the time and to show off their skilzz with.

2

There is always a selected cell. It is called the Active Cell.

3

I have fairly extensive experience with VBA for Excel and have taught a class in it. Most of what you describe is pretty straightforward (I’m not sure about opening a browser but I doubt it’s difficult).

However, **KneadToKnow ** has an excellent point. Normally, there will be cells activated.

It is possible under some circumstances to have no active range, which generally involves things like selecting a drawing object, a chart. Also some things that a macro can do. But I am not sure that’s what you have in mind in the OP.

As I always do in these cases, I will ask for a little more background to establish what you want to do rather than how you think you need to do it. Why do you want the user to have to have cells selected, if they are pressing a button to run the macro?

4

The document in question is an entire year of staff rotas, updated each week by adding a new table to the bottom.

When the user clicks the button the area they selected (next week’s rota and this week’s rota) are then saved as a web document on a file share which happens to be the root folder of the intranet.

In other words they are ‘uploading’ the rota.
I want to avoid them accidentally submitting the entire year of rotas! (hence the check of whether an area is selected)

5

Any particular reason that you want to be sure they have cells selected first?

6

Wouldn’t they just accidentally submit the data in cell A1 (for example)?

7

What the hell is a rota?

But, to the challenge:

Am I right in understanding that what you actually meant to say was: “checks that particular cells are selected. If those cells are not selected, macro informs the user to select the correct cells and exits. If those cells are selected, macro …”?

If so, it’s a lot easier to just program the macro to select the relevant cells and leave the user out of it.

8

A schedule of shifts/who’s working when.

9

That’s probably the way I’d handle it, if it all possible. I generally avoid user input unless absolutely necessary.

Otherwise, I’d have the macro cue the user to select the proper cells.

10

To answer several people about the selection of cells…

I happen to know that the ‘save as webpage’ saves the entire sheet if no range of cells are selected…

So If the user forgets to select the right rotas he should not be able to save the entire file.
And I deliberately left out these details in the op because they are irrelivant. I only want the code to check if the user has selected something (more than one cell) The macro doesn’t care what they’ve selected. It also doesn’t care that it happens to be a rota.
rota= roster or rosta = table of when staff are in work and on what days.

11

I want the macro to be as simple as possible.

I want the macro to leave the task of selecting the right cells up to the user (because that is hard to get wrong)

So I don’t want the macro to care what cells are selected… all the macro is catching is whether the user has completely forgotten to do the selecting.
edit: having the macro do the selecting is making this FAR more complicated than I need it to be (also more room for things to go weirdly wrong) because the tables are manually created… sometimes they have notes to the right of them, or underneath them, or on top of them (such as ‘Christmas Rota 2008’) sometimes the number of people might change, too many factors for the macro doing the selecting to be simple.

12

User opens document.

User puts selection round this weeks and next week’s rota.

User presses button.

Button assumes user has selected what he meant to and saves selection as a file named ‘rota.htm’ in a specific place on the network. (replacing the file that’s there)

13

First blush: I don’t see any way to make it take a pre-selected range of cells. It can take the whole page or a range of cells selected after the Publish command is invoked. I’m not having any luck setting a variable equal to the pre-selected range and getting PublishObjects.Add to take that variable, either.

14

Wait, I think the trick will be to copy the pre-selected range to a blank sheet (creating the new sheet on the fly if necessary), publish that whole sheet, then (if necessary) delete it on the fly.

15

Rough draft:

Sub LobsangsChallenge()
’
'presupposes selected range is correct
Selection.Copy
'change name of sheet to any blank sheet - will need to write code to erase it either before or after this process
Sheets(“Sheet2”).Select
Range(“A1”).Select
ActiveSheet.Paste
'change c: emp\Page.htm to any desired output
With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
“c: emp\Page.htm”, “Sheet2”, “”, xlHtmlStatic, “Book1_1265”, “”)
.Publish (True)
.AutoRepublish = False
End With
End Sub

16

Put in the test for a selection of one or fewer cells:


Sub LobsangsChallenge()
'
    If Selection.Count <= 1 Then
        MsgBox "Select the entire range to be published."
        Exit Sub
    End If
    Selection.Copy
'change name of sheet to any blank sheet
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
'change c:	emp\Page.htm to any desired output
    With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
        "c:	emp\Page.htm", "Sheet2", "", xlHtmlStatic, "Book1_1265", "")
        .Publish (True)
        .AutoRepublish = False
    End With
'next two lines erase the sheet, leaving it blank for next time
    Sheets("Sheet2").Select
    Selection.Clear
End Sub
17

FWIW, I don’t see where the “open published web page in browser” option is in the vb code. I suspect it’s a separate process Excel triggers like when you click on a hyperlink.

18

it sort of works if I comment out “Range(“A1”).Select”
Edit: I guess I assumed that if ms excel had a ‘selection’ bit in the publish dialog box then you would also be able to use that somehow in the script. It seems a shame to have to create a new sheet as a staging area for it.

19 

With wb.PublishObjects.Add(xlSourceRange, _
"C:\myPage.htm", "Sheet1", "$A$1:$C$6", _
xlHtmlStatic, "Book1_20936")
.Publish (True)

This bit of code I found on the net seems to show that you can include a range when doing the publish

So would it be possible to simply somehow include what’s on the clipboard in place of “$A$1:$C$6”?

20

All “Range(“A1”).Select” does is move the active cell to the first cell in the sheet. Feel free to comment it out, but I put it in there so that when you publish, your data won’t be mysteriously shifted right and down from the top left.

As I said, you can also create and delete the sheet you need on the fly.

To be honest, I think I came up with a pretty elegant solution to your challenge and I’m very disappointed in your rather negative response.